MySQL :: MySQL 5.6 Reference Manual :: 6.5.1.4 SHA-256 Pluggable Authentication

8.0
5.7 current

MySQL 5.6 Reference Manual / ... / SHA-256 Pluggable Authentication

6.5.1.4 SHA-256 Pluggable Authentication

MySQL provides an authentication plugin that implements SHA-256 hashing for user account passwords.

Important

To connect to the server using an account that authenticates with the sha256_password plugin, you must use either a TLS connection or an unencrypted connection that supports password exchange using an RSA key pair, as described later in this section. Either way, use of the sha256_password plugin requires that MySQL be built with SSL capabilities. See Section 6.4, “Using Encrypted Connections”.

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

Table 6.11 Plugin and Library Names for SHA-256 Authentication

Server-side plugin name	`sha256_password`
Client-side plugin name	`sha256_password`
Library file name	None (plugins are built in)

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

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

Installing SHA-256 Pluggable Authentication

The sha256_password plugin exists in server and client forms:

The server-side plugin is built into the server, need not be loaded explicitly, and cannot be disabled by unloading it.
The client-side plugin is built into the libmysqlclient client library and is available to any program linked against libmysqlclient.

Using SHA-256 Pluggable Authentication

To set up an account that uses the sha256_password plugin for SHA-256 password hashing, use the following procedure.

Create the account and specify that it authenticates using the sha256_password plugin:
```
CREATE USER 'sha256user'@'localhost' IDENTIFIED WITH sha256_password;
```
Set the old_passwords system variable to 2 to cause the PASSWORD() function to use SHA-256 hashing of password strings, then set the account password:
```
SET old_passwords = 2;
SET PASSWORD FOR 'sha256user'@'localhost' = PASSWORD('Sh@256Pa33');
```

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.

Alternatively, start the server with the default authentication plugin set to sha256_password. For example, 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:

CREATE USER 'sha256user'@'localhost' IDENTIFIED BY 'Sh@256Pa33';

Accounts in the mysql.user system table that use the SHA-256 plugin can be identified as rows with 'sha256_password' in the plugin column and a SHA-256 password hash in the authentication_string column.

Another consequence of using sha256_password as the default authentication plugin is that, to use some other plugin for account creation, you must specify that plugin explicitly in the CREATE USER statement, then set old_passwords appropriately for the plugin before using SET PASSWORD to set the account password. For example, to use the mysql_native_password plugin, do this:

CREATE USER 'nativeuser'@'localhost' IDENTIFIED WITH mysql_native_password;
SET old_passwords = 0;
SET PASSWORD FOR 'nativeuser'@'localhost' = PASSWORD('N@tivePa33');

To set or change the password for any account that authenticates using the sha256_password plugin, be sure that the value of old_passwords is 2 before using SET PASSWORD. If old_passwords has a value other than 2, an error occurs for attempts to set the password:

mysql> SET old_passwords = 0;
mysql> SET PASSWORD FOR 'sha256user'@'localhost' = PASSWORD('NewSh@256Pa33');
ERROR 1827 (HY000): The password hash doesn't have the expected format.
Check if the correct password algorithm is being used with the
PASSWORD() function.

For more information about old_passwords and PASSWORD(), see Section 5.1.5, “Server System Variables”, and Section 12.13, “Encryption and Compression Functions”.

MySQL can be compiled using either OpenSSL or yaSSL (see Section 6.4.4, “OpenSSL Versus yaSSL”). The sha256_password plugin works with distributions compiled using either package, but if MySQL is compiled using OpenSSL, sha256_password supports the use of RSA encryption. (To enable this capability, you must follow the RSA configuration procedure given later in this section.) RSA support has these characteristics:

The server exposes two system variables that name the RSA private and public key-pair files: sha256_password_private_key_path and sha256_password_public_key_path. The database administrator must set these variables at server startup if the key files to use have names that differ from the system variable default values.
The server exposes a status variable, Rsa_public_key, that displays the RSA public key value.
If they have the RSA public key, clients can perform RSA key pair-based password exchange with the server during the connection process, as described later.
These client programs support a --server-public-key-path option for specifying an RSA public key file explicitly: mysql, mysqltest.
Programs that use the C API can request the RSA public key by calling mysql_options() with the MYSQL_SERVER_PUBLIC_KEY option.

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:

If the connection is secure, an RSA key pair is unnecessary and is not used. This applies to encrypted connections that use TLS. The password is sent as cleartext but cannot be snooped because the connection is secure.
If the connection is not secure, and an RSA key pair is available, the connection remains unencrypted. This applies to unencrypted connections without TLS. RSA is used only for password exchange between client and server, to prevent password snooping. When the server receives the encrypted password, it decrypts it. A scramble is used in the encryption to prevent repeat attacks.
If a secure connection is not used and RSA encryption is not available, the connection attempt fails because the password cannot be sent without being exposed as cleartext.

As mentioned previously, RSA password encryption is available only if MySQL was compiled using OpenSSL. The implication for MySQL distributions compiled using yaSSL 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:

Create the RSA private and public key-pair files using the instructions in Section 6.4.3, “Creating SSL and RSA Certificates and Keys”.
If the private and public key files are located in the data directory and are named private_key.pem and public_key.pem (the default values of the sha256_password_private_key_path and sha256_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
```

Restart the server, then connect to it and check the Rsa_public_key status variable value. The value will differ 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 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 encrypts the password using RSA. Suppose that an unencrypted connection is used. For example:

shell> mysql --ssl-mode=DISABLED -u sha256user -p
Enter password: Sh@256Pa33

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 public key to the client as needed, but if a copy of the RSA public key is available on the client host, the client can use it to save a round trip in the client/server protocol:

shell> mysql --ssl-mode=DISABLED -u sha256user -p --server-public-key-path=file_name
Enter password: Sh@256Pa33

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 get the RSA public key two ways:

The database administrator can provide a copy of the public key file.
A client user who can connect to the server some other way can use a SHOW STATUS LIKE 'Rsa_public_key' statement and save the returned key value in a file.

PREV HOME UP NEXT

User Comments