Documentation Home
MySQL 8.0 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 33.6Mb
PDF (A4) - 33.6Mb
PDF (RPM) - 31.7Mb
HTML Download (TGZ) - 8.1Mb
HTML Download (Zip) - 8.1Mb
HTML Download (RPM) - 6.9Mb
Man Pages (TGZ) - 145.6Kb
Man Pages (Zip) - 206.4Kb
Info (Gzip) - 3.1Mb
Info (Zip) - 3.1Mb


MySQL 8.0 Reference Manual  /  ...  /  SHA-2 Pluggable Authentication

Pre-General Availability Draft: 2017-10-20

6.5.1.3 SHA-2 Pluggable Authentication

MySQL provides two authentication plugins that implement SHA-256 hashing for user account passwords:

  • sha256_password: Implements basic SHA-256 authentication.

  • caching_sha2_password: Implements SHA-256 authentication (like sha256_password), but uses caching for better performance and has additional features for wider applicability.

This section describes the caching SHA-2 authentication plugin. For information about the original basic (noncaching) plugin, see Section 6.5.1.2, “SHA-256 Pluggable Authentication”.

Note

In the name sha2_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:

  • 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.12 Plugin and Library Names for SHA-2 Authentication

Server-side plugin namecaching_sha2_password
Client-side plugin namecaching_sha2_password
Library file nameNone (plugins are built in)

The following sections provide installation and usage information specific to caching SHA-2pluggable authentication:

For general information about pluggable authentication in MySQL, see Section 6.3.10, “Pluggable Authentication”.

Installing SHA-2 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 against libmysqlclient.

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.

Using SHA-2 Pluggable Authentication

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 can be assumed 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 using caching_sha2_password as the default authentication plugin is that, to use some other plugin for account creation, you must specify that plugin explicitly. For example, to use the 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:

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 encrypted TCP connections that use 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 unencrypted TCP connections without 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:

  1. Create the RSA private and public key-pair files using the instructions in Section 6.4.3, “Creating SSL and RSA Certificates and Keys”.

  2. 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 caching_sha2_password_private_key_path and caching_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

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 encrypts the password using RSA. Suppose that an unencrypted connection is used. For example:

shell> 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 unless requested. Because the client made no such request, 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, specify the --get-server-public-key option:

shell> 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.

Cache Operation for SHA-2 Pluggable Authentication

The caching_sha2_password 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:

  1. When a client connects, caching_sha2_password checks whether the client and password match some cache entry. If so, authentication succeeds.

  2. 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 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.)


User Comments
Sign Up Login You must be logged in to post a comment.