6.4.1.4 Caching SHA-2 Pluggable Authentication (original) (raw)

6.4.1.4 Caching SHA-2 Pluggable Authentication

MySQL provides two authentication plugins that implement SHA-256 hashing for user account passwords:

This section describes the caching SHA-2 authentication plugin, available as of MySQL 5.7.23. For information about the original basic (noncaching) plugin, seeSection 6.4.1.5, “SHA-256 Pluggable Authentication”.

Important

In MySQL 5.7, the default authentication plugin ismysql_native_password. As of MySQL 8.0, the default authentication plugin is changed tocaching_sha2_password. To enable MySQL 5.7 clients to connect to 8.0 and higher servers using accounts that authenticate withcaching_sha2_password, the MySQL 5.7 client library and client programs support thecaching_sha2_password client-side authentication plugin. This improves MySQL 5.7 client connect-capability compatibility with respect to MySQL 8.0 and higher servers, despite the differences in default authentication plugin.

Limiting caching_sha2_password support in MySQL 5.7 to the client-side plugin in the client library has these implications compared to MySQL 8.0:

In addition, there is no support for MySQL 5.7 replicas to connect to MySQL 8.0 replication source servers using accounts that authenticate withcaching_sha2_password. That would involve a source replicating to a replica with a version number lower than the source version, whereas sources normally replicate to replicas having a version equal to or higher than the source version.

Important

To connect to a MySQL 8.0 or higher server using an account that authenticates with thecaching_sha2_password plugin, you must use either a secure connection or an unencrypted connection that supports password exchange using an RSA key pair, as described later in this section. Either way, thecaching_sha2_password plugin uses MySQL's encryption capabilities. SeeSection 6.3, “Using Encrypted Connections”.

Note

In the name sha256_password,“sha256” refers to the 256-bit digest length the plugin uses for encryption. In the namecaching_sha2_password, “sha2” refers more generally to the SHA-2 class of encryption algorithms, of which 256-bit encryption is one instance. The latter name choice leaves room for future expansion of possible digest lengths without changing the plugin name.

The caching_sha2_password plugin has these advantages, compared to sha256_password:

The following table shows the plugin name on the client side.

Table 6.10 Plugin and Library Names for SHA-2 Authentication

Plugin or File Plugin or File Name
Client-side plugin caching_sha2_password
Library file None (plugin is built in)

The following sections provide installation and usage information specific to caching SHA-2 pluggable authentication:

For general information about pluggable authentication in MySQL, see Section 6.2.13, “Pluggable Authentication”.

Installing SHA-2 Pluggable Authentication

In MySQL 5.7, the caching_sha2_password plugin exists in client form. The client-side plugin is built into the libmysqlclient client library and is available to any program linked againstlibmysqlclient.

Using SHA-2 Pluggable Authentication

In MySQL 5.7, the caching_sha2_password client-side plugin enables connecting to MySQL 8.0 or higher servers using accounts that authenticate with thecaching_sha2_password server-side plugin. The discussion here assumes that an account named'sha2user'@'localhost' exists on the MySQL 8.0 or higher server. For example, the following statement creates such an account, where_password_ is the desired account password:

CREATE USER 'sha2user'@'localhost'
IDENTIFIED WITH caching_sha2_password BY 'password';

caching_sha2_password supports connections over secure transport.caching_sha2_password also supports encrypted password exchange using RSA over unencrypted connections if these conditions are satisfied:

RSA support has these characteristics, where all aspects that pertain to the server side require a MySQL 8.0 or higher server:

For clients that use thecaching_sha2_password plugin, passwords are never exposed as cleartext when connecting to the MySQL 8.0 or higher server. How password transmission occurs depends on whether a secure connection or RSA encryption is used:

As mentioned previously, RSA password encryption is available only if MySQL 5.7 was compiled using OpenSSL. The implication for clients from MySQL 5.7 distributions compiled using yaSSL is that, to use SHA-2 passwords, clients_must_ use an encrypted connection to access the server. SeeSection 6.3.1, “Configuring MySQL to Use Encrypted Connections”.

Assuming that MySQL 5.7 has been compiled using OpenSSL, use the following procedure to enable use of an RSA key pair for password exchange during the client connection process.

Important

Aspects of this procedure that pertain to server configuration must be done on the MySQL 8.0 or higher server to which you wish to connect using MySQL 5.7 clients,not on your MySQL 5.7 server.

  1. Create the RSA private and public key-pair files using the instructions in Section 6.3.3, “Creating SSL and RSA Certificates and Keys”.
  2. If the private and public key files are located in the data directory and are namedprivate_key.pem andpublic_key.pem (the default values of thecaching_sha2_password_private_key_path andcaching_sha2_password_public_key_path system variables), the server uses them automatically at startup.
    Otherwise, to name the key files explicitly, set the system variables to the key file names in the server option file. If the files are located in the server data directory, you need not specify their full path names:
[mysqld]  
caching_sha2_password_private_key_path=myprivkey.pem  
caching_sha2_password_public_key_path=mypubkey.pem

If the key files are not located in the data directory, or to make their locations explicit in the system variable values, use full path names: 

[mysqld]  
caching_sha2_password_private_key_path=/usr/local/mysql/myprivkey.pem  
caching_sha2_password_public_key_path=/usr/local/mysql/mypubkey.pem
  1. Restart the server, then connect to it and check theCaching_sha2_password_rsa_public_key status variable value. The actual value differs from that shown here, but should be nonempty:
mysql> SHOW STATUS LIKE 'Caching_sha2_password_rsa_public_key'\G  
*************************** 1. row ***************************  
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, the server found some problem with the key files. Check the error log for diagnostic information.

After the server has been configured with the RSA key files, accounts that authenticate with thecaching_sha2_password plugin have the option of using those key files to connect to the server. As mentioned previously, such accounts can use either a secure connection (in which case RSA is not used) or an unencrypted connection that performs password exchange using RSA. Suppose that an unencrypted connection is used. For example:

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

For this connection attempt by sha2user, the server determines thatcaching_sha2_password is the appropriate authentication plugin and invokes it (because that was the plugin specified at CREATE USER time). The plugin finds that the connection is not encrypted and thus requires the password to be transmitted using RSA encryption. However, the server does not send the public key to the client, and the client provided no public key, so 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:

$> 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, which uses it to encrypt the password and returns the result to the server. The plugin uses the RSA private key on the server side to decrypt the password and accepts or rejects the connection based on whether the password is correct.

Alternatively, if the client has a file containing a local copy of the RSA public key required by the server, it can specify the file using the--server-public-key-path option:

$> 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 plugin uses the RSA private key on the server side to decrypt the password and accepts or rejects the connection based on whether the password is correct.

The public key value in the file named by the--server-public-key-path option should be the same as the key value in the server-side file named by thecaching_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 occurs. If the key file does not contain a valid public key, the client program cannot use it.

Client users can obtain the RSA public key two ways:

Cache Operation for SHA-2 Pluggable Authentication

On the server side, thecaching_sha2_password plugin uses an in-memory cache for faster authentication of clients who have connected previously. For MySQL 5.7, which supports only thecaching_sha2_password client-side plugin, this server-side caching thus takes place on the MySQL 8.0 or higher server to which you connect using MySQL 5.7 clients. For information about cache operation, seeCache Operation for SHA-2 Pluggable Authentication, in the MySQL 8.0 Reference Manual.