PlanetMysql.ru — информация о СУБД MySQL

Managing SSL certificates for MySQL can be somewhat of a pain, especially when setting it up on multiple machines.

I looked around on the web, and didn’t really run into any mentions of how to do this for multiple machines, so I dug into it a bit more.

If you’ve created the certificates before, you’ll know you get prompted for various bits of information during a couple of the steps (country, state, email).

However, this can be by-passed by using the -batch option with the openssl command.

So, to set this up for multiple servers, just loop through the following 5 commands for your servers:

cd C:\mysql\certs
openssl genrsa 2048 > ca-key.pem
openssl req -new -x509 -nodes -days 3600 -batch -key ca-key.pem > ca-cert.pem
openssl req -newkey rsa:2048 -days 3600 -batch -nodes -keyout server-key.pem >
	server-req.pem
openssl x509 -req -in server-req.pem -days 3600 -CA ca-cert.pem
	-CAkey ca-key.pem -set_serial 01 > server-cert.pem

This will create the following 5 files:

ca-cert.pem, ca-key.pem, server-cert.pem, server-key.pem, and server-req.pem

Those are all that is needed to set up SSL for MySQL.

Then, just add the following lines to the [mysqld] section of your my.cnf/my.ini files:

ssl-ca	 = C:\mysql\certs\ca-cert.pem
ssl-cert = C:\mysql\certs\server-cert.pem
ssl-key	 = C:\mysql\certs\server-key.pem

Ensure you have an SSL user created, and then start up mysqld.

You can connect with the following command:

mysql -ussluser -p --ssl-key=

Connection Output:

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

Connection id:          1
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:                 8 sec

Threads: 1  Questions: 4  Slow queries: 0  Opens: 33  ...
--------------

Hope this helps.

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

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.)

This is more of a question than me sharing knowledge, but I'm testing the effects of MySQL SSL on replication delay. Interestingly, my tests show faster replication when I enable SSL, versus disable. The slave_compressed_protocol has an effect too, and I get the best result when I use SSL and slave compression in a non-CPU bound test.

The question came up, however, of what SSL Cipher I'm using. I'm not specifying one in my CHANGE MASTER statement, so I'm guessing I'm getting the same default cipher as if I connect with the regular mysql command line client with SSL, which is:

read more

So, just in case that wasn’t evil enough for you… perhaps you have something you want to know the MD5 checksum of. So, you could just do this:

drizzle> select md5('Hello World!');
+----------------------------------+
| md5('Hello World!')              |
+----------------------------------+
| ed076287532e86365e841e92bfc50d8c |
+----------------------------------+
1 row in set (0 sec)

But that is soooo boring.

Since we have the SSL libs already loaded into Drizzle, and using my very evil libtcc plugin… we could just implement it in C. We can even use malloc!

drizzle> SELECT LIBTCC("#include <string.h>\n#include <stdlib.h>\n#include <openssl/md5.h>\nint foo(char* s) { char *a = malloc(100); MD5_CTX context; unsigned char digest[16]; MD5_Init(&context); strcpy(a,\"Hello World!\"); MD5_Update(&context, a, strlen(a)); MD5_Final(digest, &context); snprintf(s, 33, \"%02x%02x%02x%02x%02x%02x%02x%02x%02x%02x%02x%02x%02x%02x%02x%02x\", digest[0], digest[1], digest[2], digest[3],digest[4], digest[5], digest[6], digest[7],digest[8], digest[9], digest[10], digest[11],digest[12], digest[13], digest[14], digest[15]); free(a); return 32; }") AS RESULT;


+----------------------------------+
| RESULT                           |
+----------------------------------+
| ed076287532e86365e841e92bfc50d8c |
+----------------------------------+
1 row in set (0.01 sec)

Currently the parameter is static in the C version due to me not having… well.. done a good job implementing the calling of C code.

So, just in case that wasn’t evil enough for you… perhaps you have something you want to know the MD5 checksum of. So, you could just do this:

drizzle> select md5('Hello World!');
+----------------------------------+
| md5('Hello World!')              |
+----------------------------------+
| ed076287532e86365e841e92bfc50d8c |
+----------------------------------+
1 row in set (0 sec)

But that is soooo boring.

Since we have the SSL libs already loaded into Drizzle, and using my very evil libtcc plugin… we could just implement it in C. We can even use malloc!

drizzle> SELECT LIBTCC("#include <string.h>\n#include <stdlib.h>\n#include <openssl/md5.h>\nint foo(char* s) { char *a = malloc(100); MD5_CTX context; unsigned char digest[16]; MD5_Init(&context); strcpy(a,\"Hello World!\"); MD5_Update(&context, a, strlen(a)); MD5_Final(digest, &context); snprintf(s, 33, \"%02x%02x%02x%02x%02x%02x%02x%02x%02x%02x%02x%02x%02x%02x%02x%02x\", digest[0], digest[1], digest[2], digest[3],digest[4], digest[5], digest[6], digest[7],digest[8], digest[9], digest[10], digest[11],digest[12], digest[13], digest[14], digest[15]); free(a); return 32; }") AS RESULT;


+----------------------------------+
| RESULT                           |
+----------------------------------+
| ed076287532e86365e841e92bfc50d8c |
+----------------------------------+
1 row in set (0.01 sec)

Currently the parameter is static in the C version due to me not having… well.. done a good job implementing the calling of C code.

Reminder, mark you callendar:

Wednesday January 27th, Join the Sun Startup Essentials Webinar on  Security for Web Applications.

A key success factor for Web startups is to protect their applications and data from different security threats. Join this webinar to learn about security challenges and about key solutions such as encryption, authentication, certificates, secure and fault-tolerant storage, chrooted environments. The Sun Startup Essentials experts will also cover how to implement these solutions at minimal cost by using standard and open components such as Solaris, Apache, MySQL, ZFS and more.

Registration limited to members of the Sun Startup Essentials program.

Your company is less than 6 year and  150 employee: Join Sun Startup Essentials >>