MySQL 5.6 Reference Manual Including MySQL NDB Cluster 7.3-7.4 Reference Guide
MySQL provides an authentication plugin that implements SHA-256 hashing for user account passwords.
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.3, “Using Encrypted Connections”.
The following table shows the plugin names on the server and client sides.
Table 6.10 Plugin and Library Names for SHA-256 Authentication
Plugin or File | Plugin or File Name |
---|---|
Server-side plugin | sha256_password |
Client-side plugin | sha256_password |
Library file | 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.2.11, “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
.
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('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:
CREATE USER 'sha256user'@'localhost' IDENTIFIED BY 'password
';
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 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('
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.password
');
For more information about
old_passwords
and
PASSWORD()
, see
Section 5.1.7, “Server System Variables”, and
Section 12.14, “Encryption and Compression Functions”.
MySQL can be compiled using either OpenSSL or yaSSL (see
Section 6.3.4, “SSL Library-Dependent Capabilities”). 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.)
It is possible to compile MySQL using yaSSL as an alternative to OpenSSL only prior to MySQL 5.6.46. As of MySQL 5.6.46, support for yaSSL is removed and all MySQL builds use OpenSSL.
RSA support has these characteristics:
On the server side, two system variables 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 Rsa_public_key
status variable displays the RSA public key value used by
the sha256_password
authentication
plugin.
Clients that have the RSA public key can perform RSA key pair-based password exchange with the server during the connection process, as described later.
For connections by accounts that authenticate with
sha256_password
and RSA public key
pair-based password exchange, the server sends the RSA
public key to the client as needed. However, if a copy of
the public key is available on the client host, the client
can use it to save a round trip in the client/server
protocol:
For these command-line clients, use the
--server-public-key-path
option to specify the RSA public key file:
mysql,
mysqltest.
For programs that use the C API, call
mysql_options()
to
specify the RSA public key file by passing the
MYSQL_SERVER_PUBLIC_KEY
option and
the name of the file.
For replicas, RSA key pair-based password exchange
cannot be used to connect to source servers for
accounts that authenticate with the
sha256_password
plugin. For such
accounts, only secure connections can be used.
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 connections encrypted using TLS. The password is sent as cleartext but cannot be snooped because the connection is secure.
sha256_password
does not treat
shared-memory connections as secure, even though
share-memory transport is secure by default.
If the connection is not secure, and an RSA key pair is available, the connection remains unencrypted. This applies to connections not encrypted using 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.3.1, “Configuring MySQL to Use Encrypted Connections”.
To use RSA password encryption with
sha256_password
, the client and server
both must be compiled using OpenSSL, not just one of them.
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.3.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 differs 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 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=
Enter password:file_name
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:
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.