Posts Tagged ‘ca-cert.pem’

Setting Up SSL For MySQL

Friday, November 18th, 2011

I often get asked how to set up and use SSL with MySQL, especially on Windows – I think largely because the manual step-by-step is more geared towards Linux users (though this “how-to” is valid for all – anyone can copy/paste the commands after changing the paths).

So I’ve compiled a concise, yet comprehensive, how-to that I wanted to share with everyone out there who wants to set this up quickly and efficiently.

And for those who haven’t done it, setting up SSL can often seem intimidating, so I want to dispell that myth while I’m at it, and show you how quick and easy it can be to set up SSL for MySQL.

For one, I should mention that I’m using a version where SSL is ‘available’, just ‘DISABLED’ (some versions can be compiled w/out ssl support – so those would not work):

mysql> show global variables like 'have_%ssl';
+---------------+----------+
| Variable_name | Value    |
+---------------+----------+
| have_openssl  | DISABLED |
| have_ssl      | DISABLED |
+---------------+----------+

This is normal, and what you want to see if you’ve not already set up SSL.

Next, I like to store the SSL files I generate in their own directory, so I created a subdir named ‘certs’ in the MySQL $basedir. But by all means, store these wherever you wish.

Third, you need to have OpenSSL installed/available on your machine. Many have this already, certainly most Linux anyway (check with “where openssl” on Win or “which openssl” on Linux). (These latter commands assume you have it in the $PATH too.)

If you do not have it, you can download it from here:

http://www.slproweb.com/…/Win32OpenSSL.html <-- Windows
http://www.openssl.org/source/ <-- Linux

(And note my commands below assume either 'openssl' in the $PATH or that you're in the directory where the 'openssl' binary/exe is located.)

Now that that’s out of the way, you’re basically looking at a 5 step process:

  1. Create CA Certificates (following 2 commands create 2 files: ca-cert.pem and ca-key.pem):
    openssl genrsa 2048 > "C:/Program Files/mysql/mysql-5.5.16/certs/ca-key.pem"
    openssl req -new -x509 -nodes -days 3600 -key
      "C:/Program Files/mysql/mysql-5.5.16/certs/ca-key.pem" >
      "C:/Program Files/mysql/mysql-5.5.16/certs/ca-cert.pem"
  2. Create Server Certificates (following 2 commands create 3 files: server-cert.pem, server-key.pem, and server-req.pem):
    openssl req -newkey rsa:2048 -days 3600 -nodes -keyout
      "C:/Program Files/mysql/mysql-5.5.16/certs/server-key.pem" >
      "C:/Program Files/mysql/mysql-5.5.16/certs/server-req.pem"
    openssl x509 -req -in "C:/Program Files/mysql/mysql-5.5.16/certs/server-req.pem"
      -days 3600 -CA "C:/Program Files/mysql/mysql-5.5.16/certs/ca-cert.pem"
      -CAkey "C:/Program Files/mysql/mysql-5.5.16/certs/ca-key.pem" -set_serial 01 >
      "C:/Program Files/mysql/mysql-5.5.16/certs/server-cert.pem"
  3. Create Client Certificates (following 2 commands create 3 files: client-cert.pem, client-key.pem, and client-req.pem):
    openssl req -newkey rsa:2048 -days 3600 -nodes -keyout
      "C:/Program Files/mysql/mysql-5.5.16/certs/client-key.pem" >
      "C:/Program Files/mysql/mysql-5.5.16/certs/client-req.pem"
    openssl x509 -req -in "C:/Program Files/mysql/mysql-5.5.16/certs/client-req.pem"
      -days 3600 -CA "C:/Program Files/mysql/mysql-5.5.16/certs/ca-cert.pem"
      -CAkey "C:/Program Files/mysql/mysql-5.5.16/certs/ca-key.pem" -set_serial 01 >
      "C:/Program Files/mysql/mysql-5.5.16/certs/client-cert.pem"
  4. Create SSL User(s):
    GRANT ALL PRIVILEGES ON *.* TO 'ssluser'@'localhost' IDENTIFIED BY 'ssluser' REQUIRE SSL;
  5. Update my.cnf and restart mysqld (add following 3 options to [mysqld] section of my.cnf file)
    [mysqld]
    ssl-ca		= "C:/Program Files/MySQL/mysql-5.5.16/certs/ca-cert.pem"
    ssl-cert	= "C:/Program Files/MySQL/mysql-5.5.16/certs/server-cert.pem"
    ssl-key		= "C:/Program Files/MySQL/mysql-5.5.16/certs/server-key.pem"

After restart, connect via SSL with your newly created SSL user (this is the minimum you need to specify – note the “–ssl-key=”, as-is .. refer to prev. post and related bug for further discussion on that if interested):

mysql -ussluser -pssluser -P3430 --ssl-key=

Connected!

You can verify with the ‘status’ command once connected (note the SSL “cipher in use is DHE-RSA-AES256-SHA” means SSL is being used):

mysql> status
--------------
mysql  Ver 14.14 Distrib 5.5.16, for Win32 (x86)

Connection id:          11
Current database:
Current user:           ssluser@localhost
SSL:                    Cipher in use is DHE-RSA-AES256-SHA
Using delimiter:        ;
Server version:         5.5.16-log MySQL Community Server (GPL)
Protocol version:       10
Connection:             localhost via TCP/IP
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    cp850
Conn.  characterset:    cp850
TCP port:               3430
Uptime:                 35 min 26 sec

Threads: 1  Questions: 24  Slow queries: 0  Opens: 33  Flush tables: 1  Open tables: 0
Queries per second avg: 0.011

Comments:

If you’re knowledgeable abut SSL (and MySQL), or if you’ve read my prior post discussing SSL, you’ll know you do not need all of the files I created above (8 in total).

In fact, for the most basic SSL setup, (“gateway SSL”, we’ll say), you only technically need these 3 files: ca-cert.pem, server-cert.pem, and server-key.pem.

But, why not create all you’d ever need, since it’s only a couple commands, and then you’re set in case you want to move beyond the “gateway SSL” setup into an even more secure SSL setup.

If you’re interested in the even-more-secure SSL setups, you’ll want to read up on the REQUIRE X509, REQUIRE ISSUER, REQUIRE SUBJECT, and REQUIRE CIPHER specifications on the following page:

http://dev.mysql.com/doc/refman/5.5/en/grant.html

As always, hope this helps. :)

 
 
 

MySQL SSL Users: BEWARE This Bug

Tuesday, October 18th, 2011

If you’re using MySQL and SSL, you might want to glance over this article and give your setup a quick test.

** Update: If you are looking for “how-to” set up SSL for MySQL (something much clearer than the MySQL manual that also exposes some hidden facts), then please see this article I’ve written here: Setting Up SSL For MySQL **

I’ve uncovered an alarming bug in 5.5 where one could gain access to your MySQL instance just knowing the username and password (not having any SSL certificate, key, etc.)!

Of course, I’ve filed a bug about it here:

http://bugs.mysql.com/bug.php?id=62743

It’s been over 4 days now, and not one comment from the MySQL Bug/Dev Team.

So once again, I feel the need to share this bug with the public, in case you are using SSL with 5.5, and think your connections are secure, or that only users with the certs/key could gain access.

For SSL Users, you’ll already have this set up, but for those who don’t, I’ve simply got mysqld (5.5.15 and 5.5.16 thus far) running with the following options:

ssl-ca	 = "C:/Program Files/MySQL/mysql-5.5.16/certs/ca-cert.pem"
ssl-cert = "C:/Program Files/MySQL/mysql-5.5.16/certs/server-cert.pem"
ssl-key	 = "C:/Program Files/MySQL/mysql-5.5.16/certs/server-key.pem"

In theory, any user connecting should either be specifying the –ssl-ca option, path, and file, or both the –ssl-cert and –ssl-key options.

However, at least in 5.5.15 and 5.5.16 (haven’t tested any others yet), one can connect with *just* the –ssl-key option.

What’s worse, and most important, is that you don’t even have to specify a file here. Just specify some bogus text!

I created 2 users, one local and one remote, using these 2 commands:

GRANT ALL PRIVILEGES ON *.* TO 'ssluser'@'localhost' IDENTIFIED BY 'ssluser' REQUIRE SSL;)
GRANT ALL PRIVILEGES ON *.* TO 'ssluser'@'remote-hostname' IDENTIFIED BY 'ssluser' REQUIRE SSL;

Now, just specify “buggg” for the -ssl-key option (no path, no file, no nothing):

mysql -ussluser -pssluser -P3430 --ssl-key=buggg

Voila!

The user connects as if it were using an SSL connection. All that was needed to connect to this remote host is the username and password.

Check out the output:

Localhost:

C:\Program Files\MySQL\mysql-5.5.16\bin>mysql -ussluser -pssluser -P3430 --ssl-key=buggg
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.5.16-log MySQL Community Server (GPL)

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> status
--------------
mysql  Ver 14.14 Distrib 5.5.16, for Win32 (x86)

Connection id:          11
Current database:
Current user:           ssluser@localhost
SSL:                    Cipher in use is DHE-RSA-AES256-SHA
Using delimiter:        ;
Server version:         5.5.16-log MySQL Community Server (GPL)
Protocol version:       10
Connection:             localhost via TCP/IP
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    cp850
Conn.  characterset:    cp850
TCP port:               3430
Uptime:                 35 min 26 sec

Threads: 1  Questions: 24  Slow queries: 0  Opens: 33  Flush tables: 1  Open tables: 0
Queries per second avg: 0.011
--------------

Remote Host:

C:\Documents and Settings>mysql -ussluser -pssluser -h192.168.1.100 -P3430 --ssl-key=buggg
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.5.16-log MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> status
--------------
mysql  Ver 14.12 Distrib 5.0.70, for Win32 (ia32)

Connection id:          6
Current database:
Current user:           ssluser@HOST-LAPTOP
SSL:                    Cipher in use is DHE-RSA-AES256-SHA
Using delimiter:        ;
Server version:         5.5.16-log MySQL Community Server (GPL)
Protocol version:       10
Connection:             192.168.1.100 via TCP/IP
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    latin1
Conn.  characterset:    latin1
TCP port:               3430
Uptime:                 13 min 13 sec

Threads: 2  Questions: 14  Slow queries: 0  Opens: 33  Flush tables: 1 Open tab
les: 26  Queries per second avg: 0.017
--------------

Again, I have no idea how many versions are affected by this yet. I’ve only tested 5.5.15 and 5.5.16 (seen on both Windows and Linux, as well).

In fact, that’s all I thought I would have needed to test, as I thought MySQL would have been all over this bug. But since there’s been no word from them about it, I feel it’s my duty to let the community know about this bug until it gets fixed.

(And I even wonder if the above is secure or not. I mean, it “says” the cipher is in use, but since I didn’t specify a ssl cert or key, how can I be certain this is secure.)


Period Panties by Period Panteez Menstrual Underwear Menstruation PMS Panty