6.5.1.2 SHA-256 Pluggable Authentication

The sha256_password plugin exists in server and client forms:

To set up an account that uses the sha256_password plugin for SHA-256 password hashing, use the following statement, where password is the desired account 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 the plugin and authentication_string columns of the mysql.user system table.

The preceding instructions do not assume that sha256_password is the default authentication plugin. If sha256_password is the default authentication plugin, a simpler CREATE USER syntax can be used.

To start the server with the default authentication plugin set to sha256_password, put these lines in the server option file:

[mysqld]
default_authentication_plugin=sha256_password

That causes the sha256_password plugin to be used by default for new accounts. As a result, it is possible to create the account and set its password without naming the plugin explicitly:

Another consequence of setting default_authentication_plugin to sha256_password is that, to use some other plugin for account creation, you must specify that plugin explicitly. For example, to use the mysql_native_password plugin, use this statement:

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

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:

As mentioned previously, RSA password encryption is available only if MySQL was compiled using OpenSSL. The implication for MySQL distributions compiled using wolfSSL is that, to use SHA-256 passwords, clients must use an encrypted connection to access the server. See Section 6.4.1, “Configuring MySQL to Use Encrypted Connections”.

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:

After the server has been configured with the RSA key files, accounts that authenticate with the sha256_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:

shell> 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 at CREATE 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:

shell> 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 the sha256_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 sha256_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: