MySQL provides two authentication plugins that implement SHA-256 hashing for user account passwords:
caching_sha2_password
: Implements SHA-256 authentication (like the deprecatedsha256_password
), but uses caching on the server side for better performance and has additional features for wider applicability.sha256_password
: Implements basic SHA-256 authentication. This is deprecated as of MySQL 8.0.16 and subject to removal in the future.
This section describes the caching SHA-2 authentication plugin. For information about the original basic (noncaching) plugin, see Section 6.1.3, “SHA-256 Pluggable Authentication”.
In MySQL 8.0, caching_sha2_password
is the
default authentication plugin rather than
mysql_native_password
. For information
about the implications of this change for server operation and
compatibility of the server with clients and connectors, see
caching_sha2_password as the Preferred Authentication Plugin.
To connect to the server using an account that authenticates
with the caching_sha2_password
plugin, you
must use either a secure connection or an unencrypted
connection that supports password exchange using an RSA key
pair, as described later in this section. Either way, the
caching_sha2_password
plugin uses MySQL's
encryption capabilities. See
Chapter 5, Using Encrypted Connections.
In the name sha256_password
,
“sha256” refers to the 256-bit digest length the
plugin uses for encryption. In the name
caching_sha2_password
, “sha2”
refers more generally to the SHA-2 class of encryption
algorithms, of which 256-bit encryption is one instance. The
latter name choice leaves room for future expansion of
possible digest lengths without changing the plugin name.
The caching_sha2_password
plugin has these
advantages, compared to sha256_password
:
On the server side, an in-memory cache enables faster reauthentication of users who have connected previously when they connect again.
RSA-based password exchange is available regardless of the SSL library against which MySQL is linked.
Support is provided for client connections that use the Unix socket-file and shared-memory protocols.
The following table shows the plugin names on the server and client sides.
Table 6.2 Plugin and Library Names for SHA-2 Authentication
Plugin or File | Plugin or File Name |
---|---|
Server-side plugin | caching_sha2_password |
Client-side plugin | caching_sha2_password |
Library file | None (plugins are built in) |
The following sections provide installation and usage information specific to caching SHA-2 pluggable authentication:
For general information about pluggable authentication in MySQL, see Section 4.17, “Pluggable Authentication”.
The caching_sha2_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 againstlibmysqlclient
.
The server-side plugin uses the
sha2_cache_cleaner
audit plugin as a helper
to perform password cache management.
sha2_cache_cleaner
, like
caching_sha2_password
, is built in and need
not be installed.
To set up an account that uses the
caching_sha2_password
plugin for SHA-256
password hashing, use the following statement, where
password
is the desired account
password:
CREATE USER 'sha2user'@'localhost'
IDENTIFIED WITH caching_sha2_password BY 'password';
The server assigns the
caching_sha2_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
caching_sha2_password
is the default
authentication plugin. If
caching_sha2_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 caching_sha2_password
, put these lines
in the server option file:
[mysqld]
default_authentication_plugin=caching_sha2_password
That causes the caching_sha2_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 'sha2user'@'localhost' IDENTIFIED BY 'password';
Another consequence of setting
default_authentication_plugin
to caching_sha2_password
is that, to use
some other plugin for account creation, you must specify that
plugin explicitly. For example, to use the deprecated
mysql_native_password
plugin, use this
statement:
CREATE USER 'nativeuser'@'localhost'
IDENTIFIED WITH mysql_native_password BY 'password';
caching_sha2_password
supports connections
over secure transport. If you follow the RSA configuration
procedure given later in this section, it also supports
encrypted password exchange using RSA over unencrypted
connections. RSA support has these characteristics:
On the server side, two system variables name the RSA private and public key-pair files:
caching_sha2_password_private_key_path
andcaching_sha2_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 uses the
caching_sha2_password_auto_generate_rsa_keys
system variable to determine whether to automatically generate the RSA key-pair files. See Section 5.3, “Creating SSL and RSA Certificates and Keys”.The
Caching_sha2_password_rsa_public_key
status variable displays the RSA public key value used by thecaching_sha2_password
authentication plugin.Clients that are in possession of 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
caching_sha2_password
and RSA key pair-based password exchange, the server does not send the RSA public key to clients by default. Clients can use a client-side copy of the required public key, or request the public key from the server.Use of a trusted local copy of the public key enables the client to avoid a round trip in the client/server protocol, and is more secure than requesting the public key from the server. On the other hand, requesting the public key from the server is more convenient (it requires no management of a client-side file) and may be acceptable in secure network environments.
For command-line clients, use the
--server-public-key-path
option to specify the RSA public key file. Use the--get-server-public-key
option to request the public key from the server. The following programs support the two options: mysql, mysqlsh, mysqladmin, mysqlbinlog, mysqlcheck, mysqldump, mysqlimport, mysqlpump, mysqlshow, mysqlslap, mysqltest, mysql_upgrade.For programs that use the C API, call
mysql_options()
to specify the RSA public key file by passing theMYSQL_SERVER_PUBLIC_KEY
option and the name of the file, or request the public key from the server by passing theMYSQL_OPT_GET_SERVER_PUBLIC_KEY
option.For replicas, use the
CHANGE REPLICATION SOURCE TO
statement (from MySQL 8.0.23) orCHANGE MASTER TO
statement (before MySQL 8.0.23) with theSOURCE_PUBLIC_KEY_PATH
|MASTER_PUBLIC_KEY_PATH
option to specify the RSA public key file, or theGET_SOURCE_PUBLIC_KEY
|GET_MASTER_PUBLIC_KEY
option to request the public key from the source. For Group Replication, thegroup_replication_recovery_public_key_path
andgroup_replication_recovery_get_public_key
system variables serve the same purpose.
In all cases, if the option is given to specify a valid public key file, it takes precedence over the option to request the public key from the server.
For clients that use the
caching_sha2_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 TCP connections encrypted using TLS, as well as Unix socket-file and shared-memory connections. The password is sent as cleartext but cannot be snooped because the connection is secure.
If the connection is not secure, an RSA key pair is used. This applies to TCP connections not encrypted using TLS and named-pipe connections. 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.
To enable use of an RSA key pair for password exchange during the client connection process, use the following procedure:
Create the RSA private and public key-pair files using the instructions in Section 5.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
andpublic_key.pem
(the default values of thecaching_sha2_password_private_key_path
andcaching_sha2_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] caching_sha2_password_private_key_path=myprivkey.pem caching_sha2_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] caching_sha2_password_private_key_path=/usr/local/mysql/myprivkey.pem caching_sha2_password_public_key_path=/usr/local/mysql/mypubkey.pem
If you want to change the number of hash rounds used by
caching_sha2_password
during password generation, set thecaching_sha2_password_digest_rounds
system variable. For example:[mysqld] caching_sha2_password_digest_rounds=10000
Restart the server, then connect to it and check the
Caching_sha2_password_rsa_public_key
status variable value. The value actually displayed differs from that shown here, but should be nonempty:mysql> SHOW STATUS LIKE 'Caching_sha2_password_rsa_public_key'\G *************************** 1. row *************************** Variable_name: Caching_sha2_password_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
caching_sha2_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:
$> mysql --ssl-mode=DISABLED -u sha2user -p
Enter password: password
For this connection attempt by sha2user
,
the server determines that
caching_sha2_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. However, the server does not send the public key
to the client, and the client provided no public key, so it
cannot encrypt the password and the connection fails:
ERROR 2061 (HY000): Authentication plugin 'caching_sha2_password'
reported error: Authentication requires secure connection.
To request the RSA public key from the server, specify the
--get-server-public-key
option:
$> mysql --ssl-mode=DISABLED -u sha2user -p --get-server-public-key
Enter password: password
In this case, the server 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.
Alternatively, 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:
$> mysql --ssl-mode=DISABLED -u sha2user -p --server-public-key-path=file_name
Enter password: password
In this case, the client uses the public key 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 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
caching_sha2_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.
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 'Caching_sha2_password_rsa_public_key'
statement and save the returned key value in a file.
On the server side, the
caching_sha2_password
plugin uses an
in-memory cache for faster authentication of clients who have
connected previously. Entries consist of
account-name/password-hash pairs. The cache works like this:
When a client connects,
caching_sha2_password
checks whether the client and password match some cache entry. If so, authentication succeeds.If there is no matching cache entry, the plugin attempts to verify the client against the credentials in the
mysql.user
system table. If this succeeds,caching_sha2_password
adds an entry for the client to the hash. Otherwise, authentication fails and the connection is rejected.
In this way, when a client first connects, authentication
against the mysql.user
system table occurs.
When the client connects subsequently, faster authentication
against the cache occurs.
Password cache operations other than adding entries are
handled by the sha2_cache_cleaner
audit
plugin, which performs these actions on behalf of
caching_sha2_password
:
It clears the cache entry for any account that is renamed or dropped, or any account for which the credentials or authentication plugin are changed.
It empties the cache when the
FLUSH PRIVILEGES
statement is executed.It empties the cache at server shutdown. (This means the cache is not persistent across server restarts.)
Cache clearing operations affect the authentication requirements for subsequent client connections. For each user account, the first client connection for the user after any of the following operations must use a secure connection (made using TCP using TLS credentials, a Unix socket file, or shared memory) or RSA key pair-based password exchange:
After account creation.
After a password change for the account.
After
RENAME USER
for the account.After
FLUSH PRIVILEGES
.
FLUSH PRIVILEGES
clears the
entire cache and affects all accounts that use the
caching_sha2_password
plugin. The other
operations clear specific cache entries and affect only
accounts that are part of the operation.
Once the user authenticates successfully, the account is entered into the cache and subsequent connections do not require a secure connection or the RSA key pair, until another cache clearing event occurs that affects the account. (When the cache can be used, the server uses a challenge-response mechanism that does not use cleartext password transmission and does not require a secure connection.)