Starting with MySQL 8.0.4, we are changing the default authentication plugin for MySQL server from mysql_native_password to caching_sha2_password. Correspondingly, libmysqlclient will now use caching_sha2_password as the default authentication mechanism, too.

Why did we do it?

The advantage of mysql_native_password is that it support challenge-response mechanism which is very quick and does not require encrypted connection. However, mysql_native_password relies on SHA1 algorithm and NIST has suggested to stop using it.

Further, if two user accounts use the same password, mysql_native_password transformation is the same in the mysql.user table. Although the hash does not expose information about the actual password, it still tells which two users use the same password. To avoid that, salt should be used. Salt is basically a random number that is used as one of the inputs to cryptographic hash functions used to transform user passwords. Since salt is random and different for each execution, even if two users use the same passwords, the end result of transformation would look very different.

Since MySQL 5.6, sha256_password authentication plugin is supported. It uses multiple rounds of SHA256 hash on a salted password to make sure that the hash transformation is more secure. However, it requires either encrypted connections or support for an RSA key pair. So, while password security is stronger, secure connections and multiple rounds of hash transformations require more time in the authentication process.

caching_sha2_password tries to combine the best of both worlds.

• For a majority of connection attempts, when there exists a cached copy of the password hash in memory, it uses a SHA256-based challenge-response mechanism while authenticating a client (compared to a SHA1-based challenge-response mechanism in mysql_native_password). This is faster and allows secure authentication over an unencrypted channel. The following figure summarizes challenge-response based authentication.

• It employs a technique similar to sha256_password before storing credential information in the mysql.user table. It uses 5000 rounds of SHA256 transformation on a salted password.

The cache used by the caching_sha2_password plugin for faster authentication is invalidated for some or all users in following cases:

• When users’ passwords are changed, all such users’ cached password hashes are removed. Passwords can be changed through through ALTER USER/SET PASSWORD/GRANT.
• When users’ are renamed using RENAME USER, cached entries of their password hashes are removed from memory.
• When FLUSH PRIVILEGES is executed, all cached password hashes are removed.

In such cases of cache invalidation, caching_sha2_password requires password exchange using a secure connection. Considering that user credential changes and FLUSH PRIVILEGES are not frequently executed operations (they are performance-heavy activities anyways), in most cases, challenge-response based authentication is sufficient. This amortizes cost paid to perform authentication which uses a secure connection. The following figure summarizes full authentication.

What does this change mean?

The change in default authentication plugin means that:

• All new users created in MySQL 8.0.4 will use the caching_sha2_password as their authentication plugin.

• libmysqlclient uses caching_sha2_password as the default choice while connecting to MySQL server. Note that this is just a change in default value. libmysqlclient continues to work for all existing authentication plugins. The MySQL server-client protocol takes care of switching to the required authentication plugin for each user account.

So, what’s the rule of thumb when using such user accounts to connect to MySQL server?

Make sure that it is possible to establish a secure connection with the server. If the server has cached copy of the user password hash, it is possible to use any type of connection for users that authenticate with caching_sha2_password. However, caching_sha2_password periodically requires secure connections to transfer passwords to the server to recreate missing cached entries. See Cache Operation for SHA-2 Pluggable Authentication for more details. In such cases, the client should have one of the following:

• TCP connection with TLS support
• Unix socket or Windows shared memory connection
• If the server is configured to use RSA key pair for the plugin, then
  • access to server’s public key OR
  • Preference to automatically get public key from the server

Note that, client tools which are part of MySQL 8.0.4 packages and use libmysqlclient library now support RSA key pair for authentication as mentioned above.

Can I use newly created user as slave user or for recovery in case of replication?

Yes! Replication already supports encrypted connections and such connections are sufficient for caching_sha2_password users. With MySQL 8.0.4, we have made two sets of changes to make sure that replication also supports RSA key pairs.

• CHANGE MASTER now supports two additional clauses to enable RSA key based password exchange for caching_sha2_password
  • MASTER_PUBLIC_KEY_PATH = ‘key_file_path’
  • GET_MASTER_PUBLIC_KEY = {0|1}
• Group replication now supports two additional recovery options to enable RSA key based password exchange for caching_sha2_password
  • New option ––group-replication-recovery-public-key-path to accept path to the donor’s RSA public key
  • New option ––group-replication-recovery-get-public-key to obtain the RSA public key from the donor

What if I upgraded my database to MySQL 8.0.4?

User accounts already created in existing instances are not altered as a part of an upgrade. So, all existing user accounts continue to use the same authentication plugin that they used in e.g. MySQL 5.7. However, if you create a new user without changing ––default-authentication-plugin explicitly, caching_sha2_password is used as authentication mechanism for such users.

Support for caching_sha2_password was added in MySQL 8.0.3. Older versions of libmysqlclient do not support this plugin. So, although client tools that use libmysqlclient older than one available with MySQL 8.0.3 can connect to MySQL 8.0.4 server using users that use other authentication plugins such as mysql_native_password or sha256_password, such client cannot connect to MySQL 8.0.4 server using users which require caching_sha2_password support. For an upgraded database, it means connecting using an existing user account should not face any issues.

Further, it is always possible to change ––default-authentication-plugin to a non-default value of your choice (At present, options other than caching_sha2_password are: mysql_native_password and sha256_password). For example, if ––default-authentication-plugin is set to mysql_native_password, the behavior of the MySQL 8.0.4 server is similar to MySQL 8.0.3 and prior. Correspondingly, libmysqlclient supports the MYSQL_DEFAULT_AUTH options for mysql_options() C API function. (For libmysqlclient based client tools available in MySQL packages, the ––default-auth command-line options achieves the same purpose.) The client-side option is a performance tool and saves an extra round-trip when used in tandem with ––default-authentication-plugin option on server side.

However, we recommend use of caching_sha2_password because of the enhanced security that it offers. We also recommend updating libmysqlclient to MySQL 8.0.4 or later so that new default authentication plugin is supported by clients that use it.

To summarize…

We constantly try to improve the security of MySQL server. The change in default authentication plugin is a part of this never-ending process. We would love to hear your feedback on the caching_sha2_password authentication plugin.

As always, a big THANK YOU for using MySQL!