Visualizing the MySQL Bug Tide (2018 edition)

I've updated the bug tide graph I made in 2016 with today's data. The source code and details are here.

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.

MySQL and SSL/TLS Performance

In conversations about SSL/TLS people often say that they either don't need TLS because they trust their network or they say it is too slow to be used in production.

With TLS the client and server has to do additional work, so some overhead is expected. But the price of this overhead also gives you something in return: more secure communication and more authentication options (client certificates).

SSL and TLS have existed for quite a long time. First they were only used for online banking and during authentication on web sites. But slowly many websites went to full-on SSL/TLS. And with the introduction of Let's encrypt many small websites are now using SSL/TLS. And many non-HTTP protocols either add encryption or move to a HTTP based protocol.

So TLS performance is very important for day-to-day usage. Many people and companies have put a lot of effort into improving TLS performance. This includes browser vendors, hardware vendors and much more.

But instead of just hoping for good performance: Let's try to measure it with a simple benchmark.

There are multiple pieces of a database connection we have to benchmark:

1. New connections
2. Reconnecting
3. Bulk transfer

 And for all of these there are multiple things we can measure:

1. Connect and/or transfer time (performance)
2. CPU usage (efficiency)
3. Concurrency 

The benchmark code can be found here: https://github.com/dveeden/mysql_go_tls

Let's look at connection performance. In this test I connect a number of times to MySQL  and do a "DO 1". This is on a localhost TCP connection, so it should be fast.

This is the connection time in ms for a single connection.
With 5.6.33 Community Edition, which is YaSSL based we see a very noticable overhead. And with 5.7.17 Community Edition this overhead is much smaller, but still very noticable.

Then MySQL 5.7 with OpenSSL (compiled on Fedora 25) shows another very noticable improvement over YaSSL. This can be explained because in this case the AVX2 and AES-NI CPU features can be used.

Also OpenSSL supports TLS tickets and YaSSL doesn't. This is why the yellow bar is much shorter that the orange bar. This is not yet supported in libmysqlclient, see Bug #76921 for details.

So SSL/TLS can be slow, but doesn't have to be slow.

Note that TLS needs multiple roundtrips. When testing this with netem on Linux I see this with MySQL 5.7.18 (YaSSL) and a 5ms delay:
No TLS goes from 0.5ms to 52ms
TLS goes from 8ms to 85ms

The second thing to measure is bulk performance. This is for large result sets including mysqldump.

With mysqldump from MySQL 5.7 it is easy to do:

$ time mysqldump --ssl-mode=disabled -A > /dev/null

real 0m0.145s
user 0m0.021s
sys 0m0.005s
$ time mysqldump --ssl-mode=required --ssl-cipher=AES128-SHA -A > /dev/null

real 0m0.120s
user 0m0.039s
sys 0m0.007s 

If you do this with multiple ciphers and put some data in the database you'll see something like this:

No TLS	4.5s
TLS Default	10.4s
RC4-MD5	7.1s
DES-CBC3-SHA	23.2s

 This is with MySQL 5.6.33 with YaSSL. Note that this is without using modern CPU features etc.

To conclude, there are some steps you can take to improve SSL/TLS performance:

1. Upgrade to 5.7
2. Compile MySQL with OpenSSL
3. Use TLS tickets
4. Use persistent connections
5. Try different cipher suits for mysqldump and other places where you transfer larger amounts of data.

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.