Documentation Home
MySQL 8.0 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 38.5Mb
PDF (A4) - 38.6Mb
PDF (RPM) - 33.3Mb
HTML Download (TGZ) - 8.1Mb
HTML Download (Zip) - 8.2Mb
HTML Download (RPM) - 7.0Mb
Man Pages (TGZ) - 134.2Kb
Man Pages (Zip) - 190.2Kb
Info (Gzip) - 3.4Mb
Info (Zip) - 3.4Mb
Excerpts from this Manual

MySQL 8.0 Reference Manual  /  ...  /  Password Management

6.3.8 Password Management

MySQL supports these password-management capabilities:

  • Password expiration, to require passwords to be changed periodically.

  • Password reuse restrictions, to prevent old passwords from being chosen again.

  • Password verification, to require that password changes also specify the current password to be replaced.

  • Password strength assessment, to require strong passwords.

The following sections these capabilities, except password strength assessment, which is implemented using the validate_password plugin and is described in Section 6.5.3, “The Password Validation Component”.

Important

MySQL implements password-management capabilities using tables in the mysql system database. If you upgrade MySQL from an earlier version, your system tables might not be up to date. In that case, the server writes messages similar to these to the error log during the startup process:

[ERROR] Column count of mysql.user is wrong. Expected
49, found 47. The table is probably corrupted
[Warning] ACL table mysql.password_history missing.
Some operations may fail.

To correct the issue, run mysql_upgrade and restart the server. Until this is done, password changes are not possible.

Note

The password-management capabilities described here apply only to accounts that store credentials internally in the mysql.user system table (mysql_native_password, sha256_password, or caching_sha2_password). For accounts that use plugins that perform authentication against an external credential system, password management must be handled externally against that system as well.

Password Expiration Policy

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.

To expire an account password manually, use the ALTER USER statement:

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

This operation marks the password expired in the corresponding mysql.user table row.

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

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:

  • To establish a global policy that passwords have a lifetime of approximately six months, start the server with these lines in a server my.cnf file:

    [mysqld]
    default_password_lifetime=180
  • To establish a global policy such that passwords never expire, set default_password_lifetime to 0:

    [mysqld]
    default_password_lifetime=0
  • default_password_lifetime can also be set and persisted at runtime:

    SET PERSIST default_password_lifetime = 180;
    SET PERSIST default_password_lifetime = 0;

    SET PERSIST sets the value for the running MySQL instance. It also saves the value to be used for subsequent server restarts; see Section 13.7.5.1, “SET Syntax for Variable Assignment”. To change a value for the running MySQL instance without saving it for subsequent restarts, use the GLOBAL keyword rather than PERSIST.

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 13.7.1.3, “CREATE USER Syntax”, and Section 13.7.1.1, “ALTER USER Syntax”.

Example account-specific statements:

  • Require the password to be changed every 90 days:

    CREATE USER 'jeffrey'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;
    ALTER USER 'jeffrey'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;

    This expiration option overrides the global policy for all accounts named by the statement.

  • Disable password expiration:

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

    This expiration option overrides the global policy for all accounts named by the statement.

  • Defer to the global expiration policy for all accounts named by the statement:

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

When a client successfully connects, the server determines whether the account password has expired:

  • The server checks whether the password has been manually expired.

  • Otherwise, the server checks whether the password age is greater than its permitted lifetime according to the automatic password expiration policy. If so, the server considers the password expired.

If the password is expired (whether manually or automatically), the server either disconnects the client or restricts the operations permitted to it (see Section 6.3.9, “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.

Note

It is possible to reset a password by setting it to its current value. As a matter of good policy, it is preferable to choose a different password. DBAs can enforce non-reuse by establishing an appropriate password-reuse policy. See Password Reuse Policy.

Password Reuse Policy

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.

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:

  • If an account is restricted on the basis of number of password changes, a new password cannot be chosen from a specified number of the most recent passwords. For example, if the minimum number of password changes is set to 3, a new password cannot be the same as any of the most recent 3 passwords.

  • If an account is restricted based on time elapsed, a new password cannot be chosen from passwords in the history that are newer than a specified number of days. For example, if the password reuse interval is set to 60, a new password must not be among those previously chosen within the last 60 days.

Note

The empty password does not count in the password history and is subject to reuse at any time.

To establish password-reuse policy globally, use the password_history and password_reuse_interval system variables.

Examples:

  • To prohibit reusing any of the last 6 passwords or passwords newer than 365 days, put these lines in the server my.cnf file:

    [mysqld]
    password_history=6
    password_reuse_interval=365
  • To set and persist the variables at runtime, use statements like this:

    SET PERSIST password_history = 6;
    SET PERSIST password_reuse_interval = 365;

    SET PERSIST sets the value for the running MySQL instance. It also saves the value to be used for subsequent server restarts; see Section 13.7.5.1, “SET Syntax for Variable Assignment”. To change a value for the running MySQL instance without saving it for subsequent restarts, use the GLOBAL keyword rather than PERSIST.

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 13.7.1.3, “CREATE USER Syntax”, and Section 13.7.1.1, “ALTER USER Syntax”.

Example account-specific statements:

  • Require a minimum of 5 password changes before permitting reuse:

    CREATE USER 'jeffrey'@'localhost' PASSWORD HISTORY 5;
    ALTER USER 'jeffrey'@'localhost' PASSWORD HISTORY 5;

    This history-length option overrides the global policy for all accounts named by the statement.

  • Require a minimum of 365 days elapsed before permitting reuse:

    CREATE USER 'jeffrey'@'localhost' PASSWORD REUSE INTERVAL 365 DAY;
    ALTER USER 'jeffrey'@'localhost' PASSWORD REUSE INTERVAL 365 DAY;

    This time-elapsed option overrides the global policy for all accounts named by the statement.

  • To combine both types of reuse restrictions, use PASSWORD HISTORY and PASSWORD REUSE INTERVAL together:

    CREATE USER 'jeffrey'@'localhost'
      PASSWORD HISTORY 5
      PASSWORD REUSE INTERVAL 365 DAY;
    ALTER USER 'jeffrey'@'localhost'
      PASSWORD HISTORY 5
      PASSWORD REUSE INTERVAL 365 DAY;

    These options override both global policy reuse restrictions for all accounts named by the statement.

  • Defer to the global policy for both types of reuse restrictions:

    CREATE USER 'jeffrey'@'localhost'
      PASSWORD HISTORY DEFAULT
      PASSWORD REUSE INTERVAL DEFAULT;
    ALTER USER 'jeffrey'@'localhost'
      PASSWORD HISTORY DEFAULT
      PASSWORD REUSE INTERVAL DEFAULT;

Password Verification-Required Policy

As of MySQL 8.0.13, 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:

  • The original user becomes unable to access MySQL until the account password is reset by an administrator.

  • Until the password reset occurs, the malicious user can access MySQL with the benign user's changed credentials.

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.

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:

  • If the account setting is PASSWORD REQUIRE CURRENT, password changes must specify the current password.

  • If the account setting is PASSWORD REQUIRE CURRENT OPTIONAL, password changes may but need not specify the current password.

  • If the account setting is PASSWORD REQUIRE CURRENT DEFAULT, the password_require_current system variable determines the verification-required policy for the account:

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.

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.

The following table shows how per-account settings interact with password_require_current system variable values to determine account password verification-required policy.

Table 6.10 Password-Verification Policy

Per-Account Setting password_require_current System Variable Password Changes Require Current Password?
PASSWORD REQUIRE CURRENT OFF Yes
PASSWORD REQUIRE CURRENT ON Yes
PASSWORD REQUIRE CURRENT OPTIONAL OFF No
PASSWORD REQUIRE CURRENT OPTIONAL ON No
PASSWORD REQUIRE CURRENT DEFAULT OFF No
PASSWORD REQUIRE CURRENT DEFAULT ON Yes

Note

Privileged users can change any account password without specifying the current password, regardless of the verification-required policy. A privileged user is one who has the global CREATE USER privilege or the UPDATE privilege for the mysql system database.

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:

  • To establish a global policy that password changes must specify the current password, start the server with these lines in a server my.cnf file:

    [mysqld]
    password_require_current=ON
  • To set and persist password_require_current at runtime, use a statement such as one of these:

    SET PERSIST password_require_current = ON;
    SET PERSIST password_require_current = OFF;

    SET PERSIST sets the value for the running MySQL instance. It also saves the value to be used for subsequent server restarts; see Section 13.7.5.1, “SET Syntax for Variable Assignment”. To change a value for the running MySQL instance without saving it for subsequent restarts, use the GLOBAL keyword rather than PERSIST.

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 13.7.1.3, “CREATE USER Syntax”, and Section 13.7.1.1, “ALTER USER Syntax”.

Example account-specific statements:

  • Require that password changes specify the current password:

    CREATE USER 'jeffrey'@'localhost' PASSWORD REQUIRE CURRENT;
    ALTER USER 'jeffrey'@'localhost' PASSWORD REQUIRE CURRENT;

    This verification option overrides the global policy for all accounts named by the statement.

  • Do not require that password changes specify the current password (the current password may but need not be given):

    CREATE USER 'jeffrey'@'localhost' PASSWORD REQUIRE CURRENT OPTIONAL;
    ALTER USER 'jeffrey'@'localhost' PASSWORD REQUIRE CURRENT OPTIONAL;

    This verification option overrides the global policy for all accounts named by the statement.

  • Defer to the global password verification-required policy for all accounts named by the statement:

    CREATE USER 'jeffrey'@'localhost' PASSWORD REQUIRE CURRENT DEFAULT;
    ALTER USER 'jeffrey'@'localhost' PASSWORD REQUIRE CURRENT DEFAULT;

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.

In password-change statements, a REPLACE clause specifies the current password to be replaced. Examples:

  • Change the current user's password:

    ALTER USER USER() IDENTIFIED BY 'auth_string' REPLACE 'current_auth_string';
  • Change a named user's password:

    ALTER USER 'jeffrey'@'localhost'
      IDENTIFIED BY 'auth_string'
      REPLACE 'current_auth_string';
  • Change a named user's authentication plugin and password:

    ALTER USER 'jeffrey'@'localhost'
      IDENTIFIED WITH caching_sha2_password BY 'auth_string'
      REPLACE 'current_auth_string';

The REPLACE clause works like this:

  • REPLACE must be given if password changes for the account are required to specify the current password, as verification that the user attempting to make the change actually knows the current password.

  • REPLACE is optional if password changes for the account may but need not specify the current password.

  • If REPLACE is specified, it must specify the correct current password, or an error occurs. This is true even if REPLACE is optional.

  • REPLACE can be specified only when changing the account password for the current user. (This means that in the examples just shown, the statements that explicitly name the account for jeffrey fail unless the current user is jeffrey.) This is true even if the change is attempted for another user by a privileged user; however, such a user can change any password without specifying REPLACE.

  • REPLACE is omitted from the binary log to avoid writing cleartext passwords to it.


User Comments
User comments in this section are, as the name implies, provided by MySQL users. The MySQL documentation team is not responsible for, nor do they endorse, any of the information provided here.
Sign Up Login You must be logged in to post a comment.