As of MySQL 5.6.6, MySQL provides an authentication plugin that implements SHA-256 hashing for user account passwords. The following table shows the plugin names on the server and client sides.

The server-side sha256_password plugin is built into the server, need not be loaded explicitly, and cannot be disabled by unloading it. Similarly, clients need not specify the location of the client-side plugin.

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

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 and sets old_passwords to 2. As a result, it is possible to set the password at account-creation time with the CREATE USER statement:

mysql> CREATE USER 'sha256user2'@'localhost'
        IDENTIFIED BY 'sha256P@ss2';Query OK, 0 rows affected (0.06 sec)

In this case, the server assigns the sha256_password plugin to the account and encrypts the password using SHA-256. (Another consequence is that to create an account that uses a different authentication plugin, you must specify that plugin in the CREATE USER statement, then set old_passwords appropriately for the plugin before using SET PASSWORD to set the account password.)

If old_passwords has a value other than 2, an error occurs for attempts to set the password for an account that requires a SHA-256 password:

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

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

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

MySQL can be built with either yaSSL or OpenSSL and the sha256_password plugin works with distributions built using either package. The default is to use yaSSL. If MySQL is built using OpenSSL instead, RSA encryption is available and sha256_password implements the additional capabilities in the following list. (To enable these capabilities, you must also follow the RSA configuration procedure given later in this section.)

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 an SSL connection is used and whether RSA encryption is available:

As mentioned previously, RSA password encryption is available only if MySQL was built using OpenSSL. The implication for MySQL distributions built using yaSSL is that SHA-256 passwords can be used only when clients access the server using an SSL connection. For information about connecting to the server using SSL, see Section 6.3.9, "Using SSL for Secure Connections".

Assuming that MySQL has been built with OpenSSL, the following procedure describes how to enable RSA encryption of passwords during the client connection process:

After the server has been configured with the RSA key files, clients can connect to it using accounts that authenticate with the sha256_password plugin. As mentioned previously, such accounts can use either an SSL connection (in which case RSA is not used) or a plain connection that encrypts the password using RSA. Assume for the following discussion that SSL is not used. Connecting to the server involves no special preparation on the client side. For example:

shell> mysql -u sha256user -pEnter password: sha256P@ss

For connection attempts by sha256user, the server determines that sha256_password is the appropriate authentication plugin and invokes it. The plugin finds that the connection does not use SSL and thus requires the password to be transmitted using RSA encryption. It 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 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 -u sha256user -p
        --server-public-key-path=file_name

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 server 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: