Documentation Home
MySQL 8.0 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 33.8Mb
PDF (A4) - 33.8Mb
PDF (RPM) - 31.8Mb
HTML Download (TGZ) - 8.1Mb
HTML Download (Zip) - 8.2Mb
HTML Download (RPM) - 7.0Mb
Man Pages (TGZ) - 145.9Kb
Man Pages (Zip) - 206.8Kb
Info (Gzip) - 3.1Mb
Info (Zip) - 3.1Mb


MySQL 8.0 Reference Manual  /  ...  /  Password Management

Pre-General Availability Draft: 2017-11-23

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 strength assessment, to require strong passwords

The following sections describe password expiration and reuse-restriction capabilities. Password strength assessment is implemented using the validate_password plugin; see Section 6.5.3, “The Password Validation Plugin”.

Important

MySQL implements password-reuse restrictions by means of columns in the mysql.user system table and a mysql.password_history system table that are new in MySQL 8.0.3. If you upgrade to 8.0.3 or higher release from an earlier version, you must incorporate these system database changes. Otherwise, the server writes these messages 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.

Password Expiration Policy

MySQL enables database administrators to expire account passwords manually, and to establish a policy for automatic password expiration. It is possible to establish expiration policy globally, as well as on a per-account basis. These capabilities apply to accounts that use a MySQL built-in authentication plugin (mysql_native_password, sha256_password, or caching_sha2_password). For accounts that use plugins that perform authentication against an external credential system, password expiration must be handled externally as well.

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 only for the running MySQL instance without saving it for subsequent restarts, use the GLOBAL keyword rather than PERSIST.

To establish password-expiration policy for individual accounts, use the PASSWORD EXPIRE 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 expiration 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;
  • Disable password expiration:

    CREATE USER 'jeffrey'@'localhost' PASSWORD EXPIRE NEVER;
    ALTER USER 'jeffrey'@'localhost' PASSWORD EXPIRE NEVER;
  • Defer to the global expiration policy:

    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. It is possible to establish reuse policy globally, as well as on a per-account basis. These capabilities apply to accounts that use a MySQL built-in authentication plugin (mysql_native_password, sha256_password, or caching_sha2_password). For accounts that use plugins that perform authentication against an external credential system, reuse restrictions must be handled externally as well.

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. To specify the variable values at server startup, define them in your server my.cnf file.

Examples:

  • To prohibit reusing any of the last 6 passwords or passwords newer than 365 days, put these lines in your 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 only for the running MySQL instance without saving it for subsequent restarts, use the GLOBAL keyword rather than PERSIST.

To establish password-reuse 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”.

Examples:

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

    CREATE USER 'jeffrey'@'localhost' PASSWORD HISTORY 5;
    ALTER USER 'jeffrey'@'localhost' PASSWORD HISTORY 5;
  • Require a minimum of 365 days elapsed before permitting reuse:

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

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

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