MySQL provides two authentication plugins that implement SHA-256 hashing for user account passwords:MySQL提供了两个身份验证插件,为用户帐户密码实现SHA-256哈希:
sha256_password
: Implements basic SHA-256 authentication.:实现基本的SHA-256身份验证。
caching_sha2_password
: Implements SHA-256 authentication (like :实现SHA-256身份验证(如sha256_password
), but uses caching on the server side for better performance and has additional features for wider applicability.sha256_password
),但在服务器端使用缓存以获得更好的性能,并具有更广泛的适用性。
This section describes the caching SHA-2 authentication plugin. For information about the original basic (noncaching) plugin, see Section 6.4.1.3, “SHA-256 Pluggable Authentication”.本节介绍缓存SHA-2身份验证插件。有关原始基本(非缓存)插件的信息,请参阅第6.4.1.3节,“SHA-256可插拔身份验证”。
In MySQL 8.0, 在MySQL 8.0中,caching_sha2_password
is the default authentication plugin rather than mysql_native_password
. caching_sha2_password
是默认的身份验证插件,而不是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.有关此更改对服务器操作的影响以及服务器与客户端和连接器的兼容性的信息,请参阅caching_sha2_password
作为首选身份验证插件。
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. caching_sha2_password
插件进行身份验证的帐户连接到服务器,您必须使用安全连接或支持使用RSA密钥对进行密码交换的未加密连接,如本节稍后所述。Either way, the 无论哪种方式,caching_sha2_password
plugin uses MySQL's encryption capabilities. See Section 6.3, “Using Encrypted Connections”.caching_sha2_password
插件都使用MySQL的加密功能。请参阅第6.3节,“使用加密连接”。
In the name 在名称sha256_password
, “sha256” refers to the 256-bit digest length the plugin uses for encryption. sha256_password
中,“sha256”是指插件用于加密的256位摘要长度。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. caching_sha2_password
中,“sha2”更一般地指SHA-2类加密算法,其中256位加密是一个实例。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
:sha256_password
相比,caching_sha2_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.无论MySQL链接到哪个SSL库,都可以使用基于RSA的密码交换。
Support is provided for client connections that use the Unix socket-file and shared-memory protocols.支持使用Unix套接字文件和共享内存协议的客户端连接。
The following table shows the plugin names on the server and client sides.下表显示了服务器端和客户端的插件名称。
Table 6.13 Plugin and Library Names for SHA-2 AuthenticationSHA-2身份验证的插件和库名称
caching_sha2_password |
|
caching_sha2_password |
|
The following sections provide installation and usage information specific to caching SHA-2 pluggable authentication:以下部分提供了特定于缓存SHA-2可插拔身份验证的安装和使用信息:
For general information about pluggable authentication in MySQL, see Section 6.2.17, “Pluggable Authentication”.有关MySQL中可插拔身份验证的一般信息,请参阅第6.2.17节,“可插拔身份认证”。
The caching_sha2_password
plugin exists in server and client forms:caching_sha2_password
插件存在于服务器和客户端表单中:
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
.libmysqlclient
客户端库中,可供任何与libmysqlclient
链接的程序使用。
The server-side plugin uses the 服务器端插件使用sha2_cache_cleaner
audit plugin as a helper to perform password cache management. sha2_cache_cleaner
审计插件作为助手来执行密码缓存管理。sha2_cache_cleaner
, like caching_sha2_password
, is built in and need not be installed.sha2_cache_cleaner
与caching_sha2_password
一样,是内置的,不需要安装。
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:caching_sha2_password
插件进行SHA-256密码哈希的帐户,请使用以下语句,其中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.caching_sha2_password
插件分配给帐户,并使用它使用SHA-256对密码进行加密,将这些值存储在mysql.user
系统表的插件和authentication_string
列中。
The preceding instructions do not assume that 前面的说明并不假设caching_sha2_password
is the default authentication plugin. caching_sha2_password
是默认的身份验证插件。If 如果默认身份验证插件是caching_sha2_password
is the default authentication plugin, a simpler CREATE USER
syntax can be used.caching_sha2_password
,则可以使用更简单的CREATE USER
语法。
To start the server with the default authentication plugin set to 要使用设置为caching_sha2_password
, put these lines in the server option file:caching_sha2_password
的默认身份验证插件启动服务器,请将以下行放入服务器选项文件中:
[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:caching_sha2_password
插件。因此,可以创建帐户并设置密码,而无需显式命名插件:
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. default_authentication_plugin
设置为caching_sha2_password
的另一个后果是,要使用其他插件创建帐户,您必须明确指定该插件。For example, to use the 例如,要使用mysql_native_password插件,请使用以下语句: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. caching_sha2_password
支持通过安全传输进行连接。If you follow the RSA configuration procedure given later in this section, it also supports encrypted password exchange using RSA over unencrypted connections. 如果您遵循本节稍后给出的RSA配置过程,它还支持在未加密的连接上使用RSA进行加密密码交换。RSA support has these characteristics:RSA支持具有以下特点:
On the server side, two system variables name the RSA private and public key-pair files: 在服务器端,两个系统变量命名RSA私钥和公钥对文件:caching_sha2_password_private_key_path
and caching_sha2_password_public_key_path
. caching_sha2_password_private_key_path
和caching_sa2_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 6.3.3, “Creating SSL and RSA Certificates and Keys”.caching_sha2_password_auto_generate_rsa_keys
系统变量来确定是否自动生成rsa密钥对文件。请参阅第6.3.3节,“创建SSL和RSA证书和密钥”。
The Caching_sha2_password_rsa_public_key
status variable displays the RSA public key value used by the caching_sha2_password
authentication plugin.Caching_sha2_password_rsa_public_key
状态变量显示caching_sha2-password
身份验证插件使用的rsa公钥值。
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.拥有RSA公钥的客户端可以在连接过程中与服务器进行基于RSA密钥对的密码交换,如稍后所述。
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.caching_sha2_password
和基于RSA密钥对的密码交换进行身份验证的帐户的连接,默认情况下服务器不会向客户端发送RSA公钥。客户端可以使用所需公钥的客户端副本,也可以从服务器请求公钥。
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. --server-public-key-path
选项指定RSA公钥文件。Use the 使用--get-server-public-key
option to request the public key from the server. --get-server-public-key
选项向服务器请求公钥。The following programs support the two options: mysql, mysqlsh, mysqladmin, mysqlbinlog, mysqlcheck, mysqldump, mysqlimport, mysqlpump, mysqlshow, mysqlslap, mysqltest, mysql_upgrade.以下程序支持两个选项:mysql、mysqlsh、mysqladmin、mysqlbinlog、mysqlcheck、mysqldump、mysqlimport、mysqlpump、mysqlshow、mysqlslap、mysqltest、mysql_upgrade。
For programs that use the C API, call 对于使用C API的程序,调用mysql_options()
to specify the RSA public key file by passing the MYSQL_SERVER_PUBLIC_KEY
option and the name of the file, or request the public key from the server by passing the MYSQL_OPT_GET_SERVER_PUBLIC_KEY
option.mysql_options()
通过传递MYSQL_SERVER_PUBLIC_KEY
选项和文件名来指定RSA公钥文件,或者通过传递MYSQL_OPT_GET_SERVER_PUBLIC_KEY
选项来从服务器请求公钥。
For replicas, use the 对于副本,使用CHANGE REPLICATION SOURCE TO
statement (from MySQL 8.0.23) or CHANGE MASTER TO
statement (before MySQL 8.0.23) with the SOURCE_PUBLIC_KEY_PATH
| MASTER_PUBLIC_KEY_PATH
option to specify the RSA public key file, or the GET_SOURCE_PUBLIC_KEY
| GET_MASTER_PUBLIC_KEY
option to request the public key from the source. CHANGE REPLICATION SOURCE TO
语句(来自MySQL 8.0.23)或CHANGE MASTER TO
语句(MySQL 8.0.22之前),并使用SOURCE_PUBLIC_KEY_PATH
| MASTER _PUBLIC_ KEY_PATH
选项指定RSA公钥文件,或使用GET_SOURCE_PUBLIC_KEY
| GET_MASTER_PUBLIC KEY
选项从源请求公钥。For Group Replication, the 对于组复制,group_replication_recovery_public_key_path
and group_replication_recovery_get_public_key
system variables serve the same purpose.group_replication_recovery_public_key_path
和group_replication_recovery_get_public_key
系统变量具有相同的目的。
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:caching_sha2_password
插件的客户端,在连接到服务器时,密码永远不会以明文形式公开。密码传输的方式取决于使用的是安全连接还是RSA加密:
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. 如果连接是安全的,则不需要使用RSA密钥对。这适用于使用TLS加密的TCP连接,以及Unix套接字文件和共享内存连接。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 without TLS and named-pipe connections. 如果连接不安全,则使用RSA密钥对。这适用于未使用TLS加密的TCP连接和命名管道连接。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.RSA仅用于客户端和服务器之间的密码交换,以防止密码窥探。当服务器接收到加密的密码时,它会对其进行解密。加密过程中会使用加扰来防止重复攻击。
To enable use of an RSA key pair for password exchange during the client connection process, use the following procedure:要在客户端连接过程中使用RSA密钥对进行密码交换,请使用以下过程:
Create the RSA private and public key-pair files using the instructions in Section 6.3.3, “Creating SSL and RSA Certificates and Keys”.使用第6.3.3节,“创建SSL和RSA证书和密钥”中的说明创建RSA私钥和公钥对文件。
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.private_key.pem
和public_key.pem
(caching_sha2_password_private_key_path
和caching_sha2_password_public_key_path
系统变量的默认值),服务器将在启动时自动使用它们。
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 the caching_sha2_password_digest_rounds
system variable. For example:caching_sha2_password
在密码生成过程中使用的哈希轮数,请设置caching_sha 2_password_digest_rounds
系统变量。例如:
[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. Caching_sha2_password_rsa_public_key
状态变量值。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 在服务器配置了RSA密钥文件后,使用caching_sha2_password
plugin have the option of using those key files to connect to the server. caching_sha2_password
插件进行身份验证的帐户可以选择使用这些密钥文件连接到服务器。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. 如前所述,此类帐户可以使用安全连接(在这种情况下不使用RSA)或使用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). sha2user
的此连接尝试,服务器确定caching_sha2_password
是适当的身份验证插件并调用它(因为这是在CREATE USER
时指定的插件)。The plugin finds that the connection is not encrypted and thus requires the password to be transmitted using RSA encryption. 插件发现连接未加密,因此要求使用RSA加密传输密码。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 要从服务器请求RSA公钥,请指定--get-server-public-key
option:--get-server-public-key
选项:
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.在这种情况下,服务器将RSA公钥发送给客户端,客户端使用它来加密密码,并将结果返回给服务器。该插件使用服务器端的RSA私钥解密密码,并根据密码是否正确接受或拒绝连接。
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 或者,如果客户端有一个文件包含服务器所需的RSA公钥的本地副本,则可以使用--server-public-key-path
option:--server-public-key-path
选项指定该文件:
shell>mysql --ssl-mode=DISABLED -u sha2user -p --server-public-key-path=
Enter password:file_name
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.在这种情况下,客户端使用公钥对密码进行加密,并将结果返回给服务器。该插件使用服务器端的RSA私钥解密密码,并根据密码是否正确接受或拒绝连接。
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. --server-public-key-path
选项命名的文件中的公钥值应与由caching_sha2_password_public_key_path
系统变量命名的服务器端文件中的键值相同。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:客户端用户可以通过两种方式获取RSA公钥:
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.SHOW STATUS LIKE 'Caching_sha2_password_rsa_public_key'
语句,并将返回的键值保存在文件中。
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:caching_sha2_password
插件使用内存缓存来更快地验证以前连接过的客户端。条目由帐户名/密码哈希对组成。缓存的工作原理如下:
When a client connects, 当客户端连接时,caching_sha2_password
checks whether the client and password match some cache entry. If so, authentication succeeds.caching_sha2_password
会检查客户端和密码是否与某个缓存条目匹配。如果是这样,则身份验证成功。
If there is no matching cache entry, the plugin attempts to verify the client against the credentials in the 如果没有匹配的缓存条目,插件会尝试根据mysql.user
system table. mysql.user
系统表中的凭据验证客户端。If this succeeds, 如果成功,caching_sha2_password
adds an entry for the client to the hash. Otherwise, authentication fails and the connection is rejected.caching_sha2_password
会将客户端的条目添加到哈希中。否则,身份验证失败,连接被拒绝。
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.mysql.user
系统表进行身份验证。当客户端随后连接时,会对缓存进行更快的身份验证。
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
:sha2_cache_cleaner
审核插件处理,该插件代表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.FLUSH PRIVILES
语句时清空缓存。
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:缓存清除操作会影响后续客户端连接的身份验证要求。对于每个用户帐户,在执行以下任何操作后,用户的第一个客户端连接必须使用安全连接(使用TCP使用TLS凭据、Unix套接字文件或共享内存)或基于RSA密钥对的密码交换:
After account creation.创建帐户后。
After a password change for the account.更改帐户密码后。
After 为帐户RENAME USER
for the account.RENAME USER
(重命名用户)后。
After FLUSH PRIVILEGES
.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.FLUSH PRIVILEGES
会清除整个缓存,并影响使用caching_sha2_password
插件的所有帐户。其他操作清除特定的缓存条目,仅影响作为操作一部分的帐户。
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.一旦用户成功通过身份验证,该帐户将被输入缓存,随后的连接不需要安全连接或RSA密钥对,直到发生影响该帐户的另一个缓存清除事件。 (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.)(当可以使用缓存时,服务器使用质询-响应机制,该机制不使用明文密码传输,也不需要安全连接。)