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.

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.

MySQL NL Meetup March 18

The next MySQL User Group NL meetup is this Tuesday at the Oracle office in Utrecht.

For more info go to the Event page @ Meetup.com

MySQL Certification

According to the Oracle website the following certifications are available for MySQL:

• Oracle Certified Associate, MySQL 5.0/5.1/5.5
• Oracle Certified Professional, MySQL 5.0 Developer
• Oracle Certified Professional, MySQL 5.0 Database Administrator
• Oracle Certified Expert, MySQL 5.1 Cluster Database Administrator

There were two new versions released since MySQL 5.0. MySQL 5.0 was released in 2005, that's more than 5 years ago. And "MySQL 5.1 Cluster Database"? According to wikipedia the NDB release in the 5.1 source tree is old and not maintained. And Cluster 7.1 has many new features like multithreaded data nodes and disk based data.

So Oracle, please update the certifications.