Why TLS for MySQL is difficult

The internet has changed to a place where most protocols like HTTP etc now use secure connections with TLS by default.

While both HTTP and the MySQL Protocol use TLS for secure connections there are still many differences which make it difficult for MySQL to benefit from the same advancements as HTTP has seen in the last so many years.

What is TLS?

TLS stands for Transport Layer Security and is the successor of SSL (Socket Layer Security). SSL and TLS are often used interchangably, while this isn’t correct strictly speaking. What TLS provides is a standardized way to encrypt in transit traffic and authenticate the other end of the connection. TLS when used together with HTTP is known as HTTPS, for secure HTTP.

How TLS works in HTTPS

The client (webbrowser) connects to a server on port 443. Then negitiation is done to agree on what encryption method is to be used. The server presents the client with a certificate, which the client then verifies against the system trust store.

For this to work the server needs to have a certificate that is signed by a cerfificate authority (CA) that is known to the client.

The CA will sign the certificate after doing some checks. Nowadays this is often a fully automated process. There are multiple ways to prove ownership of the domain to the CA. You can put a special file on the webserver or add a DNS record. See ACME for details.

What this provides is that the server authenticates itself to the client and that the connection is secure.

How TLS works with MySQL

The client (MySQL Client, etc) connects to the server. When both the client and server set the CLIENT_SSL flag then the connection switches to TLS. So the TLS part doesn’t start at the beginning of the conversation as is the case with HTTPS. This allows the MySQL protocol to do both TLS and non-TLS connections on the same port, where HTTP and HTTPS use two ports (80 and 443).

Then the usual negitioation etc happens, but the server certificate is likely not signed by one of the CA’s in the system truststore, so the user might have to specify the certificate of the CA when setting up the connection. And when the user doesn’t specify a CA certificate the client might not verify the certificate of the server against a CA.

So for the server part there are multiple options: - MySQL can now generate a certificate on it’s own. But it won’t be signed by a proper CA, instead it is self signed. - You could setup your own CA and have that sign the certificate. For example with Lemur (From Netflix) or with a more bare bones OpenSSL setup as described here. - Get certificates from a proper CA, just like you would with HTTPS.

Both HTTPS and the MySQL protocol allow mutual-TLS or mTLS where not only the server authenticates to the client, but also the clients presents a certificate to the server. However this is much more common with MySQL.

Getting certificates for internal hostnames from a proper CA is hard

The common challenge types for Let’s Encrypt are all dependent on having a server that has a public DNS record. And while Let’s Encrypt supports wildcard certifictes, these are usually less secure as they are (obviously) less restrictive on hostname and are more valuable when they end up in the wrong hands. So the best practice is to not use wildcard certificates. Other CA often have similar issues.

You could have a public DNS entry for your database server, but not allow any direct access from the internet, but this does expose the hostname of your server to the internet, which might be a problem for some.

And even if you’re ok with a public DNS entry, this means that automating this is more difficult than setting up certbot with Nginx, Apache, etc. as those often rely on publishing a challenge token on a HTTP endpoint. One option is to use certbot --standalone, which provides it’s own builtin webserver for the challenge token, but this requires the server to have port 80 open to the internet.

It is common to have database servers with only internal IP’s (192.168.x.x, etc), which also makes this harder. And the limited supply of IPv4 addresses globally also doesn’t help, however the “easy” solution for that is IPv6.

My opinion on this: - Small organizations should rely on Let’s Encrypt or something similar and have database servers with a public DNS entry and a public IP and then rely on strict firewall rules for security. - Bigger organizations should consider to setup their own CA, including automated certificate renewal.

This resuls in insecure defaults

The MySQL client, MySQL Shell and most connecteors have a default ssl-mode that is set to PREFERRED. This means it will use TLS if available, but it won’t validate the certificate or the hostname.

SSL Mode	Encrypted communication	Prevents against downgrade	Prevents against MitM
DISABLED	❌	N/A	❌
PREFERRED	If available	❌	❌
REQUIRED	✅	✅	❌
VERIFY_CA	✅	✅	Mostly
VERIFY_IDENTITY	✅	✅	✅

With VERIFY_CA the server needs a valid certificate, but the hostname of the server and that of the certificate doesn’t have to match. This means that a stolen certificate and key for dev.example.com could be used to do a MitM attack on proddb1.example.com.

Note that with curl comes with a more secrue default, which is similar to VERIFY_IDENTITY. It allows you to go connect to an insecure server with --insecure. This is only possible because webservers are expected to have CA signed certificates.

Note that with the caching_sha2_password authentiction, which is now the default the password is send in plain text over TLS. So a MitM attack could reveal this password to an attacker.

As the MySQL connections are less likely to go over the public internet and as servers are less likely to have a certificate that can be easily verified without additional configuration this behavor seems reasonable to me. However I think making this more secure by default would be a good step to take.

The handshake makes it difficult to use some TLS offloading products

As the MySQL Protocol first needs the exchange of some packets before the TLS connection is setup.

This makes it more difficult for proxies/routers/loadbalancers/etc to support MySQL as they also need to implement a small subset of the MySQL protocol.

Note that OpenSSL can do this with openssl s_client --starttls mysql ....

Long lived and stateful connections

With HTTPS you may have a persistent connection, but if it fails you can just retry the connection and things are usually fine as the session is not directly linked to the connection. And connections usually don’t stay open for long.

With MySQL the connection is linked to the session. And there may be local variables, ongoing transactions etc that are linked to this session. So if the connection is gone, these are all gone and you have to re-authenticate and retry your transaction and set any variables that you modified before. Also it isn’t uncommon to have long running sessions for things like ALTER TABLE or heavy reporting etc.

This makes it more difficult to change the X.509 certificate and key that the server uses as just restarting would distrupt users.

How MySQL made TLS easier to use

There used to be many more issues with TLS but Oracle MySQL has been working hard over the last few years to improve things.

So what things improved? I’ll list a few:

• Clients and connectors where changed to use TLS by default. This isn’t a perfect situation yet, but it is better for security than not using TLS at all.
• MySQL Community Edition was changed to use OpenSSL instead of YaSSL. This is mostly a licensing thing. This helps a lot for performance and it also brought support for TLSv1.2 and TLSv1.3.
• First mysql_ssl_rsa_setup was introduced to generate TLS certificates and a RSA keypair. And eventually this was integrated into the server. Especially for development setups etc this makes this a lot easier.
• Support for reloading certificates, keys, etc without server restart.

How caching_sha2_password leaks passwords

Oracle recently announced a new authentication plugin: caching_sha2_password. This was added in 8.0.4, the second release candidate for MySQL 8.0. The new plugin is also made the default (can be configured by changing default_authentication_plugin.

Why?

Phasing out SHA1

As Oracle said in the blog post to annouce this change they want to move to a more secure hashing algorithm (SHA256). Which I think is a good reason to do this.

Adding salt

Adding a salt makes hashes for identical passwords, but different users different. Again a good reason to do this.

Performance

Their earlier attempt at this resulted in sha256_password. But this resulted in slower authentication. Without using persistent connections this is a serious limitation. So again a good reason.

What's wrong?

If you don't use SSL/TLS it gives your password away.

To protect against sending the password in cleartext over an insecure connection it encrypts the password before sending it. It does this by using public key cryptography. It encrypts the password with the public key of the server. Then the server can decrypt it with its private key. So far so good.

But the problem is how MySQL gets the public from the server. There is --get-server-public-key which requests the key from the server. But it does so over an insecure connection, so this isn't safe.

An attacker could do a Man-in-the-Middle attack and give you their public key... and then the attacker can decrypt your password and proxy the connection.

The second option is to use --server-public-key-path=file_name. But then you somehow need to collect all public keys from all your servers and securely distribute them to your clients. And you might want to renew these keys every year... this seems like an operational nightmare to me.

Also depending on what connector you use these options may not be available.

If you use SSL/TLS things are not much better.

With default settings mysqld generates self-signed X509 certificates and enables SSL/TLS. And the default ssl-mode is PREFERRED. This is better than the previous defaults as it guards against passive attacks. However this is NOT protecting against active attacks (MitM attacks) as MySQL won't verify if the certificate is signed by a known CA. It by default also doesn't verify if the hostname matches the certificate.

So if someone hijacks your connection and knowns how to do a SSL handshake: then the caching_sha2_password plugin will handover the password in clear text.

Can we use it in a secure way?

Use SSL/TLS and set ssl-mode to VERIFY_IDENTITY (or at least VERIFY_CA). Note that this requires you to configure MySQL with certificates which are signed by your CA and matches the hostnames of your servers.

In case you only need localhost connections: configure MySQL to only listen on local-loopback and you're done.

Staying with mysql_native_password seems also to be an acceptable option for now. Note that sha256_password has many of the same issues and should also be avoided without strict SSL/TLS settings.

I initially reported this to Oracle in Bug #79944 on 13 January 2016 for the sha256_password plugin in 5.7.10.

Network attacks on MySQL, Part 6: Loose ends

Backup traffic

After securing application-to-database and replication traffic, you should also do the same for backup traffic.

If you use Percona XtraBackup with streaming than you should use SSH to send your backup to a secure location. The same is true for MySQL Enterprise Backup. Also both have options to encrypt the backup itself. If you send your backup to a cloud service this is something you should really do, especially if it is not sent via SSH or HTTPS.

And mysqldump and mysqlbinlog both support SSL. And you could use GnuPG, OpenSSL, WinZIP or any other tool to encrypt it.

Sending credentials

You could try to force the client to send credentials elsewhere. This can be done if you can control the parameters to the mysql client. It reads the config from /etc/my.cnf, ~/.my.cnf and ~/.mylogin.conf but if you for example specify a login-path and a hostname.. it connects to that host, but with the password and username from the loginpath from the encrypted ~/.mylogin.cnf file.

You could use --enable-cleartext-plugin to make it even easier to get to the stored password. Note that if you have direct access to the ~/.mylogin.cnf file that there are options to decrypt it.

See Bug #74545: mysql allows to override login-path for details.

MySQL Cluster (NDB)

Make sure your machines use a private network (VLAN) which can only be accessed from cluster nodes. Your API nodes should be in this network and have a public interface where mysqld listens. Another option might be to use a firewall device or host based firewalls. Just make sure you are aware or the risks.

As usual thers is extensive documentation about this: MySQL Cluster Security and Networking Issues from the MySQL Reference Manual.

Network storage

And use proper security for iSCSI, NFS, FCP or any other kind of network storage you might be using. I've seen setups where iSCSI and/or NFS were publicly available and even with data-at-rest encryption this is not really safe, especially if read-write access is available.

Future

In both MySQL 5.6 and MySQL 5.7 Oracle improved the SSL/TLS support a lot. There are more improvements needed as a lot has changed in how SSL over the past 10 years. Assumptions made years ago are no longer true.

And also the creators of YaSSL have been busy: wolfSSL/mysql-patch on github

Network attacks on MySQL, Part 5: Attack on SHA256 based passwords

The mysql_sha256_password doesn't use the nonce system which is used for mysql_new_password, but instead forces the use of RSA or SSL.

This is how that works:

1. The client connects
2. The server changes authentication to sha256 password (or default?)
3. The server sends the RSA public key.
4. The client encrypts the password with the RSA public key and sends it to the server.
5. The server decrypts the password with the private key and validates it.

The problem is that the client trusts public key of the server. It is possible to use --server-public-key-path=file_name. But then you need to take care of secure public key distribution yourself.

So if we put a proxy between the client and the server and then have the proxy sent its own public key... then we can decrypt it and reencode it with the real public key and send it to the server. Also the decrypted password is the password, not a hash. So we then know the real password.

And if SSL is used it doesn't do the RSA encryption... but this can be a connection with an invalid certificate. Just anything as long as the connection is SSL.

Network attacks on MySQL, Part 4: SSL hostnames

In my previous blogs I told you to enable SSL/TLS and configure it to check the CA. So I followed my advice and did all that. Great!

So the --ssl-mode setting was used a few times as a solution. And it has a setting we didn't use yet: VERIFY_IDENTITY. In older MySQL versions you can use --ssl-verify-server-cert. Both turn on hostname verification.

The attack

Get any certificate which is trusted by the configured CA, this can for example be a certificate from a development machine. And use that with a man-in-the-middle proxy.

Then the client:

1. Checks if SSL is uses (--ssl-mode=REQUIRED)
2. Verify if the certificate is signed by a trusted CA (--ssl-mode=VERIFY_CA)

Both checks succeed. But the certificate might be for testhost01.example.com and the database server might be prod-websitedb-123.example.com.

Browsers by default verify hostnames, MySQL does not.

Turning on hostname validation

So use --ssl-mode=VERIFY_IDENTITY and everything should be fine?

Well that might work for simple setups, but would probably fail for more complex setups.

This is because you might have a master-slave setup with loadbalancer in front of it. So your webapp connect to mydb-prod-lb.example.com which might be served by mydb1.example.com (master) or mydb2.example.com (slave). There might or might not be any automatic read/write splitting.

So then just configure the loadbalancer be the endpoint of the SSL connection? Well no, because most loadbalancers don't know how to speak the mysql protocol, which is needed to setup the SSL connection.

Ok, then just configure both servers with the certificate for mydb-prod-lb.example.com and everything should work. And it does!

But then you want to change the replication connection to also use SSL, but now the certificates and hostnames don't match anymore as they connect directly.

The same might be true for mysqldump or mysqlbinlog instances running on a separate backup server.

But there is a X.509 extension available which can be used: 'SubjectAlternativeName' a.k.a. SAN. (Not to be confused with Storage Area Networking). This allows you to have a certificate with multiple hostnames.

So for both hosts put their own hostname and the loadbalancer hostname in there.

But unfortunately that doesn't work yet. MySQL doesn't support this.

See Bug #68052: SSL Certificate Subject ALT Names with IPs not respected with --ssl-verify-serve for more details.

So yes, do enable hostname verification, but probably not everywhere yet.

Network attacks on MySQL, Part 3: What do you trust?

In my previous blogs I told you to enable SSL/TLS and force the connection to be secured. So I followed my advice and did forced SSL. Great!

So now everything is 100% secure isn't it?

No it isn't and I would never claim anything to be 100% secure.

There are important differences in the SSL/TLS implementations of browers and the implementation in MySQL. One of these differences is that your browser has a trust store with a large set of trusted certificate authorities. If the website you visit has SSL enabled then your browser will check if the certificate it presents is signed by a trusted CA. MySQL doesn't use a list of trusted CA's, and this makes sense for many setups.

The key difference is that a website has clients (browsers) which are not managed by the same organization. And for MySQL connections the set of clients is often much smaller are more or less managed by one organization. Adding a CA for a set of MySQL connections if ok, adding a CA for groups of websites is not.

The result is that a self signed certificate or a certificate which is signed by an internal CA is ok. An public CA also won't issue a certificate for internal hostnames, so if your server has an internal hostname this isn't even an option. Note that the organization running public CA's sometimes offer a service where they manage your internal CA, but then your CA is not signed by the public CA.

But if you don't tell your MySQL client or application which CA's it should trust it will trust all certifictes. This allows an attacker to use a man-in-the-middle proxy which terminates the SSL connection between your client and the proxy and setup another connection to the server, which may or may not be useing SSL.

To protect against this attack:

1. Use the --ssl-ca option for the client to specify the CA certificate.
2. Use the --ssl-mode=VERIFY_CA option for the client.

You could use a CA for each server or a CA you use for all MySQL servers in your organization. If you use multiple CA's then you should bundle them in one file or use --ssl-capath instead.

Network attacks on MySQL, Part 2: SSL stripping with MySQL

Intro

In my previous blog post I told you to use SSL/TLS to secure your MySQL network connections. So I followed my advice and did enable SSL. Great!

So first let's quickly verify that everything is working.

So you enabled SSL with mysql_ssl_rsa_setup, used a OpenSSL based build or put ssl-cert, ssl-key and ssl-ca in the mysqld section of your /etc/my.cnf and now show global variables like 'have_SSL'; returns 'YES'.

And you have configured the client with --ssl-mode=PREFERRED. Now show global status like 'Ssl_cipher'; indicates the session is indeed secured.

You could also dump traffic and it looks 'encrypted' (i.e. not readable)...

With SSL enabled everything should be safe isn't it?

The handshake which MySQL uses always starts unsecured and is upgraded to secured if both the client and server have the SSL flag set. This is very similar to STARTTLS as used in the SMTP protocol.

To attach this we need an active attack; we need to actually sit in between the client and the server and modify packets.

Then we modify the flags sent from the server to the client to have the SSL flag disabled. This is called SSL stripping.

Because the client thinks the server doesn't support SSL the connection is not upgraded and continues in clear text.

An example can be found in the dolfijn_stripssl.py script.

Once the SSL layer is stripped from the connection an attacker can see your queries and resultsets again as described before.

To protect against this attack:

1. Set REQUIRE SSL on accounts which should never use unencrypted connections.
2. On the client use --ssl-mode=REQUIRED to force the use of SSL. This is available since 5.6.30 / 5.7 11.
3. For older clients: Check the Ssl_cipher status variable and exit if it is empty.

Network attacks on MySQL, Part 1: Unencrypted connections

Intro

In a set of blog posts I will explain to you how different attacks on the network traffic of MySQL look like and what you can do to secure your systems againt these kinds of attacks.

How to gain access

To gain access to MySQL network traffic you can use tcpdump, dumpcap, snoop or whatever the tool to capture network packets on your OS is. This can be on any device which is part of the connnection: the server, the client, routers, switches, etc.

Besides application-to-database traffic this attack can also be done on replication traffic.

Results

This allows you to extract queries and result sets.

The default password hash type mysql_new_password uses a nonce to protect against password sniffing. But when you change a password this will be sent accross the wire by default. Note that MySQL 5.6 and newer has some protection which ensures passwords are not sent to the logfiles, but this feature won't secure your network traffic.

In the replication stream however there are not as many places where passwords are exposed. This is true especially for row based replication, but even for statement based replication this can be true.

Some examples:

SET PASSWORD FOR 'myuser'@'%' = PASSWORD('foo'); -- deprecated syntax
UPDATE secrets SET secret_value = AES_ENCRYPT('foo', 'secret') WHERE id=5;

For both the password and the encryption key this can be seen in plain text for application-to-server traffic, but not for RBR replication traffic.

There is a trick to make this somewhat more secure, especially on 5.5 and older:

SELECT PASSWORD('foo') INTO @pwd;
SET PASSWORD FOR 'myuser'@'%' = @a;

If your application stores passwords in MySQL: You're doing it wrong. If your application stores hashed passwords (w/ salt, etc): If the hashing is done in your application: this is ok. But note that a man-in-the-middle might send a slightly altered resultset to your application and with this gain access to your application, but that requires an active attack.

This attacks for this level are mostly passive, which makes it hard to detect. An attacker might snif password hashes for your appliation and brute force them and then login to your application. The only thing you will see in your logs is a successful login...

To protect against this attack:

1. Use SSL/TLS
2. Encrypt/Decrypt values in the application before inserting it in the database.
3. Use a SSH tunnel (Workbench has built-in support for this)
4. Use a local TCP or UNIX domain socket when changing passwords.[1]
5. Don't use the MySQL protocol over the internet w/o encryption. Use a VPN or SSH.

For sensitive data you preferably should combine 1. and 2. Item 3. and 4. are mostly for ad-hoc DBA access.

Keep in mind that there might be some cron jobs, backups etc. which also need to use a secure connection. Ofcourse you should also protect your data files and backup files, but that's not what this post is about.

[1] It is possible to snoop on UNIX domain socket traffic, but an attacker who has that access probably has full system access and might more easily use an active attack.

About Oracle MySQL and CVE-2016-6662

The issue

On 12 September 2016 (three days ago) a MySQL security vulnerability was announced. The CVE id is CVE-2016-6662.

There are 3 claims:

1. By setting malloc-lib in the configuration file access to an OS root shell can be gained.
2. By using the general log a configuration file can be written in any place which is writable for the OS mysql user.
3. By using SELECT...INTO DUMPFILE... it is possible to elevate privileges from a database user with the FILE privilege to any database account including root.

How it is supposed to be used

1. Find an SQL Injection in a website or otherwise gain access to a MySQL account.
2. Now create a trigger file (requires FILE privilege)
3. Now in the trigger or otherwise use SET GLOBAL general_log_file etc to create a my.cnf in the datadir with the correct privileges. Directly using SELECT...INTO DUMPFILE...won't work as that would result in the wrong permissions, which would cause mysqld/mysqld_safe to ignore that file.
4. Now wait someone/something to restart MySQL (upgrade, daily cold backup, etc) and a shell will be available on a port number and IP address chosen by the attacker.

How it is fixed

The document claims "Official patches for the vulnerability are not available at this time for Oracle MySQL server. ", but that isn't true.

From the 5.7.15 release notes:

• mysqld_safe attempted to read my.cnf in the data directory, although that is no longer a standard option file location. (Bug #24482156)
• For mysqld_safe, the argument to --malloc-lib now must be one of the directories /usr/lib, /usr/lib64, /usr/lib/i386-linux-gnu, or /usr/lib/x86_64-linux-gnu. In addition, the --mysqld and --mysqld-version options can be used only on the command line and not in an option file. (Bug #24464380)
• It was possible to write log files ending with .ini or .cnf that later could be parsed as option files. The general query log and slow query log can no longer be written to a file ending with .ini or .cnf. (Bug #24388753)
  

The last two items are also in the 5.6.33 release notes.

So 2 out of the 3 vulnerabilities are patched. So the obvious advice is to upgrade.

Further steps to take

But there are more things you can do to further secure your setup.

Check if your my.cnf file(s) are writable for the mysql user

/etc/my.cnf, /etc/mysql/my.cnf /etc/mysql/my.cnf.d/* should NOT be writable for the mysql user. Make sure these are owned by root and mode 644.

Put an empty my.cnf in your datadir and make sure it has the above mentioned privileges. The vulnerability document also mentions a .my.cnf in the datadir, so also make that an empty file.

Review accounts with the FILE privilege:

Run this query and drop accounts or revoke the file privilege from them if they don't really need it.
SELECT GRANTEE FROM INFORMATION_SCHEMA.USER_PRIVILEGES WHERE PRIVILEGE_TYPE='FILE';

Isolate services

Don't run all services on one machine. Isolate services from each other. So put the webserver and database server on separate (virtual) machines or containers.

Use a firewall. If the database suddenly starts to listen on a weird port the attacker should not be able to connect to it. This can be a host based firewall like iptables and a network device. Yes an IDS might be able to detect the network shell, but running an IDS/IPS needs serious amount of time and doesn't give any guarantees.

Prepare for the next vulnerability

This is not only for MySQL, but also for other parts of your stack (OS, webserver, etc).

Make sure the configuration is secured properly for each service. A helpful resource here are the benchmark documents from the Center for Internet Security.

CIS Oracle MySQL Community Server 5.6 Benchmark v1.0.0

Today we released The CIS Oracle MySQL Community Server 5.6 Benchmark v1.0.0

This is a benchmark which can be used to secure your MySQL installation.

Different SSL setups for MySQL

In this blog post I will describe different ways of using SSL with the MySQL database server.

What does SSL give you?

You might use MySQL replication over the internet or connect to MySQL over the internet.

Another posibility is that you connect over an enterprise network to which just too many people have access. This is especially an issue if you use an BYOD network.

SSL helps here by encrypting the network traffic to prevent against evesdropping. It also validates that you're talking to the correct server to prevent man-in-the-middle attacks.

And you can also use SSL client certificates together with an password as two factor authentication.

SSL is not the only option, you could use SSH and many MySQL GUI clients like MySQL Workbench support this. But you can't easily use SSH with a python script or mysqldump.

Things that could go wrong

Using SSL is almost always better than not using SSL at all. So there is not much you could do wrong.

But there are a few things that could go wrong:

A false sense of security

You think you are protected by using SSL, but you forgot some option somewhere and the connection falls back to a non-SSL connection. Make sure you set SSL as required so this can't happen and use Wireshark or a similar tool to inspect the traffic to see if it is really encrypted.

Not renewing certificates in time

You should have some mechanism in place to notify you that your certificate is about to expire. This could be a nagios check, a note in your calendar or an email alert from your CA. If you let your certificates expire then your clients should stop with an error.

Performance

If performance is important then you should benchmark with and without SSL to see what the impact is. You could try a different cipher and try both OpenSSL and YaSSL to see what performs best.

Tools which use traffic inspection

If you are using a tool like VividCortex, pt-query-digest based on a pcap file then you should make sure these continue to work after you've deployed SSL. This can be done by giving them your key and use a cipher which doesn't use Diffie-Hellman (DH) or let them use a different source like performance_schema and/or your slow query log. But this depends on what is supported with that application. This might also includes some load balancer setups.

How SSL with MySQL is different to SSL in your browser

The browser by default has a big list of trusted CA's. MySQL doesn't trust anything by default. So that a big difference. This is more similar to how OpenVPN uses SSL.

For both a MySQL server and a Web server you have to enable SSL. And with both it's possible to require client certificates, but that's much more common with MySQL.

There are some other minor differences like protocol support: MySQL only uses TLS v1.0. And MySQL doesn't use hostname validation by default, so your certificate might be for db1.example.com and might be used on the host db2.example.com. Your browser might use OCSP, CRL's or CRLsets to verify if a certificate is still valid. MySQL is only capable of using a CRL sice MySQL 5.6.

Setup 1: Server/Client with Internal CA

This is the most basic setup and where I created mysslgen for.

You should create a key for the server in PKCS#1 format as PKCS#8 won't work. And then create a CSR (Certificate Signing Request) which can be signed by your CA. The end result is a CA certificate a server certificate and a server key. I always use PEM formatted certificates.

What you need to configure on the server:

• SSL server certificate and key (ssl-cert and ssl-key)
• SSL CA certificate (ssl-ca)

What you need to configure on the client:

• SSL CA certificate (ssl-ca)

To require the use of SSL for an account you should use REQUIRE SSL with your GRANT statement.

Setup 2: Server/Client with Internal CA and client certificate

For this you need to enable SSL on the server and create a certificate for the client.

On the client you need to specify the client certificate and key with ssl-cert and ssl-key.

Now you must use REQUIRE X509 to require a valid certificate. This won't work if you use the CA for more than this one MySQL server as other people might be able to get a valid certificate.

You might use REQUIRE SUBJECT '/CN=myclient.example.com' to limit the certificates which can be used.

In this setup each certificate is issued by your CA this gives you a lot of control about which certificates are issued. The result is that all certificates from your CA are trusted.

Setup 3: Server/Client with Public CA

This is similar to setup 2. But a public CA will issue much more certificates and will probably also revoke some of them.

Here you should use --ssl-verify-server-cert to make sure the server has a certificate which matches the hostname. Otherwise someone might use a certificate which is valid, but just not for that hostname to create a man in the middle attack.

Also you should use REQUIRE X509 and require a SUBJECT and optionally require a ISSUER for each user.

Setup 4: Server/Client with multiple public CA's

This looks almost like what webbrowsers use.

You could either use a certificate bundle (one file with multiple certificates) and specify this file with ssl-ca.

On Ubuntu this is made available by the ca-certificates package on /etc/ssl/certs/ca-certificates.crt

Another option is to specify a ssl-capath instead of using ssl-ca. Then you could set it to /etc/ssl/certs . If you want to use a custom dir with CA's then you need to run the c_rehash utility from OpenSSL on it to generate the correct symlinks. This only works if you use OpenSSL.

Setup 5: Replication

This works by setting MASTER_SSL_* parameters in the CHANGE MASTER TO command.

Make sure to use MASTER_SSL_VERIFY_SERVER_CERT if this is possible to enable hostname verification.

The only way to be really sure that the connection is using SSL is to set SSL as required for the replication user. The output of SHOW SLAVE STATUS output will only show if SSL is allowed, not that it's in active use.

One CA per server or a centralized CA?

You could use a CA per server and then issue a client certificate for each client. But then you will need a different client certificate for each server you want to connect to. Also you would have much more CA certificates to distribute.

So you might want to use one CA instead of a per-server CA.

What to do if things go wrong

If one of the private keys (client, server or CA) get's compromised there are a few things you can do:

1. Nothing, just wait till the certificate expires. This only works if you are using short lived certificates (e.g. certificates which are valid for 7 days). This will still leave a window in which you are vulnerable. You might have another control in place which mitigates this risk. The problem with this is that you need to restart MySQL every 7 days and you need some mechanism to distribute the certificates in an automated fashion.

2. Re-create all certificates and keys (including the CA). This might only be feasable if you have a very small CA with just a few users and one or more servers.

3. Use a CRL This is a Certificate Revocation list. This only works if your certificates have proper serial numbers. Also if you use multiple CA's then you must have a CRL for each and then bundle them in one file and specify that with ssl-crl or in a directory with symlinks created by c_rehash and specified with ssl-crlpath (OpenSSL only). If you use CRL's then you need to use MySQL 5.6 or newer and have some method for updating the CRL, also for all clients.

Note: You must always re-create the private key if leaked, using the old CSR (Certificate Signing Request) is not enough.

Note on client configuration

The ssl-ca parameter might be set in the [client] section of your my.cnf. However mysqlbinlog then stops working. So use loose-ssl-ca in the client section instead.

The ssl-key and ssl-cert should be in the [client] section of your ~/.my.cnf and those file should be properly protected.

It is currently not possible to add SSL settings the a 'login-path' as created by my_config_editor.

Note on ciphers

With ssl-cipher you can set a certain cipher, but with YaSSL this is quite limited. When you use OpenSSL you could use a cipher suite, which is a certain set of ciphers with some filters and an order.

Note on YaSSL, OpenSSL and status variables

It's hard to determine if your MySQL server uses OpenSSL or YaSSL. There are some methods like using ldd /path/to/mysqld. By default MySQL Community Edition uses YaSSL and MySQL Enterprise Edition uses OpenSSL.

Depending on the SSL implementation some status variables might not get updated correctly.

To make sure you're fully protected against any SSL vulnerabilities you should follow the Oracle Critical Patch Update and keep your MySQL and OpenSSL packages up to date.

Note on running a CA

This is not as easy as it seams. It is easy to get started, but doing it right is hard. Make sure you understand how to issue a CRL, what X509 extensions are and which of them you must use.

One of the many websites which tells you how to setup your own CA: How to act as your own certificate authority (CA) - jamielinux

Also many of the public CA's offer to host your private CA for you.

Books on SSL/TLS

There is a book about SSL I can recommend: Bulletproof SSL and TLS

And there is a very entertaining book about the history of cryptography: The Code Book

Using a CRL with MySQL

So assume you just uploaded the certificate you use to identify yourself to the MySQL server to Github or some other place it doesn't belong...and there is no undelete.

First: Don't panic.
Often a password is required besides a certificate to connect to the server. So someone with the certificate can't use it without the password. The certificate itself might be protected by a password, but that's really rare. Also access to MySQL and/or your account should be limited to certain IP's.

The next step is to revoke the certificate. This is possible since MySQL 5.6.3 by using a Certificate Revocation List (CRL).
A CRL is a list of the serials of the revoked certificates and signed by the CA. So this will only work if the certificates have unique serials.

To get the serial of a certificate with OpenSSL:
$ openssl x509 -in client-cert.pem -noout -text | grep 'Serial Number' 
Serial Number: 3 (0x3)

To get the serial of a certificate with GnuTLS:
$ certtool --certificate-info --infile client-cert.pem | grep 'Serial Number' 
Serial Number (hex): 03

If all certificates have the same serial, then you can't revoke them and you have to create a new CA and all other certificates.

If you do have unique serials for your certificates then you can use GnuTLS to create the CRL:

$ certtool --generate-crl --load-ca-privkey=CAkey.pem \
--load-ca-certificate=CAcert.pem --outfile=CAcrl.pem

Then you will be asked in how many days the next CRL will be issued and what the CRL number is.

With OpenSSL you can use the 'ca' command with the -gencrl option, but that requires a bit of a setup.

If you have the CRL file then you can use --ssl-crl option for the server and clients to let them know which certificates are revoked. It looks like MySQL Workbench can't be configured to use CRL's, but it should work for most other tools and connectors.

The major drawback of using a CRL is that you have to update the file for all servers and clients if you revoke a certificate.

With OpenSSL you can use openssl crl -in CAcrl.pem -noout -text to view the CRL contents and with GnuTLS you can use certtool --infile=CAcrl.pem --crl-info

If you use a public CA for your MySQL certificates then the CA can revoke the certificate, but then you still need to get the latest CRL from your CA and distribute it to all servers and clients. This is because MySQL doesn't fetch the CRL from the 'CRL Distribution Points' URL's which may be embedded in the certificates.

Disabling old_passwords=1

It is possible to disallow users from using old_passwords=1. This can be done by adding 'maximum-old_passwords=0' to your my.cnf

This prevents users from generating passwords hashes in pre-4.1 format. In MySQL 5.7 old_passwords=1 is already disabled, so this is only useful on 5.6 and earlier.

Be aware that this also restricts old_passwords=2 which is needed for sha256 hashes.

mysql> select @@old_passwords;
+-----------------+
| @@old_passwords |
+-----------------+
|               0 |
+-----------------+
1 row in set (0.00 sec)

mysql> set old_passwords=1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+----------------------------------------------+
| Level   | Code | Message                                      |
+---------+------+----------------------------------------------+
| Warning | 1292 | Truncated incorrect old_passwords value: '1' |
+---------+------+----------------------------------------------+
1 row in set (0.00 sec)

mysql> select @@old_passwords;
+-----------------+
| @@old_passwords |
+-----------------+
|               0 |
+-----------------+
1 row in set (0.00 sec)

CIS Oracle MySQL 5.6 Security Benchmark

I've been working on the CIS security benchmark for Oracle MySQL 5.6. There already is a benchmark document for earlier versions of MySQL.

The benchmark document helps you to ensure you have a secure configuration for MySQL.

Center for Internet Security (CIS) is a nonprofit organization.

The Call For Participation is now open.

If you are already a CIS member then you can download the document here. If you don't have an account you can register for free.

Oracle Critical Patch Update for MySQL

Oracle has released the Critical Patch Update (CPU) for July 2014. The Oracle MySQL Risk Matrix lists 10 security fixes.

It took me some time to understand the subcomponent names. So here is the list with the full name of each subcomponent:

Subcomponent	Full name
SRFTS	Server: Full Text Search
SRINFOSC	Server: INFORMATION_SCHEMA
SRCHAR	Server: Character sets
ENARC	Engine: Archive
SROPTZR	Server: Optimizer
SRREP	Server: Replication
SRSP	Server: Stored Procecure
ENFED	Engine: Federated

I don't think there is anything really important in the list, but it might be a good trigger to update to the latest release.

Upgrading should be easy especially if you're using the APT or YUM repositories from Oracle. If you're upgrading to a new major release (e.g. from 5.5 to 5.6) then you should read the instructions in the reference manual.

Notes on the AES encryption in MySQL

Oracle has improved the AES encryption/decryption functions in MySQL 5.6.17. They improved it a lot and posted a blog which explains all the details.

If you would like to know more about encryption there are two resources I would recommend:

• The Code Book by Simon Singh. This is about the history of cryptography, but it also includes a lot of information about crypto which is currently in use. This is also a very entertaining read.
• Crypto 101, a free/opensource book which gives a intro to crypto. The webpage also has a video of the talk on which the book is based.

And if you're going to use the AES encryption functions in MySQL there are some things you should consider:

• Connections with a UNIX socket (or shared memory on Windows) should be safe. Local TCP/IP connections are also safe in most circumstances.
• Remote connections which use the AES encryption should use a protected connection (SSL, VPN, etc). Otherwise someone might be able to sniff the network traffice which contains your encryption key.
• Replication traffic should also be protected (especially if you're using statement based replication. Mixed or row based might be safer).
• There is a trick to prevent the key to be logged:
• SET @encrypted := AES_ENCRYPT('foo', 'bar');
• INSERT INTO t1(c1) VALUES(@encrypted);
• MySQL 5.6 prevents logging of passwords, but this doesn't include the AES functions (yet). (Bug #72158). So be careful with your logging.
• It is possible to use the AES_DECRYPT in a view definition, but then your probably storing your key with your data, which defeats the purpose. The same is true for storing your key in a stored procedure, function or a virtual column in case of MariaDB.

And there are alternatives to using encryption within MySQL: Your application could encrypt the data before it's sent over the network. And depending on why you need encryption it might also be possible to encrypt the whole data directory. This can be achieved with LUKS or eCryptFS if you're on Linux or ZFS if you're on Solaris. On Windows you might use the EFS feature of NTFS or use Bitlocker.

The AES functions in MySQL are a good solution if you only want to encrypt specific data (which is often the case) or if you need to search on the decrypted data (WHERE AES_DECRYPT(..)='foo')

MySQL in Oracle Critical Patch Update Advisory January 2014

Oracle has released the Critical Patch Update (CPU) advisory for January 2014.

The affected MySQL products are:

• Oracle MySQL Enterprise Monitor, versions 2.3, 3.0 

• Oracle MySQL Server, versions 5.1, 5.5, 5.6

So this means that you should consider updating MySQL. For MySQL Enterprise the updates should be available on My Oracle Support and for the Community version the new versions are on the regular download locations. I guess the official repositories are already updated.

For MySQL 5.6 you should upgrade to 5.6.15
For MySQL 5.5 you should upgrade to 5.5.35
For MySQL 5.1 you should upgrade to 5.1.73

If you use the MySQL release from your distribution you should ask them to fix the CVE numbers fixed in these releases (if they didn't do that already).

The highest CVSS score for MEM is 10 and for MySQL the highest score is 6.8.

Update: for more information see this blog post by Dave Stokes.

Playing hid-and-seek with databases

As far as I know there isn't a well accepted set of best practices for MySQL, but there are many best practices known and used by most MySQL DBA's. One of those best practices is that the datadir must not be equal to a mountpoint; it has to be a subdirectory of a mountpoint. I learned this the hard way a few years ago when I used a NetApp Filer via NFS as a data directory. The NetApp filer exposed the snapshots via a .snapshot directory. A database in MySQL is a directory, so MySQL thought that the .snapshot directory was a database. This resulted in some issues with our monitoring scripts, so we had to create a mysql_data directory and move all the databases to that directory.

For other setups directories like lost+found, .zfs, etc. gave similar issues.

In MySQL 5.6 a new feature was introduced to make it possible to make some databases hidden. To do this a ignore-db-dir option needs to be specified for each directory. On a running server the list of ignored database directories is shown in the ignore_db_dirs variable.

mysql> SHOW DATABASES LIKE 'testdir%';
+---------------------+
| Database (testdir%) |
+---------------------+
| testdir3            |
+---------------------+
1 row in set (0.00 sec)

mysql> \! ls -ld data/testdir*
drwxrwxr-x 2 dveeden dveeden 4096 May 26 10:06 data/testdir1
drwxrwxr-x 2 dveeden dveeden 4096 May 26 10:06 data/testdir2
drwxrwxr-x 2 dveeden dveeden 4096 May 26 10:06 data/testdir3
mysql> SELECT @@ignore_db_dirs;
+-------------------+
| @@ignore_db_dirs  |
+-------------------+
| testdir1,testdir2 |
+-------------------+
1 row in set (0.00 sec)

mysql> \! grep ignore-db-dir my.sandbox.cnf
ignore-db-dir='testdir1'
ignore-db-dir='testdir2'

This helps a lot if there are foreign directories in the datadir, but it's always better to not have those directories there as it might be loaded into the database with a LOAD DATA statement or it might get deleted with a DROP DATABASE statement. So the best practices is still to have a data directory which is a subdirectory of a mountpoint, not the mountpoint itself.

A hidden directory is still available and can be used, it's just not shown in the output of various commands.

How MySQL 5.6 handles passwords more securely

There are many thing changed in MySQL 5.6 which are related to passwords:

• There is a new password hash algorithm (SHA-256)
• There is obfuscation for passwords with the .mylogin.cnf file.
• The option to store slave passwords in a database table.
• It's now possible to supply a password to START SLAVE.

But that's not what this blog post is about.

This blog post is a great new feature: Hiding passwords from your log files, automatically.

MySQL 5.6 will by default hide passwords from the general log. This is not just obfuscation as only the one-way hash will be put in the log files. By setting log-raw=OFF you can disable password hiding for the general log. The log-raw setting will only influence the general log, so the passwords in the slow query log and the binary logs will still be hidden.

With MySQL 5.5 this could be done manually by first storing the hash in a variable, but like many other work-arounds this is not needed anymore.

But it doesn't stop there. According to the manual the mysql client now won't log statements which match "*IDENTIFIED*:*PASSWORD*".

But don't forget that MySQL Server logfiles and MySQL Client logfiles should still be protected with the correct file permissions and the same is true for all other files like master.info. And if you store slave credentials inside the database these should be protected by using the MySQL privileges system, but that's even true if master.info is used as someone with too many database privileges might just load the contents with LOAD DATA INFILE.

Of course there there is still room for improvement:
It would be nice if we could easily enable connection security. MySQL and many other applications use TLS, but that has some performance overhead and isn't really easy to setup. And MySQL is still not secure-by-default, but easy-by-default...

So MySQL 5.6 improves security without needing any special configuration.

Avoid clear text passwords in MySQL logging.

What happens when you use the PASSWORD() function to insert a password hash into a table?

• The hash will be written to the table
• The password might be written in clear text to the binlog
• The password might be written in clear text to the general log
• The password might be written in clear text to the slow query log

The query

mysql [localhost] {msandbox} (test) > INSERT INTO testpwd(pwd) VALUES(PASSWORD('secret_password'));
Query OK, 1 row affected (0.00 sec)

The General log

130128 16:04:41     1 Query     INSERT INTO testpwd(pwd) VALUES(PASSWORD('secret_password'))

The Slow query log

# Time: 130128 16:04:41
# User@Host: msandbox[msandbox] @ localhost []
# Query_time: 0.004887  Lock_time: 0.001043 Rows_sent: 0  Rows_examined: 0
SET timestamp=1359385481;
INSERT INTO testpwd(pwd) VALUES(PASSWORD('secret_password'));

The binlog:

# at 219
#130128 16:04:41 server id 1  end_log_pos 287  Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1359385481/*!*/;
BEGIN
/*!*/;
# at 287
#130128 16:04:41 server id 1  end_log_pos 315  Intvar
SET INSERT_ID=1/*!*/;
# at 315
#130128 16:04:41 server id 1  end_log_pos 438  Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1359385481/*!*/;
INSERT INTO testpwd(pwd) VALUES(PASSWORD('secret_password'))
/*!*/;
# at 438
#130128 16:04:41 server id 1  end_log_pos 465  Xid = 8
COMMIT/*!*/;

It's possible to do it in a more secure way by using variables.

The query

mysql [localhost] {msandbox} (test) > SET @pwd := PASSWORD('another_secret_password');
Query OK, 0 rows affected (0.00 sec)

mysql [localhost] {msandbox} (test) > INSERT INTO testpwd(pwd) VALUES(@pwd);
Query OK, 1 row affected (0.01 sec)

The General log

130128 16:05:18     1 Query     SET @pwd := PASSWORD('another_secret_password')
130128 16:05:30     1 Query     INSERT INTO testpwd(pwd) VALUES(@pwd)

The Slow query log

# Time: 130128 16:05:18
# User@Host: msandbox[msandbox] @ localhost []
# Query_time: 0.000251  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 0
SET timestamp=1359385518;
SET @pwd := PASSWORD('another_secret_password');
# Time: 130128 16:05:30
# User@Host: msandbox[msandbox] @ localhost []
# Query_time: 0.003031  Lock_time: 0.000288 Rows_sent: 0  Rows_examined: 0
SET timestamp=1359385530;
INSERT INTO testpwd(pwd) VALUES(@pwd);

The binlog

# at 465
#130128 16:05:30 server id 1  end_log_pos 533  Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1359385530/*!*/;
BEGIN
/*!*/;
# at 533
#130128 16:05:30 server id 1  end_log_pos 561  Intvar
SET INSERT_ID=2/*!*/;
# at 561
#130128 16:05:30 server id 1  end_log_pos 638  User_var
SET @`pwd`:=_utf8 0x2A41424645453834453346463233423442454338323635383832433244383141414536363744363235 COLLATE `utf8_general_ci`/*!*/;
# at 638
#130128 16:05:30 server id 1  end_log_pos 738  Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1359385530/*!*/;
INSERT INTO testpwd(pwd) VALUES(@pwd)
/*!*/;
# at 738
#130128 16:05:30 server id 1  end_log_pos 765  Xid = 10
COMMIT/*!*/;

An other trick is to set binlog_format to ROW:

# at 555
#130128 16:30:47 server id 1  end_log_pos 623   Query   thread_id=2     exec_time=0     error_code=0
SET TIMESTAMP=1359387047/*!*/;
BEGIN
/*!*/;
# at 623
# at 672
#130128 16:30:47 server id 1  end_log_pos 672   Table_map: `test`.`testpwd` mapped to number 33
#130128 16:30:47 server id 1  end_log_pos 752   Write_rows: table id 33 flags: STMT_END_F

BINLOG '
p5kGURMBAAAAMQAAAKACAAAAACEAAAAAAAEABHRlc3QAB3Rlc3Rwd2QAAggPAv8AAg==
p5kGURcBAAAAUAAAAPACAAAAACEAAAAAAAEAAv/8BQAAAAAAAAApKkNFNDdGODRBOThDOTI0ODdC
RjI5MUM1QzIyNTY3ODg0RjAxMjdGM0U=
'/*!*/;
# at 752
#130128 16:30:47 server id 1  end_log_pos 779   Xid = 19
COMMIT/*!*/;

And even with the "-v" option for mysqlbinlog the passwords won't show with row based binlogs:

### INSERT INTO `test`.`testpwd`
### SET
###   @1=5
###   @2='*CE47F84A98C92487BF291C5C22567884F0127F3E'

With 5.6 hiding passwords should get done automatically and for all the log types. Unfortunately I didn't get it to work yet. (Bug #68200)

To prevent logging of passwords you need to

• Disable the slow query log
• Disable the general log
• Use variables or disable the binlog
• Or wait on the 5.6 GA version