Technology Sharing | MySQL:caching_sha2_password Quick Q&A

Author: Hu Chengqing

A member of the DBA team of Aikesheng, good at failure analysis and performance optimization, personal blog: https://www.jianshu.com/u/a95ec11f67a8 , welcome to discuss.

Source of this article: original contribution

*The original content is produced by the open source community of Aikesheng, and the original content shall not be used without authorization. For reprinting, please contact the editor and indicate the source.

---

an error

When using the client to log in to MySQL 8.0, we often encounter the following error:

ERROR 2061 (HY000): Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection.

Many posts on the Internet teach us to modify the user authentication plug-in to mysql_native_password to solve the problem, so what is going on in fact? This article will discuss one or two.

Introduction to caching_sha2_password

caching_sha2_password is a new authentication plugin introduced in MySQL 8.0.4, and its features can be seen from its name:

• sha2_password: In fact, it is sha256_password, which is an authentication plug-in introduced in MySQL 5.6. Its advantage is to perform multiple rounds of SHA256 hashing on the salted password to ensure that the hash conversion is more secure. The disadvantage is that it requires a secure connection or an unencrypted connection using an RSA key pair for password exchange, so it is less efficient for authentication.
• Caching: Cache is added on the basis of sha256_password. In the case of cache, no encrypted connection or RSA key pair is required, which has achieved the coexistence of security and efficiency.

In fact, the above introduction is not easy to understand, let's lift the veil of caching_sha2_password in a question-and-answer format.

Q: What does it mean to use a secure connection or an unencrypted connection that uses an RSA key pair for password exchange?

caching_sha2_password requires higher password security, requiring that the password transmitted over the network during user authentication is encrypted:

• If it is an SSL encrypted connection, use the SSL certificate and key pair to complete the "symmetric encryption key pair (generated in the TSL handshake)" exchange, and then use the "symmetric encryption key pair" to encrypt passwords and data. For details, see: MySQL: Analysis of SSL Connections;
• If it is a non-SSL encrypted connection, when the connection is established, the client uses the RSA public key of the MySQL server to encrypt the user password, and the server uses the RSA private key to decrypt and verify the correctness of the password, which can prevent the password from being snooped during network transmission.

Tips: SSL encrypted connections will not only encrypt user passwords, but also encrypt data (SQL requests, returned results); non-encrypted connections only use RSA key pairs to encrypt user passwords.

Q: How does an unencrypted connection use an RSA key pair for password exchange?

When the user is authenticated successfully, the user password hash is cached. When a newly connected client initiates a login request, the MySQL server will determine whether the cache is hit. If there is no cache, for unencrypted connections, the caching_sha2_password plugin requires RSA to be used for encrypted password exchange when the connection is established, otherwise an error will be reported. The process is as follows:

• If the client has the RSA public key of the server, use the --server-public-key-path option to specify the RSA public key file;
• The client uses the RSA public key to encrypt the user's password and requests a connection;
• The server uses the RSA private key for decryption to verify the correctness of the password.

If the client does not save the RSA public key file of the server, you can also use the --get-server-public-key option to request the public key from the server. When establishing a connection, the server will first send the RSA public key to the client.

If neither --server-public-key-path nor --get-server-public-key are specified, the following classic error will be reported:

 [root@172-16-21-5 ~] mysql -h172.16.21.4 -utest -ptestpass --ssl-mode=disable
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 2061 (HY000): Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection.

Specify --get-server-public-key to log in successfully:

 [root@172-16-21-5 ~] mysql -h172.16.21.4 -utest -ptestpass --ssl-mode=disable --get-server-public-key -e "select 1"
mysql: [Warning] Using a password on the command line interface can be insecure.
+---+
| 1 |
+---+
| 1 |
+---+

If the test user logs in successfully and there is a cache, the unencrypted connection will no longer require the use of an RSA key pair during the next authentication:

 [root@172-16-21-5 ~] mysql -h172.16.21.4 -utest -ptestpass --ssl-mode=disable -e "select 1"
mysql: [Warning] Using a password on the command line interface can be insecure.
+---+
| 1 |
+---+
| 1 |
+---+

Note: The above client refers to the mysql default command line client, the --server-public-key-path, --get-server-public-key parameters are only applicable to the mysql client

Where is the RSA key pair stored?

The RSA key pair is saved in MySQL datadir by default and is used for password encryption exchange in non-SSL connection: use the RSA public key to encrypt the password, and use the RSA private key to decrypt:

 private_key.pem      RSA公钥
public_key.pem       RSA私钥

Q: When does the password hash cache expire?

When the user is successfully authenticated, the password hash will be cached, and the cache will be cleared in the following cases:

• when the user's password is changed;
• When using RENAME USER to rename a user;
• When executing FLUSH PRIVILEGES;
• MySQL restarts.

Q: What should I pay attention to when using the caching_sha2_password plugin for copy users?

For MGR, if group_replication_ssl_mode=DISABLED is set, the following variables must also be used to specify the RSA public key, otherwise an error will be reported:

• group_replication_recovery_get_public_key : request the RSA public key from the server;
• group_replication_recovery_public_key_path : Specifies the local RSA public key file.

Just set one. Considering the trouble of copying the RSA public key to each node, it is recommended to set group_replication_recovery_get_public_key=ON.

For asynchronous/semi-synchronous replication, you need to specify in the change master command: MASTER_PUBLIC_KEY_PATH = 'key_file_path' or GET_MASTER_PUBLIC_KEY = {0|1}

Same meaning as above, suggestion: GET_MASTER_PUBLIC_KEY = 1

References

https://dev.mysql.com/blog-archive/mysql-8-0-4-new-default-authentication-plugin-caching_sha2_password/

https://dev.mysql.com/doc/refman/8.0/en/caching-sha2-pluggable-authentication.html