Thursday, March 10, 2011

Using SSL with MySQL

Since MySQL 4.0 it's possible to use SSL to secure the connections to MySQL. Unfortunately this is not used very often.

It can be used for cient-server connections and for replication. It will encrypt your traffic and prevent man-in-the-middle attacks if you're using ssl-verify-server-cert when connecting using mysql.

Securing database communication can be required to be compliant to some regulations.

There are other means of preventing anyone from snooping your database traffic like VPN, SSH Tunneling or stunnel.

As usual the documentation is quite good. The only issue with the docs is that the verification step is missing. (Bug #59628).

I'ts quite hard to debug as the OpenSSL messages are not reported in MySQL's errors. Bug luckily that's changing as Bug #21287 does have a patch under SCA/OCA.

And you should check your my.cnf for typo's when it's not working as mysqld won't tell you when it can't find the ssl-ca file. (Bug #59630).

I've used tcpdump to capture network traffic when SSL didn't work and loaded it into wireshark. Wireshark has very neat features to decode all kinds of traffic (HTTP, MySQL, SSL and even FC).

Also a debug build can provide you with some more info.

MySQL uses the same port for SSL and non-SSL connections. So you don't have to change your firewall rules. This also means that you can't force SSL on your firewall.

There are a few SSL status variables availables in the output of SHOW GLOBAL STATUS LIKE 'Ssl_%'; The documetation is here. The only issue is that it doesn't actually work. (Bug #59635). This is bad as statistics about renegotiation could indicate renegotiation vulnerabilities.

To force SSL you should use REQUIRE SSL or REQUIRE X509 with your CREATE USER or GRANT statements. Optionally you could use the REQUIRE SUBJECT, but that only works if the RDN order is how the server expects it to be (Bug #59376).

There are also some known issues with mixing OpenSSL and YaSSL your should be aware of.

Using SSL is very well possible, but there is room for improvement.

I haven't tested the performance impact of using SSL. I would also be interesting to see if the hardware SSL in the Sun T2 cpu would speed this up. (Sun T2 PDF). So Percona/Oracle: start your benchmarks!

As far as I known the MySQL branch/forks like Percona Server and MariaDB are using almost the same code/features for SSL as Oracle.

4 comments:

  1. I have slides from a presentation I did about setting up secure replication here:

    http://technocation.org/files/doc/2009_04_secure_rep.pdf

    ReplyDelete
  2. Hi, can I know whether or not you use wireshark to decode the mysql packet which is encrypted with openssl, if so, can I know how do you do it? I stuck on using wireshark to decode mysql packet which encrypted openssl.
    Thanks

    ReplyDelete
    Replies
    1. My answer: http://databaseblog.myname.nl/2014/07/decoding-encrypted-mysql-traffic-with.html

      Delete
  3. It very helpful! Thank you very much.

    Thanks

    ReplyDelete