某些身份验证插件将账户凭据存储在 MySQL 内部的 mysql.user 系统表中：Some authentication plugins store account credentials internally to MySQL, in the mysql.user system table:

本节中的大部分讨论适用于此类认证插件，因为这里描述的大多数密码管理功能都是基于 MySQL 自身处理的内部凭据存储。其他认证插件则将账户凭据存储在 MySQL 外部。对于使用外部凭据系统进行认证的插件账户，密码管理也必须在该系统外部进行。Most discussion in this section applies to such authentication plugins because most password-management capabilities described here are based on internal credentials storage handled by MySQL itself. Other authentication plugins store account credentials externally to MySQL. For accounts that use plugins that perform authentication against an external credentials system, password management must be handled externally against that system as well.

例外情况是，针对失败登录跟踪和临时账户锁定的选项适用于所有账户，而不仅仅是使用内部凭据存储的账户，因为无论账户使用内部还是外部凭据存储，MySQL 都能够评估任何账户的登录尝试状态。The exception is that the options for failed-login tracking and temporary account locking apply to all accounts, not just accounts that use internal credentials storage, because MySQL is able to assess the status of login attempts for any account no matter whether it uses internal or external credentials storage.

有关各个身份验证插件的信息，请参见 第 8.4.1 节，“身份验证插件”。For information about individual authentication plugins, see Section 8.4.1, “Authentication Plugins”.

MySQL 允许数据库管理员手动让账户密码过期，并建立自动密码过期的策略。过期策略可以全局设定，单个账户可以选择遵循全局策略，或通过特定的每账户行为覆盖全局策略。MySQL enables database administrators to expire account passwords manually, and to establish a policy for automatic password expiration. Expiration policy can be established globally, and individual accounts can be set to either defer to the global policy or override the global policy with specific per-account behavior.

要手动使账户密码过期，请使用 ALTER USER 语句：To expire an account password manually, use the ALTER USER statement:

ALTER USER 'jeffrey'@'localhost' PASSWORD EXPIRE;

此操作会在 mysql.user 系统表中的相应行标记密码为已过期。This operation marks the password expired in the corresponding row in the mysql.user system table.

根据策略的密码过期是自动进行的，并且基于密码的年龄，对于某个账户来说，其年龄是从最近一次密码更改的日期和时间开始计算的。mysql.user 系统表指示每个账户密码的最后更改时间，如果密码的年龄超过其允许的使用期，服务器会在客户端连接时自动将密码视为已过期。这在不需显式手动过期密码的情况下也能生效。Password expiration according to policy is automatic and is based on password age, which for a given account is assessed from the date and time of its most recent password change. The mysql.user system table indicates for each account when its password was last changed, and the server automatically treats the password as expired at client connection time if its age is greater than its permitted lifetime. This works with no explicit manual password expiration.

要在全局范围内建立自动密码过期策略，请使用 default_password_lifetime 系统变量。其默认值为 0，这会禁用自动密码过期。如果 default_password_lifetime 的值为正整数 N，则表示允许的密码使用期限，即密码必须每 N 天更改一次。To establish automatic password-expiration policy globally, use the default_password_lifetime system variable. Its default value is 0, which disables automatic password expiration. If the value of default_password_lifetime is a positive integer N, it indicates the permitted password lifetime, such that passwords must be changed every N days.

示例：Examples:

The global password-expiration policy applies to all accounts that have not been set to override it. To establish policy for individual accounts, use the PASSWORD EXPIRE option of the CREATE USER以及ALTER USER statements. See Section 15.7.1.3, “CREATE USER Statement”，以及Section 15.7.1.1, “ALTER USER Statement”.The global password-expiration policy applies to all accounts that have not been set to override it. To establish policy for individual accounts, use the PASSWORD EXPIRE option of the CREATE USER and ALTER USER statements. See Section 15.7.1.3, “CREATE USER Statement”, and Section 15.7.1.1, “ALTER USER Statement”.

账户特定示例说明：Example account-specific statements:

当客户端成功连接时，服务器会确定账户密码是否已过期：When a client successfully connects, the server determines whether the account password has expired:

如果密码已过期（无论是手动还是自动），服务器要么断开客户端连接，要么限制其允许执行的操作（参见 第 8.2.16 节，“服务器处理过期密码”）。受限客户端执行的操作会导致错误，直到用户设置新的帐户密码：If the password is expired (whether manually or automatically), the server either disconnects the client or restricts the operations permitted to it (see Section 8.2.16, “Server Handling of Expired Passwords”). Operations performed by a restricted client result in an error until the user establishes a new account password:

mysql> SELECT 1;
ERROR 1820 (HY000): You must reset your password using ALTER USER
statement before executing this statement.

mysql> ALTER USER USER() IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT 1;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

在客户端重置密码后，服务器会恢复该会话的正常访问，以及使用该账户的后续连接。管理用户也可以重置账户密码，但该账户的任何已存在的受限会话仍然保持受限。使用该账户的客户端必须断开连接并重新连接，然后才能成功执行语句。After the client resets the password, the server restores normal access for the session, as well as for subsequent connections that use the account. It is also possible for an administrative user to reset the account password, but any existing restricted sessions for that account remain restricted. A client using the account must disconnect and reconnect before statements can be executed successfully.

MySQL 允许对以前使用过的密码设置重复使用的限制。重复使用限制可以基于密码更改次数、经过的时间或两者同时设定。重复使用策略可以在全局范围内设定，单个账户可以选择遵循全局策略，或者通过特定的每账户行为覆盖全局策略。MySQL enables restrictions to be placed on reuse of previous passwords. Reuse restrictions can be established based on number of password changes, time elapsed, or both. Reuse policy can be established globally, and individual accounts can be set to either defer to the global policy or override the global policy with specific per-account behavior.

账户的密码历史由其过去被分配的密码组成。MySQL 可以限制新密码不能从这些历史密码中选择：The password history for an account consists of passwords it has been assigned in the past. MySQL can restrict new passwords from being chosen from this history:

要在全局范围内建立密码重用策略，请使用 password_history 和 password_reuse_interval 系统变量。To establish password-reuse policy globally, use the password_history and password_reuse_interval system variables.

示例：Examples:

全球密码重用策略适用于所有未设置为覆盖此策略的账户。要为单个账户建立策略，请使用 PASSWORD HISTORY 和 PASSWORD REUSE INTERVAL 选项，这些选项可在 CREATE USER 和 ALTER USER 语句中使用。请参阅 第 15.7.1.3 节，“CREATE USER 语句” 和 第 15.7.1.1 节，“ALTER USER 语句”。The global password-reuse policy applies to all accounts that have not been set to override it. To establish policy for individual accounts, use the PASSWORD HISTORY and PASSWORD REUSE INTERVAL options of the CREATE USER and ALTER USER statements. See Section 15.7.1.3, “CREATE USER Statement”, and Section 15.7.1.1, “ALTER USER Statement”.

账户特定示例说明：Example account-specific statements:

可以要求在尝试更改账户密码时，必须通过指定当前要被替换的密码来进行验证。这使得数据库管理员可以防止用户在未证明知道当前密码的情况下更改密码。否则，这类更改可能会发生，例如，当某个用户暂时离开终端会话而没有注销时，恶意用户可能会利用该会话更改原用户的 MySQL 密码。这可能导致不良后果：It is possible to require that attempts to change an account password be verified by specifying the current password to be replaced. This enables DBAs to prevent users from changing a password without proving that they know the current password. Such changes could otherwise occur, for example, if one user walks away from a terminal session temporarily without logging out, and a malicious user uses the session to change the original user's MySQL password. This can have unfortunate consequences:

可以在全局范围内建立密码验证策略，并且可以为各个账户设置以遵循全局策略或通过特定的每账户行为覆盖全局策略。Password-verification policy can be established globally, and individual accounts can be set to either defer to the global policy or override the global policy with specific per-account behavior.

对于每个账户，其 mysql.user 行指示是否存在需要在尝试更改密码时验证当前密码的账户特定设置。该设置由 PASSWORD REQUIRE 和 ALTER USER 语句中的 CREATE USER 选项建立：For each account, its mysql.user row indicates whether there is an account-specific setting requiring verification of the current password for password change attempts. The setting is established by the PASSWORD REQUIRE option of the CREATE USER and ALTER USER statements:

换句话说，如果账户设置不是 PASSWORD REQUIRE CURRENT DEFAULT，则账户设置优先于由 password_require_current 系统变量建立的全局策略。否则，账户会遵循 password_require_current 设置。In other words, if the account setting is not PASSWORD REQUIRE CURRENT DEFAULT, the account setting takes precedence over the global policy established by the password_require_current system variable. Otherwise, the account defers to the password_require_current setting.

默认情况下，密码验证是可选的：password_require_current 被禁用，并且创建时未使用 PASSWORD REQUIRE 选项的账户默认设置为 PASSWORD REQUIRE CURRENT DEFAULT。By default, password verification is optional: password_require_current is disabled and accounts created with no PASSWORD REQUIRE option default to PASSWORD REQUIRE CURRENT DEFAULT.

下表显示了每个账户的设置如何与 password_require_current 系统变量值交互，以确定账户密码验证所需的策略。The following table shows how per-account settings interact with password_require_current system variable values to determine account password verification-required policy.

要在全局范围内建立密码验证策略，请使用 password_require_current 系统变量。其默认值为 OFF，因此在更改账户密码时不需要指定当前密码。To establish password-verification policy globally, use the password_require_current system variable. Its default value is OFF, so it is not required that account password changes specify the current password.

示例：Examples:

全局密码验证必需策略适用于所有未设置覆盖该策略的账户。要为单个账户建立策略，请使用 PASSWORD REQUIRE 和 ALTER USER 语句中的 CREATE USER 选项。参见 第 15.7.1.3 节，“CREATE USER 语句” 和 第 15.7.1.1 节，“ALTER USER 语句”。The global password verification-required policy applies to all accounts that have not been set to override it. To establish policy for individual accounts, use the PASSWORD REQUIRE options of the CREATE USER and ALTER USER statements. See Section 15.7.1.3, “CREATE USER Statement”, and Section 15.7.1.1, “ALTER USER Statement”.

账户特定示例说明：Example account-specific statements:

当用户使用ALTER USER或SET PASSWORD语句更改密码时，会涉及当前密码的验证。示例中使用ALTER USER语句，它比SET PASSWORD更为推荐，但此处描述的原则对两种语句都是相同的。Verification of the current password comes into play when a user changes a password using the ALTER USER or SET PASSWORD statement. The examples use ALTER USER, which is preferred over SET PASSWORD, but the principles described here are the same for both statements.

在更改密码的语句中，REPLACE子句指定要被替换的当前密码。示例：In password-change statements, a REPLACE clause specifies the current password to be replaced. Examples:

REPLACE 子句的工作原理如下：The REPLACE clause works like this:

用户账户允许拥有双重密码，分别指定为主密码和次密码。双重密码功能使在以下情况下无缝执行凭证更改成为可能：User accounts are permitted to have dual passwords, designated as primary and secondary passwords. Dual-password capability makes it possible to seamlessly perform credential changes in scenarios like this:

考虑在前述类型的场景中，当一个账户仅允许单个密码时，凭证更改必须如何执行。在这种情况下，账户密码更改的时间及其在所有服务器上的传播与所有使用该账户的应用程序更新为新密码之间必须紧密配合。此过程可能涉及服务器或应用程序不可用的停机时间。Consider how a credential change must be performed in the preceding type of scenario when an account is permitted only a single password. In this case, there must be close cooperation in the timing of when the account password change is made and propagated throughout all servers, and when all applications that use the account are updated to use the new password. This process may involve downtime during which servers or applications are unavailable.

使用双重密码，可以更轻松地分阶段更改凭证，无需紧密协作，也无需停机：With dual passwords, credential changes can be made more easily, in phases, without requiring close cooperation, and without downtime:

MySQL 使用语法实现双密码功能，可保存和丢弃次要密码：MySQL implements dual-password capability with syntax that saves and discards secondary passwords:

假设在前面描述的凭据更改场景中，一个名为'appuser1'@'host1.example.com'的账户被应用程序用来连接服务器，并且该账户的密码要从'password_a'更改为'password_b'。Suppose that, for the previously described credential-change scenario, an account named 'appuser1'@'host1.example.com' is used by applications to connect to servers, and that the account password is to be changed from 'password_a' to 'password_b'.

要执行此凭据更改，请使用ALTER USER如下：To perform this change of credentials, use ALTER USER as follows:

RETAIN CURRENT PASSWORD 和 DISCARD OLD PASSWORD 子句具有以下效果：The RETAIN CURRENT PASSWORD and DISCARD OLD PASSWORD clauses have the following effects:

修改二级密码的语句需要这些权限：Statements that modify secondary passwords require these privileges:

CREATE USER、ALTER USER 和 SET PASSWORD 语句可以为用户账户生成随机密码，作为替代要求管理员显式指定字面密码的方法。有关语法的详细信息，请参阅每个语句的说明。本节描述了生成的随机密码的共同特性。The CREATE USER, ALTER USER, and SET PASSWORD statements have the capability of generating random passwords for user accounts, as an alternative to requiring explicit administrator-specified literal passwords. See the description of each statement for details about the syntax. This section describes the characteristics common to generated random passwords.

默认情况下，生成的随机密码长度为 20 个字符。此长度由 generated_random_password_length 系统变量控制，其范围为 5 到 255。By default, generated random passwords have a length of 20 characters. This length is controlled by the generated_random_password_length system variable, which has a range from 5 to 255.

对于每个通过语句生成随机密码的账户，语句会将密码存储在 mysql.user 系统表中，并根据账户认证插件适当进行哈希处理。该语句还会在结果集的一行中返回明文密码，以便执行语句的用户或应用程序使用。结果集的列名为 user、host、generated password 和 auth_factor，分别表示用于标识 mysql.user 系统表中受影响行的用户名和主机名、生成的明文密码，以及显示的密码值适用的认证因子。For each account for which a statement generates a random password, the statement stores the password in the mysql.user system table, hashed appropriately for the account authentication plugin. The statement also returns the cleartext password in a row of a result set to make it available to the user or application executing the statement. The result set columns are named user, host, generated password, and auth_factor indicating the user name and host name values that identify the affected row in the mysql.user system table, the cleartext generated password, and the authentication factor the displayed password value applies to.

mysql> CREATE USER
       'u1'@'localhost' IDENTIFIED BY RANDOM PASSWORD,
       'u2'@'%.example.com' IDENTIFIED BY RANDOM PASSWORD,
       'u3'@'%.org' IDENTIFIED BY RANDOM PASSWORD;
+------+---------------+----------------------+-------------+
| user | host          | generated password   | auth_factor |
+------+---------------+----------------------+-------------+
| u1   | localhost     | iOeqf>Mh9:;XD&qn(Hl} |           1 |
| u2   | %.example.com | sXTSAEvw3St-R+_-C3Vb |           1 |
| u3   | %.org         | nEVe%Ctw/U/*Md)Exc7& |           1 |
+------+---------------+----------------------+-------------+
mysql> ALTER USER
       'u1'@'localhost' IDENTIFIED BY RANDOM PASSWORD,
       'u2'@'%.example.com' IDENTIFIED BY RANDOM PASSWORD;
+------+---------------+----------------------+-------------+
| user | host          | generated password   | auth_factor |
+------+---------------+----------------------+-------------+
| u1   | localhost     | Seiei:&cw}8]@3OA64vh |           1 |
| u2   | %.example.com | j@&diTX80l8}(NiHXSae |           1 |
+------+---------------+----------------------+-------------+
mysql> SET PASSWORD FOR 'u3'@'%.org' TO RANDOM;
+------+-------+----------------------+-------------+
| user | host  | generated password   | auth_factor |
+------+-------+----------------------+-------------+
| u3   | %.org | n&cz2xF;P3!U)+]Vw52H |           1 |
+------+-------+----------------------+-------------+

生成账户随机密码的 CREATE USER、ALTER USER 或 SET PASSWORD 语句在二进制日志中被写成 CREATE USER 或 ALTER USER 语句，并带有 IDENTIFIED WITH auth_plugin AS 'auth_string' 子句，其中 auth_plugin 是账户的认证插件，'auth_string' 是账户的哈希密码值。A CREATE USER, ALTER USER, or SET PASSWORD statement that generates a random password for an account is written to the binary log as a CREATE USER or ALTER USER statement with an IDENTIFIED WITH auth_plugin AS 'auth_string', clause, where auth_plugin is the account authentication plugin and 'auth_string' is the account hashed password value.

如果安装了validate_password组件，它所实施的策略对生成的密码没有影响。（密码验证的目的是帮助人类创建更好的密码。）If the validate_password component is installed, the policy that it implements has no effect on generated passwords. (The purpose of password validation is to help humans create better passwords.)

管理员可以配置用户账户，使得连续过多的登录失败会导致账户暂时锁定。Administrators can configure user accounts such that too many consecutive login failures cause temporary account locking.

“登录失败” 在此上下文中指客户端在尝试连接时未能提供正确密码。这不包括由于用户不存在或网络问题等原因导致的连接失败。对于拥有双重密码的账户（请参阅 双重密码支持），任一账户密码均视为正确。“Login failure” in this context means failure of the client to provide a correct password during a connection attempt. It does not include failure to connect for reasons such as unknown user or network issues. For accounts that have dual passwords (see Dual Password Support), either account password counts as correct.

每个账户所需的登录失败次数及锁定时间可以配置，使用 CREATE USER 和 ALTER USER 语句的 FAILED_LOGIN_ATTEMPTS 和 PASSWORD_LOCK_TIME 选项。示例：The required number of login failures and the lock time are configurable per account, using the FAILED_LOGIN_ATTEMPTS and PASSWORD_LOCK_TIME options of the CREATE USER and ALTER USER statements. Examples:

CREATE USER 'u1'@'localhost' IDENTIFIED BY 'password'
  FAILED_LOGIN_ATTEMPTS 3 PASSWORD_LOCK_TIME 3;

ALTER USER 'u2'@'localhost'
  FAILED_LOGIN_ATTEMPTS 4 PASSWORD_LOCK_TIME UNBOUNDED;

当连续登录失败次数过多时，客户端会收到如下错误：When too many consecutive login failures occur, the client receives an error that looks like this:

ERROR 3957 (HY000): Access denied for user user.
Account is blocked for D day(s) (R day(s) remaining)
due to N consecutive failed logins.

按如下方式使用选项：Use the options as follows:

每个选项允许的 N 值范围是 0 到 32767。值为 0 会禁用此选项。Permitted values of N for each option are in the range from 0 to 32767. A value of 0 disables the option.

失败登录跟踪和临时账户锁定具有以下特点：Failed-login tracking and temporary account locking have these characteristics:

当服务器读取授权表时，会为每个账户初始化状态信息，包括是否启用失败登录跟踪、账户当前是否处于临时锁定状态以及如果锁定，锁定开始时间，以及如果账户未锁定，则临时锁定前的失败次数。When the server reads the grant tables, it initializes state information for each account regarding whether failed-login tracking is enabled, whether the account is currently temporarily locked and when locking began if so, and the number of failures before temporary locking occurs if the account is not locked.

账户的状态信息可以重置，这意味着失败登录计数被重置，并且如果账户当前处于临时锁定状态，将被解锁。账户重置可以是针对所有账户的全局重置，也可以是针对单个账户的重置：An account's state information can be reset, which means that failed-login counting is reset, and the account is unlocked if currently temporarily locked. Account resets can be global for all accounts or per account:

失败登录的跟踪与用于检查凭据的登录账户相关。如果使用了用户代理，跟踪会针对代理用户，而不是被代理用户。也就是说，跟踪与由USER()指示的账户相关，而不是由CURRENT_USER()指示的账户相关。有关代理用户和被代理用户之间区别的信息，请参见第 8.2.19 节，“代理用户”。Failed-login tracking is tied to the login account that is used to check credentials. If user proxying is in use, tracking occurs for the proxy user, not the proxied user. That is, tracking is tied to the account indicated by USER(), not the account indicated by CURRENT_USER(). For information about the distinction between proxy and proxied users, see Section 8.2.19, “Proxy Users”.