Dewu Technology talks about MySQL 8.0: new authentication plug-in (caching_sha2_password)

Starting from MySQL 8.0.4, the default authentication plugin has been changed from mysql_native_password to caching_sha2_password. Correspondingly, the current libmysqlclient will use caching_sha2_password as the default authentication mechanism.

do this?

The default password plugin for MySQL 5.6/5.7 has always been mysql_native_password. The advantage is that it supports the challenge-response mechanism, which is a very fast authentication mechanism, without the need to send the actual password in the network, and does not require an encrypted connection. However, mysql_native_password relies on the SHA1 algorithm, but NIST (National Institute of Standards and Technology) has recommended to stop using the SHA1 algorithm because SHA1 and other hash algorithms (such as MD5) have proven to be very easy to crack.

In addition, because mysql_native_password in the mysql.user table in the authentication_string field stores the value calculated by the two hash SHA1 (SHA1(password)), that is to say, if the two user accounts use the same password, then the mysql_native_password is converted in mysql The hash value obtained from the .user table is the same. Although there is a hash value and the actual password information cannot be obtained, it still tells the two users that the same password is used. In order to avoid this situation, the password should be salted. Salt is basically a cryptographic hash function that is used as input to transform the user's password. Since salt is random, even if two users use the same password, the final result after conversion will change significantly.

Starting from MySQL 5.6, the sha256_password authentication plug-in is supported. It uses a salted password for multiple rounds of SHA256 hashing (thousands of rounds of hashing, brute-force cracking is more difficult) to ensure that the hash value conversion is more secure. However, it requires either the secure connection or the password to be encrypted with an RSA key. Therefore, although the security of the password is stronger, the secure connection and multiple rounds of hash conversion require longer time in the authentication process.

In order to overcome these limitations, starting from MySQL 8.0.3, a new authentication plug-in caching_sha2_password has been introduced. Starting from MySQL 8.0.4, this plugin has become the new default authentication plugin for the MySQL server. caching_sha2_password Try a combination of the best of both worlds, solving both security and performance issues.

First of all, the handling of user passwords by caching_sha2_password is actually the mechanism of sha256_password:

Use the SHA2 algorithm to convert the password. Specifically, it uses the SHA256 algorithm.
The hash value stored in the authentication_string column is the salted value. Since the salt is a 20-byte random number, even if two users use the same password, the final result after conversion will be completely different.
In order to make it more difficult to guess passwords using brute force cracking mechanisms, 5000 rounds of SHA2 hashing were performed on the password and salt before storing the final conversion in the mysql.user table.

In order to implement the salting mechanism, the column authentication_string needs to save the salt value, so the length of the authentication_string value becomes 70 bytes:

mysql> select user, host, authentication_string, length(authentication_string), plugin from mysql.user limit 1;
+------+------+------------------------------------------------------------------------+-------------------------------+-----------------------+
| user | host | authentication_string                                                  | length(authentication_string) | plugin                |
+------+------+------------------------------------------------------------------------+-------------------------------+-----------------------+
| root | %    | $A$005$1%h5f1OdZ0'46}M[uz5Di5wW2WWg8eeLWynsg2h3xnzHwQLmm39bEqLBxB0   |                            70 | caching_sha2_password |
+------+------+------------------------------------------------------------------------+-------------------------------+-----------------------+
1 row in set (0.00 sec)

Under the new caching_sha2_password authentication mechanism, the bytes in authentication_string, such as the above string $A$005$1 %h5f1O dZ0' 46}M[uz5Di5wW2WWg8eeLWynsg2h3xnzHwQLmm39bEqLBxB0, respectively store the following contents:

content	number of bytes	description
Hash algorithm	2 bytes	Currently only $A, which means SHA256 algorithm
Number of hash rotations	4 bytes	Currently only $005, which means 5*1000=5000 times
Salt	21 bytes	Used to solve the problem of the same password and the same hash value
Hash value	43 bytes

Starting from MySQL 8.0.24, the caching_sha2_password_digest_rounds system variable is provided. The default and minimum value is 5000, and the maximum value is 4095000; the number of hash rotations used for the password storage of the caching_sha2_password authentication plug-in.

Secondly, caching_sha2_password solves performance problems through caching on the server side. The caching_sha2_password plug-in uses a memory cache to quickly authenticate clients that have previously connected. The memory cache entry consists of a username/SHA256 (SHA256(user_password)) pair. The working principle of the cache is this:

When the client connects, caching_sha2_password checks whether username/SHA256(SHA256(user_password)) matches the cache entry. If it matches, the verification is successful.
If there is no matching cache entry, the plug-in will continue to exchange data packets with the client and try to authenticate the client with the credentials in the mysql.user system table. If successful, caching_sha2_password adds a hash entry to the client. Otherwise, the authentication fails and the connection is refused.

In this way, when the client connects for the first time, it uses the credentials of the mysql.user system table for authentication. When the client connects, the cache is used for fast authentication.

For most connection attempts, when a copy of the password hash exists in the memory cache, it uses a challenge-response mechanism based on SHA256 to authenticate the client (mysql_native_password is a challenge-response mechanism based on SHA1). This will be faster and allow secure authentication through unencrypted channels.

The following summarizes the challenge-response-based authentication mode (also called Fast authentication mode):

Client connects to server
The server sends Nonce (20 bytes of random data) to the client
The client uses XOR(SHA256(password), SHA256(SHA256(SHA256(password)), nonce)) to generate a Scramble and send it to the server
The server checks whether the username/SHA256(SHA256(user_password)) exists in the memory cache entry, if it exists, it proves legal; send the fast_auth_success package to the client
The server sends an OK packet to the client
Enter the command phase

Note

In information security, Nonce is a number that can only be used once in encrypted communication. In the authentication protocol, it is often a random or pseudo-random number (salt) to avoid brute force attacks.

Because the caching_sha2_password plug-in can quickly authenticate when the cache is used, but it is invalid in the following cases, for some or all users:

When the user's password is changed, the user's cached password hash value is deleted from the memory. The password can be changed through ALTER USER/SET PASSWORD/GRANT.
When the user is deleted, or the certificate or authentication plug-in is changed; the password hash value cached by the user is deleted from the memory.
When the user uses RENAME USER to rename, the password hash value cached by the user is deleted from the memory.
When FLUSH PRIVILEGES is executed, all cached password hash values are deleted from memory, affecting all users. \
The cache will be cleared when the server shuts down.

In the case of cache invalidation, subsequent client connection verification requirements will be affected. caching_sha2_password requires that the user's first client connection must use a secure connection (TCP connection uses TLS, Unix socket file, or shared memory) or RSA encrypted password for exchange.

Considering that the user's password changes and FLUSH PRIVILEGES are not frequently performed operations, in most cases, challenge-response authentication is sufficient. The following summarizes the mechanism based on the complete authentication mode (perform_full_authentication) (also known as the Complete authentication mode).

Client connects to server
The server sends Nonce (20 bytes of random data) to the client
The client uses XOR(SHA256(password), SHA256(SHA256(SHA256(password)), nonce)) to generate a Scramble and send it to the server
The server checks whether the username/SHA256(SHA256(user_password)) exists in the memory cache entry, and if it does not exist, it sends the perform_full_authentication package to the client to continue authentication
The client receives the perform_full_authentication package and can proceed as follows
If the connection has established an SSL-based secure channel, you can directly send the plaintext password to the server\
Initiate a request to the server to obtain the public key (or specify the server public key file), use the public key + Nonce to encrypt the password, and send the encrypted password to the server\
The server calculates the hash value through the SHA256 algorithm, determines whether the user is authenticated, and then sends an OK packet to the client\
Enter the command phase

In summary, the working mechanism of the caching_sha2_password plug-in is divided into two stages, Complete authentication and Fast authentication. The respective authentication mechanisms have been explained above. For a more detailed process, as well as the state transition between the server and the client, the official source code document provides a picture and description for reference. The address is at the end of the article.

What has

All new users created after MySQL 8.0.4 will use caching_sha2_password as their authentication plugin by default.

mysql> CREATE USER 'sha2user'@'localhost' IDENTIFIED BY '42';
Query OK, 0 rows affected (0.02 sec)

mysql> SHOW CREATE USER 'arthurdent'@'localhost'\G
CREATE USER for sha2user@localhost: CREATE USER 'sha2user'@'localhost' IDENTIFIED WITH 'caching_sha2_password' AS '$Afnka//BGe\d3h\n<:MTEFNZ3U40FRyPrdT5V14x526MHPENmY5Tn0RbjwA16' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT
1 row in set (0.01 sec)

libmysqlclient uses caching_sha2_password as the default choice to connect to the MySQL server. Please note that this is only a change in the default value, libmysqlclient is able to support all existing authentication plugins. MySQL's server-client protocol will be responsible for the authentication plug-ins needed to switch each user's account.

If you don't want to use the default caching_sha2_password plugin, you can also use some other plugins to create an account, you must specify the plugin explicitly. For example, using the mysql_native_password plugin, use this statement:

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

caching_sha2_password supports secure connection transmission. If you follow the RSA configuration process given below, it also supports the use of RSA encrypted passwords for exchange on unencrypted connections. RSA supports the following features:

On the server side, two system variables name the files of the RSA private key and public key pair: caching_sha2_password_private_key_path and caching_sha2_password_public_key_path. If you want to change its default value, you must set the variable when the server starts.
The server uses the auto_generate_certs, sha256_password_auto_generate_rsa_keys, and caching_sha2_password_auto_generate_rsa_keys system variables to determine whether to automatically generate an RSA key pair file. These variables are enabled by default. They can be enabled and checked when the server starts, but not set at runtime. See "Creating SSL and RSA Certificates and Keys" for details
The status variable Caching_sha2_password_rsa_public_key displays the RSA public key value used by the caching_sha2_password authentication plug-in.
When the client holds the RSA public key, it can perform password exchange with the server RSA key pair during the connection process, as described later.
For connections to accounts that use caching_sha2_password and RSA keys for authentication, the server will not send the RSA public key to the client by default. The client can use a copy of the required public key, or initiate a request for the public key from the server. It should be noted that using a copy of the trusted public key locally enables the client to avoid round trips in the client/server protocol, which is more secure than the public key requested from the server. On the other hand, it is more convenient to request the public key from the server (it does not need to manage files on the client side), which is acceptable in a secure network environment.

For clients using the caching_sha2_password plugin, the password will not be exposed as clear text when connecting to the server. How the password transmission is performed depends on whether the password is encrypted using a secure connection or RSA:

If the connection is secure, the RSA key pair is unnecessary. This applies to TCP connections that use TLS encryption, as well as Unix socket file and shared memory connections. The password is sent in clear text format, but it cannot be eavesdropped because the connection is secure.
If the connection is not secure, an RSA key pair is used. This applies to TCP connections and named-pipe connections that are not encrypted using TLS. RSA is only used for password exchange between client and server to prevent passwords from being intercepted. When the server receives the password encrypted with the public key, it uses the private key to decrypt it. A random string is used in encryption to prevent repeat attacks.

To enable the client to use the RSA key pair for password exchange during the connection process, please use the following steps (MySQL 8.0.3 and above are automatically completed by default):

Create RSA private key and public key pair file
If the private key and public key files are located in the data directory, named private_key.pem and public_key.pem (the default values of the caching_sha2_password_private_key_path and caching_sha2_password_public_key_path system variables), the server automatically uses them when it starts. Otherwise, you need to specify the location of the private key and public key file in the configuration file.
After restarting the server, check the value of the Caching_sha2_password_rsa_public_key status variable. The value will be different from what is shown here, but should be non-empty:

mysql> SHOW STATUS LIKE 'Caching_sha2_password_rsa_public_key'\G
Variable_name: Caching_sha2_password_rsa_public_key
Value: -----BEGIN PUBLIC KEY-----
MIGfMA0GCSqGSIb3DQEBAQUAA4GNADCBiQKBgQDO9nRUDd+KvSZgY7cNBZMNpwX6
MvE1PbJFXO7u18nJ9lwc99Du/E7lw6CVXw7VKrXPeHbVQUzGyUNkf45Nz/ckaaJa
aLgJOBCIDmNVnyU54OT/1lcs2xiyfaDMe8fCJ64ZwTnKbY2gkt1IMjUAB5Ogd5kJ
g8aV7EtKwyhHb0c30QIDAQAB
-----END PUBLIC KEY-----

If the value is empty, check the diagnostic information in the error log.

After the server has configured the RSA key file, the user is using the caching_sha2_password plug-in for authentication, and these key files are connected to the server's options. As mentioned earlier, at this time, the user can use a secure connection (in this case, RSA is not used) or use RSA to perform a password exchange under an unencrypted connection. Assuming that an unencrypted connection is used, for example:

shell> mysql --ssl-mode=DISABLED -u sha2user -p
Enter password: password

Try to connect through the sha2user user, the server confirms that the caching_sha2_password authentication plug-in is suitable for sha2user and calls it. The plug-in finds that the connection is not encrypted, so it needs to use RSA to encrypt the sent password. However, the server will not send the public key to the client. Since the client does not provide the public key, it cannot encrypt the password and the connection fails:

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

To request the RSA public key from the server, specify the --get-server-public-key option:

shell> mysql --ssl-mode=DISABLED -u sha2user -p --get-server-public-key
Enter password: password

In this case, the server sends the RSA public key to the client, and the client uses it to encrypt the password and returns the result to the server. The plug-in uses the RSA private key on the server side to decrypt the password and decides to accept or reject the connection based on whether the password is correct.

In addition, if the client has the RSA public key file required by the server, you can use the --server-public-key-path option to specify the file:

shell> mysql --ssl-mode=DISABLED -u sha2user -p --server-public-key-path=file_name
Enter password: password

In this case, the client uses the public key to encrypt the password and returns the result to the server. The plug-in uses the RSA private key on the server side to decrypt the password and decides to accept or reject the connection based on whether the password is correct.

The public key value in the file specified by the --server-public-key-path option should be the same as the key value of the server-side file named by the caching_sha2_password_public_key_path system variable. If the key file contains a valid public key value, but the value is incorrect, an access denied error will occur. But if the key file does not contain a valid public key value, the client program cannot use it (this is because the client has checked the correctness of the public key).

Client users can obtain RSA public keys in two ways:

The database administrator can provide a copy of the public key file.
Client users who can connect to the server can use the SHOW STATUS LIKE "Caching_sha2_password_rsa_public_key" statement to return the public key value and save it in a file.

affect replication?

The replication itself is an encrypted connection. In MySQL 8.0.4, replication also supports RSA key pair.

CHANGE MASTER now supports two parameters to enable the exchange of passwords based on the caching_sha2_password RSA key
- MASTER_PUBLIC_KEY_PATH = "key_file_path", specify the RSA public key path
- GET_MASTER_PUBLIC_KEY = {0 | 1}, get the RSA public key from the server
Group Replication now supports two parameters to enable the exchange of passwords based on the caching_sha2_password RSA key
- ––Group-replication-recovery-public-key-path, specify the RSA public key path
- ––Group-replication-recovery-get-public-key, get the RSA public key from the server

Note

If the replication channel is secure, then naturally there is no need to use the RSA public key to exchange passwords.

https://mp.weixin.qq.com/s/HetYIbLI8VvzyewXuoBLoA

https://mp.weixin.qq.com/s/jfoH6D8NfoaNN7eexbzKGA

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

https://dev.mysql.com/doc/dev/mysql-server/latest/page_caching_sha2_authentication_exchanges.html

https://mysqlserverteam.com/mysql-8-0-4-new-default-authentication-plugin-caching_sha2_password

Text / Dongqing

Pay attention to the material technology, be the most fashionable technical person!