Password Hashing in MySQL 8.0: Authentication Plugins

All IT systems prioritize security, and authentication is considered the first security level to confirm the user’s identification. Generally, authentication is based on usernames and passwords, so to secure user accounts, it is essential to have a system in place to secure the transmission and storage of passwords over networks. Pluggable authentication makes it easy for DBAs to choose and change the authentication method for individual accounts. MySQL 8 provides several authentication plugins that use different mechanisms for password hashing that can be used to protect user passwords stored in the database.

In this article, we will explore the various password hashing mechanisms provided by MySQL 8 and how they can be used to secure user passwords.

Related Articles:

This article will cover:

1. What is Password Hashing?
2. The evolution of MySQL authentication mechanism
3. MySQL 8.0 Authentication Plugins
3.1. MySQL mysql_native_password Plugin:
3.2. MySQL sha256_password Plugin
3.3. MySQL caching_sha2_password Plugin
3.4. Why MySQL caching_sha2_password Plugin
Conclusion:
You might also like:

1. What is Password Hashing?

The process of converting a password into an encoded value that cannot be decoded and reversed back into the original password is known as hashing. Password hashing is a key technique used to protect passwords in databases and prevent their exposure. When a user inputs his password, the system hashes it and compares it with the previously stored hash value. Hashed passwords are usually kept in the database, and the user is verified if the hash values match.

2. The evolution of MySQL authentication mechanism

The first mechanism, known as mysql_old_password, has been removed in MySQL 5.7.5. It was initially quite unsafe because the password was only 16 bytes long and it relied on a flawed hashing function. Finding a plaintext password from the hash value stored in the password column of the mysql.user database was not a difficult task for attackers. With MySQL 5.7.5, it is gone.

In MySQL 4.1, a new mechanism was introduced, and as of MySQL 5.5, it was activated by default as the mysql_native_password plugin. In this mechanism, the password is 41 bytes long and based on the SHA-1 hashing method. The fact that passwords cannot be used for authentication makes it safer than mysql_old_password, on the one hand. On the other hand, because we obtain the same hash for identical passwords, it still needs fixing, particularly for too-basic passwords. Once more, finding the stolen hashes in rainbow tables and getting the corresponding plaintext password is not difficult.

Other enhancements were introduced in MySQL 5.6, including using the sha256_password plugin that adds a random salt to the hash generation process, making this last hash unique and Rainbow tables pointless.

While default authentication in MySQL 8.0 is still strong using the same sha256_password hashing mechanism, it becomes faster thanks to a server-side cache that allows a quicker re-authentication for previously connected accounts. This plugin is known as caching_sha2_password, and it is currently the default authentication plugin in MySQL 8.0

3. MySQL 8.0 Authentication Plugins

As we said in the previous section, the caching_sha2_password is currently the default authentication plugin in MySQL 8.0, and it is recommended if you are starting a new MySQL project. But you can still choose from mysql_native_password, sha256_password or caching_sha2_password options for the default_authentication_plugin system variable for compatibility reasons and if your application require it. You can also set a different authentication plugin for each user’s account. See the following sections for more detail.

3.1. MySQL mysql_native_password Plugin:

mysql_native_password plugin was enabled by default in MySQL 5.5. The mysql_native_password plugin implements authentication based on the native password hashing method that combines SHA-1 and a random salt value. The salt value is added to the password before hashing, making it harder to crack. To use native password hashing, you can use the following SQL statement:

 CREATE USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '<password>';

 ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '<password>';

Then you also can set a different authentication plugin for a specific account using the following statement:

 CREATE USER 'user'@'localhost' IDENTIFIED WITH mysql_native_password BY '<password>';

 ALTER USER 'user'@'localhost' IDENTIFIED WITH mysql_native_password BY '<password>';

The password parameter should contain the user’s password in plain text.

To validate all database users, including root, using mysql_native_password authentication and start the server with the default authentication plugin set to mysql_native_password, set up default_authentication_plugin system variable to mysql_native_password in the server options file:

 default-authentication-plugin=mysql_native_password

3.2. MySQL sha256_password Plugin

Additionally, MySQL 8 offers a sha256_password hashing plugin, an improved version of the mysql_native_password hashing mechanism. sha256_password hashing uses SHA-256 instead of SHA-1 and adds a random salt value to the password before hashing.

Both server and client forms support the sha256_password plugin. The server-side plugin is pre-installed, does not require specific launching, and cannot be disabled by unloading. Any application linked against the libmysqlclient client module will have access to the client-side plugin.

You must use either a TLS connection or an unencrypted connection that supports password exchange using an RSA key pair in order to connect to the server using an account that authenticates using the sha256_password plugin. The sha256_password plugin makes use of MySQL’s encryption features in either case.

To use sha256_password hashing, you can use the following SQL statement:

 CREATE USER 'user'@'localhost' IDENTIFIED WITH sha256_password BY 'password';

The password parameter should contain the user’s password in plain text.

To set up an account that uses the sha256_password plugin for SHA-256 password hashing, use the following statement:

The server encrypts the password using the sha256_password plugin and is stored in the plugin and authentication_string columns of the mysql.user system table using SHA-256.

The following statement does not assume that sha256_password is the default authentication plugin. An easier CREATE USER syntax can be used, If sha256_password is the default authentication plugin.

 
 CREATE USER 'sha256user'@'localhost'
 IDENTIFIED WITH sha256_password BY 'password';

The password parameter should contain the user’s password in plain text.

To start the server with the default authentication plugin set to sha256_password, you must add these lines in the server options file:

 default_authentication_plugin=sha256_password

This results in all new accounts will use the sha256_password plugin by default, and you can create an account and set its password without naming the plugin explicitly:

CREATE USER 'sha256user'@'localhost' IDENTIFIED BY 'password';

After setting default_authentication_plugin to sha256_password, to use some other plugin for account creation, you must specify that plugin explicitly.

  
CREATE USER 'nativeuser'@'localhost'
 IDENTIFIED WITH mysql_native_password BY 'password';

Note: The sha256_password authentication plugin is deprecated and will likely be deleted in a later release of MySQL because caching_sha2_password is the default authentication plugin in MySQL 8.0 and offers a superset of its features. It is advisable to switch MySQL users that use sha256_password for authentication to caching_sha2_password.

3.3. MySQL caching_sha2_password Plugin

Starting with MySQL 8.0.4, the default authentication plugin for MySQL server is caching_sha2_password instead of mysql_native_password. As a result, libmysqlclient will now also use caching_sha2_password as the default authentication mechanism. Similar to sha256_password, caching_sha2_password implements SHA-256 authentication but utilizes caching on the server side for better performance and has additional features for broader applicability.

3.4. Why MySQL caching_sha2_password Plugin

Because it supports rapid challenge-response technology, which does not require an encrypted connection, mysql_native_password has the advantage of being more secure. However, mysql_native_password relies on the SHA1 algorithm, and NIST has advised against using the SHA1 algorithm.

Furthermore, the mysql_native_password transformation in the mysql.user table is identical if two user accounts have the same password. While the hash hides information about the password itself, it reveals which people share a password. The usage of salt should prevent that. When user passwords are transformed using cryptographic hash functions, one of the inputs is salt, which is essentially a random number. Even if two users use identical passwords, the final transformed state differs between them since salt is random and unique for each operation.

The sha256_password authentication plugin has been supported since MySQL 5.6. In order to ensure that the hash transformation is more secure, it performs several rounds of SHA256 hashing on a salted password. However, it needs either support for an RSA key pair or encrypted communications. As a result, even while password security is improved, secure connections and many rounds of hashing make the authentication process take longer.

It is important to know that:

For the server, the default value of the default_authentication_plugin system variable changes from mysql_native_password to caching_sha2_password.
The libmysqlclient library treats caching_sha2_password as the default authentication plugin rather than mysql_native_password.
And this change applies only to new accounts created after installing or upgrading to MySQL 8.0 or higher.
For accounts already existing in an upgraded installation, their authentication plugin remains unchanged.
Existing users who want to switch to caching_sha2_password can do so using the ALTER USER statement:

 ALTER USER user
  IDENTIFIED WITH caching_sha2_password
  BY 'password';

Conclusion:

User passwords saved in databases can be secured using the password hashing security method. The password hashing mechanisms offered by MySQL 8 include native MySQL password hashing, SHA-256 password hashing, SHA-2 password hashing, and MySQL SHA-256 password hashing. These safeguards can be used to secure user credentials stored in the database and protect them from being compromised. By utilising these mechanisms, you can ensure that your MySQL 8 database is secure and that user passwords are shielded from unauthorized access.