Managing Passwords & Plugins in MySQL 8‍

‍

MySQL 8.4 has made caching_sha2_password  as the default authentication plugin replacing the mysql_native_password. This enhancement ensures security and offers high performance through SHA-256 encryption and a server-side cache.

In this blog , we’ll explore what's new with MySQL 8 password management , understand how caching_sha2_password authentication plugin works and also learn to convert from mysql_native_password to caching_sha2_password

MySQL 8 Password Management 

‍
MySQL 8 introduces several improvements in password management, making authentication more  flexible, and compliant with modern security standards. Here’s a few

• Default Authentication Plugin - Replaced mysql_native_password with caching_sha2_password for secure authentication 
• Dual Password Support - Allows user to maintain temporarily two passwords 
• Account Locking - MySQL allows to lock users temporarily in order to avoid accessing accounts after countless attempts (Brute force attack)
• Password Strength validation - Enforces the user to meet the validate_password criteria such as length , uppercase , lowercase and special characters etc.. 
• Password history - MySQL keeps a history of users past password , preventing users from repeatedly using old passwords. 

‍

caching_sha2_password Authentication Plugin

The caching_sha2_password plugin is MySQL 8.4 default authentication method, offering stronger security and uses caching on server side for  better performance than mysql_native_password. MySQL uses caching_sha2_password to encrypt the password using SHA-256, storing these values in the authentication_string column on mysql.users table.

‍

How It Works:

• When a user logs in, MySQL checks whether the password hash is cached.
• If it is cached, authentication is fast because the server does not need to revalidate credentials.
• If it is not cached, MySQL performs a full authentication
• This approach speeds up authentication without compromising security.

Key Benefits

• More secure: caching_sha2_password plugin uses SHA-256 hashing instead of SHA-1 which is used by mysql_native_password.
• Better performance: Implements password caching to reduce authentication overhead. And efficient for subsequent client connections by faster authentication 
• Authentication methods: Plugin supports both secure and insecure connections. Secure connections are protected via secure channels by transmitting the password in clear texts whereas the insecure connections are uses RSA encryption for password exchange

‍

Managing Authentication Plugin

Though caching_sha2_password plugin is more secure than mysql_native_password , conversion of this plugin is bit critical as we need the mysql user password to convert from mysql_native_password to caching_sha2_password

‍

Scenario 1 : Creating a User with caching_sha2_password : 

To create an user that uses the caching_sha2_password plugin for SHA-256 password hashing, refer below.

Before creating the user, we can validate which user is using which password plugin from mysql.users table.

mysql> SELECT user, host, plugin FROM mysql.user;
+------------------+-----------+-----------------------+
| user             | host      | plugin                |
+------------------+-----------+-----------------------+
| pt_osc           | %         | mysql_native_password |
| root             | %         | mysql_native_password |
| pt_osc           | 10.%      | mysql_native_password |
| replication      | 10.%      | mysql_native_password |
| orchestrator     | 127.0.0.1 | mysql_native_password |
| pt_osc           | localhost | mysql_native_password |
| root             | localhost | mysql_native_password |
+------------------+-----------+-----------------------+

‍

Command :
‍

CREATE USER 'sbtest'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'sbtest123';
GRANT ALL PRIVILEGES ON *.* TO sbtest@'localhost';

‍

mysql> CREATE USER 'sbtest'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'sbtest123';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT ALL PRIVILEGES ON *.* TO sbtest@'localhost';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> SELECT user, host, plugin FROM mysql.user where user like '%sbtest%';
+--------+-----------+-----------------------+
| user   | host      | plugin                |
+--------+-----------+-----------------------+
| sbtest | localhost | caching_sha2_password |
+--------+-----------+-----------------------+

‍

Scenario 2 : Converting from mysql_native_password to caching_sha2_password 

It is advised that users switch to caching_sha2_password for increased safety. This ensures the password is hashed using SHA-256 for improved security.

‍

Command :

CREATE USER 'sbtest'@'10.%' IDENTIFIED WITH mysql_native_password BY 'sbtest123';
GRANT ALL PRIVILEGES ON *.* TO sbtest@'localhost';
ALTER USER 'sbtest'@'10.%' IDENTIFIED WITH caching_sha2_password BY 'sbtest123';

‍

mysql> SELECT user, host, plugin FROM mysql.user where user like '%sbtest%';
+--------+-----------+-----------------------+
| user   | host      | plugin                |
+--------+-----------+-----------------------+
| sbtest | 10.%      | mysql_native_password |
| sbtest | localhost | caching_sha2_password |
+--------+-----------+-----------------------+
2 rows in set (0.00 sec)
mysql> ALTER USER 'sbtest'@'10.%' IDENTIFIED WITH caching_sha2_password BY 'sbtest123';
Query OK, 0 rows affected (0.01 sec)
mysql> SELECT user, host, plugin FROM mysql.user where user like '%sbtest%';
+--------+-----------+-----------------------+
| user   | host      | plugin                |
+--------+-----------+-----------------------+
| sbtest | 10.%      | caching_sha2_password |
| sbtest | localhost | caching_sha2_password |
+--------+-----------+-----------------------+
2 rows in set (0.00 sec)

‍

Scenario 3 :Converting from caching_sha2_password to mysql_native_password

If caching_sha2_password is not supported by your application, you can revert to the previous approach. Some older programs might not support caching_sha2_password, despite the fact that it is more secure. You might have to change a user's authentication plugin in certain situations.

Command :

 ALTER USER sbtest@'localhost' IDENTIFIED WITH mysql_native_password BY 'sbtest123';

‍

mysql> SELECT user, host, plugin FROM mysql.user where user like '%sbtest%';
+--------+-----------+-----------------------+
| user   | host      | plugin                |
+--------+-----------+-----------------------+
| sbtest | 10.%      | caching_sha2_password |
| sbtest | localhost | caching_sha2_password |
+--------+-----------+-----------------------+
2 rows in set (0.00 sec)
mysql> ALTER USER sbtest@'localhost' IDENTIFIED WITH mysql_native_password BY 'sbtest123';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT user, host, plugin FROM mysql.user where user like '%sbtest%';
+--------+-----------+-----------------------+
| user   | host      | plugin                |
+--------+-----------+-----------------------+
| sbtest | 10.%      | caching_sha2_password |
| sbtest | localhost | mysql_native_password |
+--------+-----------+-----------------------+
2 rows in set (0.00 sec)

This changes the authentication method and ensures the password is hashed using mysql_native_password.

Here’s a clear table showing whether each of the listed tools supports the caching_sha2_password plugin in MySQL 8.4

‍

We have listed the compatibility of the caching_sha2_password plugin for DB Managing & monitoring tools. Please ensure to validate the compatibility of caching_sha2_password of your application tools & softwares before proceeding with the conversion.

Advantages

• It uses SHA-256 rather than SHA-1 (used for mysql_native_password), making it more resistant to both brute-force attacks 
• This plugin uses in-memory cache to store hashed passwords , making it faster for reauthentication 
• Supports RSA-based password exchange for secure authentication even when SSL is unavailable.
  

Disadvantages

• Most of old MySQL clients drivers, and libraries do not support caching_sha2_password which leads to Compatibility Issues 
• Cached password expires when MySQL restarts or users run flush privileges .
• If SSL is not enabled , then mysql falls back to RSA encryption for password exchange which is complexed
• Conversion to the caching_sha2_password plugin of a mysql user requires a password for that particular user which may lead to data breach.

‍

Summary

MySQL 8 advancements on password management and authentication plugins provide more secure and adaptable database environments. It is believed that switching from mysql_native_password to caching_sha2_password will improve both security and efficiency in password management.

Transitioning to MySQL 8.4’s caching_sha2_password plugin requires careful planning and execution. At Mydbops, we specialize in MySQL Managed Services, Consulting, Remote DBA, and MySQL Support to ensure your database is secure, optimized, and always up-to-date. Let our experts handle your MySQL environment with precision and expertise.

Contact us today for a free consultation and make your MySQL security stronger than ever!