Starting with MySQL 8.0.4, the default authentication plug-in was changed from mysql_native_password to caching_sha2_password. Accordingly, libmysqlClient now uses caching_sha2_password as the default authentication mechanism.

Why do you do that?

The default password plugin for MySQL 5.6/5.7 has always been mysql_native_password. The advantage is that it supports challenge-response, which is a very fast authentication mechanism that does not require the actual password to be sent across the network, and does not require an encrypted connection. However, mysql_native_password relies on SHA1, but NIST has recommended disusing SHA1 because SHA1 and other hashing algorithms (such as MD5) have proven to be very easy to crack.

In addition, since the authentication_string field in mysql_native_password in mysql.user stores the value calculated by hashed SHA1(SHA1(password)) twice, That is, if two user accounts use the same password, the mysql_native_password will be converted into the same hash value in the mysql.user table. It still tells both users that they are using the same password, even though the hash value does not get the actual password information. To avoid this, salt should be added to the password, which is basically an encrypted hash function used as input to convert the user’s password. Because salt is random, even if two users use the same password, the end result of the conversion will be quite different.

The sha256_password authentication plugin is supported from MySQL 5.6. It uses a salted Password to run multiple rounds of SHA256 hash (thousands of rounds of hash, harder to brute-crack) to make hash conversion more secure. However, it requires either encryption over a secure connection or password using an RSA secret key pair. So, while passwords are more secure, secure connections and multiple hash conversions take longer in the authentication process.

To overcome these limitations, a new authentication plug-in called Caching_sha2_password has been introduced starting with MySQL 8.0.3. Starting with MySQL 8.0.4, this plug-in is the new default authentication plug-in for MySQL server. Caching_sha2_password tries to have the best of both worlds, addressing both security and performance issues.

First, caching_sha2_password handles the user’s password, which is essentially sha256_password:

  • Use SHA2 algorithm to convert passwords. Specifically, it uses the SHA256 algorithm.
  • The hash value stored in the authentication_string column is the value after salt is added. Since salt is a random 20-byte number, even if two users use the same password, the final result after conversion will be completely different.
  • To make it harder to guess the password using a brute force mechanism, 5000 rounds of SHA2 hashing of the password and salt were performed before the final transformation was stored in the mysql.user table.

To implement the salting mechanism, column authentication_string needs to hold the salt value, so the length of the authentication_string value is changed to 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)Copy the code

In the new Caching_sha2_PASSWORD authentication mechanism, the bytes in authentication_string, For example above string AAA005 46} $1% h5f1OdZ0 ‘M [uz5Di5wW2WWg8eeLWynsg2h3xnzHwQLmm39bEqLBxB0, which respectively save the following contents:

content The number of bytes instructions
The hash algorithm 2 – Currently, it is only $A, representing SHA256 algorithm
Number of hash turns 4 bytes The current value is only $005, which means 5*1000=5000 times
Salt (salt) 21 bytes Used to solve the problem of the same password with the same hash value
Hash value 43 byte

As of MySQL 8.0.24, the caching_sha2_PASSword_Digest_rounds system variable is provided. The default and minimum values are 5000 and the maximum is 4095000. Number of hash cycles used to store the password of the caching_sha2_password authentication plug-in.

Second, caching_sha2_PASSWORD addresses performance issues through caching on the server side. The caching_sha2_password plug-in uses memory caching for quick authentication of previously connected clients. Memory cache entries consist of username/SHA256(SHA256(user_password)) pairs. Here’s how caching works:

  1. When the client connects, caching_sha2_password checks whether username/SHA256(SHA256(user_password)) matches the cache entry. If yes, the authentication succeeds.
  2. If there is no matching cache entry, the plug-in continues to exchange packets with the client, trying to authenticate the client using the credentials of 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 rejected.

This way, when the client connects for the first time, the credentials of the mysql.user system table are used 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 sha256-based challenge-response mechanism to authenticate clients (mysql_native_password is sha1-based challenge-response mechanism). This is faster and allows for secure authentication over unencrypted channels.

The following is a summary of challenge-response based authentication mode (also known as Fast authentication mode) :

  1. The client connects to the server

  2. The server sends the Nonce (20 bytes of random data) to the client

  3. The client uses XOR(SHA256(password), SHA256(SHA256(password)), nonce)) to generate Scramble and send it to the server

  4. The server checks whether username/SHA256(SHA256(user_password)) exists in the memory cache. If so, it is valid. Send the fast_AUTH_SUCCESS package to the client

  5. The server sends the OK package to the client

  6. Enter command phase

Note

In information security, a Nonce is a number that can only be used once in encrypted communication. In authentication protocols, it tends to be a random or pseudo-random number (SALT) to avoid violent attacks.

Since the caching_sha2_PASSWORD plug-in can quickly authenticate with caching, it is not effective for some or all users:

  • When a user’s password is changed, the password hashes cached by the user are removed from memory. The PASSWORD can be changed by using ALTER USER/SET PASSWORD/GRANT.
  • When the user is deleted, or the certificate, or the authentication plug-in changes; Password hashes cached by the user are removed from memory.
  • When a USER RENAME USER, all password hashes cached by the USER are removed from memory.
  • When FLUSH PRIVILEGES is executed, all cached password hashes are removed from memory, affecting all users. The cache is cleared when the server is shut 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 be exchanged using either a secure connection (TCP connection using TLS, Unix socket files, or shared memory) or an RSA encryption password.

Given that user password changes and FLUSH PRIVILEGES are infrequently performed, challenge-Response authentication is sufficient in most cases. The following summarizes the mechanism based on the full authentication mode (perform_full_authentication) (also known as the Complete authentication mode).

  1. The client connects to the server
  2. The server sends the Nonce (20 bytes of random data) to the client
  3. The client uses XOR(SHA256(password), SHA256(SHA256(password)), nonce)) to generate Scramble and send it to the server
  4. The server checks whether username/SHA256(user_password) exists in the memory cache. If no, the server sends the perform_full_authentication package to the client to continue authentication
  5. After receiving the perform_full_authentication packet, the client can perform the following operations
  6. If an SSL-based secure channel has been established, you can directly send the plaintext password to the server to request the server to obtain the public key (or specify the server public key file), use the public key +Nonce encryption password, and send the encrypted password to the server to obtain the hash value through the SHA256 algorithm. Check whether the user succeeds in authentication. If yes, send the OK package to the client to enter the command phase

Caching_sha2_password is divided into two phases: Complete authentication and Fast authentication. The respective authentication mechanisms have been described above. For a more detailed process, as well as the transition between server and client state, the official source documentation provides a diagram and instructions for reference, and the address is at the end of the article.

What has changed?

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

mysql> CREATE USER 'sha2user'@'localhost' IDENTIFIED BY '42'; Query OK, 0 rows affected (0.02sec) 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.01sec)Copy the code

Libmysqlclient uses caching_sha2_password as the default choice to connect to the MySQL server. Note that this is only a change in the default value, libmysqlClient is able to support all existing authentication plug-ins. MySQL’s Server-client protocol takes care of the authentication plug-ins needed to switch each user’s account.

If you don’t want to use the default caching_sha2_password plug-in, there are other plug-ins you can use to create accounts, and you must specify the plug-in explicitly. For example, using the mysql_native_password plug-in, use this statement:

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

Caching_sha2_password supports secure connection transmission and, if you follow the RSA configuration procedure described below, it also supports RSA encryption password exchange over unencrypted connections. RSA supports the following features:

  • On the server side, two system variables name the RSA private key and public key pair files: 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 an RSA key pair file is automatically generated. These variables are enabled by default. They can be enabled and checked at server startup, but not set at run time. See “Creating SSL and RSA Certificates and Keys” for details.
  • Status variable Caching_sha2_password_rsa_public_key Displays the RSA public key used by the caching_sha2_password authentication plug-in.
  • When the client holds an RSA public key, it can perform password exchange with the RSA key pair of the server during connection, as described later.
  • By default, the server does not send the RSA public key to the client for the connection authenticated by caching_sha2_password and RSA key. The client can use a copy of the required public key or request the public key from the server. It should be noted that using a copy of a trusted public key locally allows clients to avoid round-tripping over the Client /server protocol and is more secure than requesting a public key from a server. On the other hand, requesting a public key from the server is more convenient (it does not require file management on the client side) and is acceptable in a secure network environment.

For clients using the caching_sha2_password plug-in, the password is not exposed as plain text when connecting to the server. How the password transfer takes place depends on whether the password is encrypted using a secure connection or RSA:

  • If the connection is secure, an RSA key pair is unnecessary. This applies to TCP connections encrypted with TLS, as well as Unix socket files and shared memory connections. The password is sent in clear text, but 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 used only for password exchange between the client and server to prevent password interception. When the server receives a password encrypted using the public key, it decrypts it using the private key. A random string is used in encryption to prevent repeat attacks.

To enable clients to exchange passwords using RSA key pairs during connection, use the following steps (MySQL 8.0.3 and later does this automatically by default) :

  1. Create an RSA private key and public key pair file
  2. If both the private and public key files are in the data directory, Pem and public_key.pem (caching_sha2_PASSword_private_KEY_PATH and caching_sha2_PASSword_public_key_path) Default values for system variables), which the server automatically uses at startup. Otherwise, you need to specify the location of the private key and public key file in the configuration file.
  3. After restarting the server, check the value of the Caching_sha2_password_rsa_public_key status variable. The value will be different from the one shown here, but should not be null:
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-----
Copy the code

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

After the server has been configured with RSA key files, the user authenticates using the caching_sha2_password plug-in, which connects to the server’s options. As mentioned earlier, at this point the user can use a secure connection (in which case RSA is not used) or perform password exchange using RSA over an unencrypted connection. Assume that an unencrypted connection is used, for example:

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

When you try to connect as the sha2user user, the server confirms that the caching_sha2_password authentication plug-in is appropriate for Sha2User and invokes 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.
Copy the code

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
Copy the code

In this case, the server sends the RSA public key to the client, which uses it to encrypt the password and return the result to the server. The plug-in uses the server-side RSA private key to decrypt the password and decide whether 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 specify the file using the –server-public-key-path option:

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

In this case, the client uses the public key to encrypt the password and return the result to the server. The plug-in uses the server-side RSA private key to decrypt the password and decide whether 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 the caching_sha2_PASSword_public_key_PATH system variable. If the key file contains a valid public key value that is incorrect, an access denial error occurs. But if the key file does not contain a valid public key value, the client program cannot use it (because the client has done public key correctness verification).

Client users can obtain the RSA public key in two ways:

  • The database administrator can provide a copy of the public key file.
  • For client users that can connect to the server, run the SHOW STATUS LIKE “Caching_sha2_password_rsa_public_key” statement to return the public key value and save it in a file.

Impact on replication?

Replication itself is a connection that supports encryption. In MySQL 8.0.4, replication is also supported with RSA key pairs.

  • CHANGE MASTER now supports two parameters to enable password exchange based on the caching_sha2_password RSA key

    • MASTER_PUBLIC_KEY_PATH =”key_file_path”, specifies the path of the RSA public key
    • GET_MASTER_PUBLIC_KEY = {0} | 1, get the RSA public key from the server

  • Group Replication now supports two parameters to enable password exchange based on the caching_sha2_password RSA key

    • — group-replication-recovery-public-key-path: specifies the path of the RSA public key
    • — group-replication-recovery-get-public-key: obtains the RSA public key from the server

Note

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

Reference > <

Mp.weixin.qq.com/s/HetYIbLI8…

Mp.weixin.qq.com/s/jfoH6D8Nf…

Dev.mysql.com/doc/refman/…

Dev.mysql.com/doc/dev/mys…

Mysqlserverteam.com/mysql-8-0-4…

Article/east green

Pay attention to the technology, do the most fashionable technology!