The evolution of MySQL authentication mechanism

The authentication, the first level of security for each IT system, is the stage to verify the user identity through the basic username and password scheme. It is crucial to have a mechanism to protect and secure password storing and transmitting over network.

In MySQL, there is plenty of different authentication methods available, and last versions improved the security of this concept.

At the beginning, the mechanism, called mysql_old_password, was pretty insecure: it’s based on a broken hashing function and the password is 16 bytes long. It was not so complex for attackers to find a plaintext password from the hash stored in the password column of mysql.user table. It has been removed in MySQL 5.7.5.

A new method was introduced in MySQL 4.1 and it became the mysql_native_password plugin as of MySQL 5.5, enabled by default. It’s based on a SHA-1 hashing algorithm and the password is 41 bytes long. On one side, it’s more secure than mysql_old_password because hashes cannot be used to authenticate. But on the other side, it still has weaknesses, especially for “too simple” passwords, because for the same passwords we get the same hash. Again, it’s not so complicated to search for the stolen hashes in rainbow tables to obtain the correspondent plaintext password.

There have been other improvements in MySQL 5.6: sha256_password plugin adds a random salt in hashes generation, so this last one is unique and rainbow tables are useless. In MySQL 8.0, default authentication remains strong (same SHA-256 password hashing mechanism) but in addition it becomes faster: a cache was added on the server side to enable a faster re-authentication for accounts that have connected previously. It’s called caching_sha2_password and it’s now the default plugin.

This MySQL authentication evolution must be seriously considered by DBAs during upgrade processes and following aspects must not to be underestimated:

• Read MySQL official documentation to prepare your upgrade procedure
• Don’t forget to run mysql_upgrade or to restart the MySQL server with upgrade=FORCE depending on your MySQL version (after importing your dump): it not only examines user tables to find out possible incompatibilities, but upgrades also system tables in mysql, performance_schema and sys databases
• Try to keep your MySQL server up to date in order to avoid surprises or difficult procedures to put in place due to a too large delta (as an example, you can find here the procedure to migrate away from pre-4.1 password hashing method – good luck 😉 )
• As of MySQL 5.7.6, the password column was removed and values are now stored in the authentication_string column of mysql.user table
• As of MySQL 5.5, the plugin column has been added to mysql.user table and as of MySQL 5.7 the server doesn’t enable any accounts with an empty plugin value
• As of MySQL 8.0, you have the choice to define one of the 3 following values for the default_authentication_plugin system variable: mysql_native_password, sha256_password and caching_sha2_password. But then you have also the possibility to set a different authentication plugin for a specific account using the following syntax: CREATE | ALTER USER … IDENTIFIED WITH <auth_plugin> […];

I would like to share with you three last considerations before closing this short blog post:

• Once again, keep your MySQL server up to date
• MySQL is getting better and better in terms of security
• “Security is always excessive until it’s not enough.” (Robbie Sinclair, Head of Security, Country Energy, NSW Australia) 😉