MySQL :: MySQL 8.4 Reference Manual :: 8.4.1.3 SHA-256 Pluggable Authentication (original) (raw)

8.4.1.3 SHA-256 Pluggable Authentication

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

Important

In MySQL 8.4,caching_sha2_password is the default authentication plugin rather thanmysql_native_password (deprecated). For information about the implications of this change for server operation and compatibility of the server with clients and connectors, seecaching_sha2_password as the Preferred Authentication Plugin.

Because caching_sha2_password is the default authentication plugin in MySQL 8.4 and provides a superset of the capabilities of thesha256_password authentication plugin,sha256_password is deprecated; expect it to be removed in a future version of MySQL. MySQL accounts that authenticate using sha256_password should be migrated to use caching_sha2_password instead.

The following table shows the plugin names on the server and client sides.

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

Installing SHA-256 Pluggable Authentication

The sha256_password (deprecated) plugin exists in server and client forms:

Using SHA-256 Pluggable Authentication

To set up an account that uses the deprecatedsha256_password plugin for SHA-256 password hashing, use the following statement, where_password_ is the desired account password:

CREATE USER 'sha256user'@'localhost'
IDENTIFIED WITH sha256_password BY 'password';

The server assigns the sha256_password plugin to the account and uses it to encrypt the password using SHA-256, storing those values in theplugin andauthentication_string columns of themysql.user system table.

(The IDENTIFIED WITH clause is not needed if sha256_password is the default plugin; this can be specified usingauthentication_policy.)

sha256_password supports connections over secure transport. sha256_password also supports encrypted password exchange using RSA over unencrypted connections if MySQL is compiled using OpenSSL, and the MySQL server to which you wish to connect is configured to support RSA (using the RSA configuration procedure given later in this section).

RSA support has these characteristics:

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

Note

To use RSA password encryption with the deprecatedsha256_password plugin, the client and server both must be compiled using OpenSSL, not just one of them.

Assuming that MySQL 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:

  1. Create the RSA private and public key-pair files using the instructions in Section 8.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 thesha256_password_private_key_path andsha256_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]  
sha256_password_private_key_path=myprivkey.pem  
sha256_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]  
sha256_password_private_key_path=/usr/local/mysql/myprivkey.pem  
sha256_password_public_key_path=/usr/local/mysql/mypubkey.pem
  1. Restart the server, then connect to it and check theRsa_public_key status variable value. The value actually displayed differs from that shown here, but should be nonempty:
mysql> SHOW STATUS LIKE 'Rsa_public_key'\G  
*************************** 1. row ***************************  
Variable_name: 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 the deprecatedsha256_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 sha256user -p
Enter password: password

For this connection attempt by sha256user, the server determines that sha256_password is the appropriate authentication plugin and invokes it (because that was the plugin specified atCREATE USER time). The plugin finds that the connection is not encrypted and thus requires the password to be transmitted using RSA encryption. In this case, the plugin 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.

The server sends the RSA public key to the client as needed. However, 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 sha256user -p --server-public-key-path=file_name
Enter password: password

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 thesha256_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. In this case, the deprecatedsha256_password plugin sends the public key to the client as if no--server-public-key-path option had been specified.

Client users can obtain the RSA public key two ways: