MySQL provides an authentication plugin that implements SHA-256 hashing for user account passwords.
To connect to the server using an account that authenticates
sha256_password plugin, you must
use either an SSL connection or an unencrypted connection that
encrypts the password using RSA, as described later in this
section. Either way, the
plugin uses MySQL's SSL capabilities. See
Section 6.4, “Using Secure 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|
|Client-side plugin name|
|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.8, “Pluggable Authentication”.
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
libmysqlclientclient library and available to any program linked against
To set up an account that uses the
sha256_password plugin for SHA-256 password
hashing, use the following statement:
CREATE USER 'sha256user'@'localhost' IDENTIFIED WITH sha256_password BY 'Sh@256Pa33';
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. As a result, it is
possible to create the account and set its password without
naming the plugin explicitly using this
CREATE USER syntax:
CREATE USER 'sha256user'@'localhost' IDENTIFIED BY 'Sh@256Pa33';
In this case, the server assigns the
sha256_password plugin to the account and
encrypts the password using SHA-256.
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
Another consequence of using
sha256_password as the default
authentication plugin is that to create an account that uses a
different plugin, you must specify that plugin using an
IDENTIFIED WITH clause in the
CREATE USER statement. For
example, to use the
plugin, use this statement:
CREATE USER 'nativeuser'@'localhost' IDENTIFIED WITH mysql_native_password BY 'N@tivePa33';
MySQL can be compiled using either OpenSSL or yaSSL (see
Section 6.4.1, “OpenSSL Versus yaSSL”). The
sha256_password plugin works with
distributions compiled using either package, but if MySQL is
compiled using OpenSSL, RSA encryption is available and
sha256_password implements the following
additional capabilities. (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,
sha256_password_public_key_path. It is intended that the database administrator will set these to the names of the RSA private and public key-pair files at server startup if the key files 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.
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 available:
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 available, the
sha256_passwordplugin 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 compiled using OpenSSL. The implication for MySQL distributions compiled using yaSSL is that SHA-256 passwords can be used only when clients use SSL to access the server. See Section 6.4.4, “Configuring MySQL to Use Secure Connections”.
Assuming that MySQL has been compiled using OpenSSL, the following procedure describes how to enable RSA encryption of passwords during the client connection process:
Create the RSA private and public key-pair files using the instructions in Section 6.4.6, “Creating SSL and RSA Certificates and Keys”.
If the private and public key files are located in the data directory and are named
public_key.pem(the default values of the
sha256_password_public_key_pathsystem variables), the server uses them automatically at startup.
Otherwise, in the server option file, set the system variables to the key file names. 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_keystatus 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,
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 an SSL connection (in
which case RSA is not used) or an unencrypted 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
mysql --ssl-mode=DISABLED -u sha256user -pEnter password:
For connection attempts by
server determines that
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.
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 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:
mysql --ssl-mode=DISABLED -u sha256user -p --server-public-key-path=Enter password:
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
sha256_password plugin 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.