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.
Table 5.3. MySQL SHA-256 Authentication Plugin
|Server-side plugin name|
|Client-side plugin name|
|Library object file name||None (plugins are built in)|
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.
Use of the
sha256_password plugin requires
that MySQL be built with SSL capabilities. See
Section 5.9, “Using SSL for Secure Connections”.
To set up an account that uses SHA-256 password hashing, use the following procedure.
Create the account and specify that it authenticates using
CREATE USER 'sha256user'@'localhost' IDENTIFIED WITH sha256_password;
old_passwords system variable to
2 to select SHA-256 hashing of password strings by the
SET old_passwords = 2;
Set the account password:
SET PASSWORD FOR 'sha256user'@'localhost' = PASSWORD('sha256P@ss');
Alternatively, start the server with the default authentication
plugin set to
sha256_password. For example,
put these lines in the server option file:
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:
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
SET PASSWORD to set
the account password.)
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:
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 the PASSWORD() function.
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
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
the additional capabilities in the following list. (To enable
these capabilities, you must also follow the RSA configuration
procedure given later in this section.)
It is possible for the client to transmit passwords to the server using RSA encryption during the client connection process, as described later.
The server exposes two additional system variables,
It is intended that the database administrator will set
these to the names of the RSA private and public key files
at server startup.
The server exposes a status variable,
displays the RSA public key value.
The mysql and
mysqltest client programs support a
option for specifying an RSA public key file explicitly.
(This option was added in MySQL 5.6.6 under the name
--server-public-key and renamed in 5.6.7 to
For clients that use the
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
If an SSL connection is used, the password is sent as cleartext but cannot be snooped because the connection is encrypted using SSL.
If an SSL connection is not used but RSA encryption is available, the password is sent within an unencrypted connection, but the password is RSA-encrypted to prevent snooping. When the server receives the password, it decrypts it. A scramble is used in the encryption to prevent repeat attacks.
If an SSL connection is not used and RSA encryption is not
causes the connection attempt to fail because the password
cannot be sent without being exposed as cleartext.
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 5.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:
Create the RSA private and public key files. Run these commands while logged into the system account used to run the MySQL server so the files will be owned by that account:
openssl genrsa -out mykey.pem 1024 openssl rsa -in mykey.pem -pubout > mykey.pub
Set the access modes for the key files. The private key should be readable only by the server:
chmod 400 mykey.pem
The public key can be freely distributed to client users:
chmod 444 mykey.pub
In the server option file, configure the appropriate system variables with the names of the key files. If you place the files in the server data directory, you need not specify their full path names:
[mysqld] sha256_password_private_key_path=mykey.pem sha256_password_public_key_path=mykey.pub
If the files are not in the data directory, or to make their locations explicit in the option values, use full path names:
[mysqld] sha256_password_private_key_path=/usr/local/mysql/mykey.pem sha256_password_public_key_path=/usr/local/mysql/mykey.pub
Restart the server, then connect to it and check the
variable value. The value will differ from that shown here,
but should be nonempty:
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,
clients can connect to it using accounts that authenticate with
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:
mysql -u sha256user -pEnter password:
For connection attempts by
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
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:
mysql -u sha256user -p --server-public-key-path=
The public key value in the file named by the
should be the same as the key value in the server-side file
named by the
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
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.