6.2.15 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.
Dual passwords, to enable clients to connect using either a primary or secondary password.
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.4.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 (the exact numbers may vary):

[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, perform the MySQL upgrade procedure. See Section 2.11, “Upgrading MySQL”. 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 row in the mysql.user system table.

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.

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:
1. SET PERSIST default_password_lifetime = 180;
2. 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 the 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:
1. CREATE USER 'jeffrey'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;
2. 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:
1. CREATE USER 'jeffrey'@'localhost' PASSWORD EXPIRE NEVER;
2. 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:
1. CREATE USER 'jeffrey'@'localhost' PASSWORD EXPIRE DEFAULT;
2. 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.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.

Note

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

Contents Haut

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 the 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:
1. CREATE USER 'jeffrey'@'localhost' PASSWORD HISTORY 5;
2. 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:
1. CREATE USER 'jeffrey'@'localhost' PASSWORD REUSE INTERVAL 365 DAY;
2. 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:
1. CREATE USER 'jeffrey'@'localhost'
2. PASSWORD HISTORY 5
3. PASSWORD REUSE INTERVAL 365 DAY;
4. ALTER USER 'jeffrey'@'localhost'
5. PASSWORD HISTORY 5
6. 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:
1. CREATE USER 'jeffrey'@'localhost'
2. PASSWORD HISTORY DEFAULT
3. PASSWORD REUSE INTERVAL DEFAULT;
4. ALTER USER 'jeffrey'@'localhost'
5. PASSWORD HISTORY DEFAULT
6. PASSWORD REUSE INTERVAL DEFAULT;

Contents Haut

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:
- If password_require_current is enabled, password changes must specify the current password.
- If password_require_current is disabled, password changes may but need not specify the current password.

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:
1. SET PERSIST password_require_current = ON;
2. 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 the 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:
1. CREATE USER 'jeffrey'@'localhost' PASSWORD REQUIRE CURRENT;
2. 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):
1. CREATE USER 'jeffrey'@'localhost' PASSWORD REQUIRE CURRENT OPTIONAL;
2. 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:
1. CREATE USER 'jeffrey'@'localhost' PASSWORD REQUIRE CURRENT DEFAULT;
2. 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:
1. ALTER USER USER() IDENTIFIED BY 'auth_string' REPLACE 'current_auth_string';
Change a named user's password:
1. ALTER USER 'jeffrey'@'localhost'
2. IDENTIFIED BY 'auth_string'
3. REPLACE 'current_auth_string';
Change a named user's authentication plugin and password:
1. ALTER USER 'jeffrey'@'localhost'
2. IDENTIFIED WITH caching_sha2_password BY 'auth_string'
3. 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.

Contents Haut

Dual Password Support

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

A system has a large number of MySQL servers, possibly involving replication.
Multiple applications connect to different MySQL servers.
Periodic credential changes must be made to the account or accounts used by the applications to connect to the servers.

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:

For each affected account, establish a new primary password on the servers, retaining the current password as the secondary password. This enables servers to recognize either the primary or secondary password for each account, while applications can continue to connect to the servers using the same password as previously (which is now the secondary password).
After the password change has propagated to all servers, modify applications that use any affected account to connect using the account primary password.
After all applications have been migrated from the secondary passwords to the primary passwords, the secondary passwords are no longer needed and can be discarded. After this change has propagated to all servers, only the primary password for each account can be used to connect. The credential change is now complete.

MySQL implements dual-password capability with syntax that saves and discards secondary passwords:

The RETAIN CURRENT PASSWORD clause for the ALTER USER and SET PASSWORD statements saves an account current password as its secondary password when you assign a new primary password.
The DISCARD OLD PASSWORD clause for ALTER USER discards an account secondary password, leaving only the primary password.

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

To perform this change of credentials, use ALTER USER as follows:

On each server that is not a replication slave, establish 'password_b' as the new appuser1 primary password, retaining the current password as the secondary password:
1. ALTER USER 'appuser1'@'host1.example.com'
2. IDENTIFIED BY 'password_b'
3. RETAIN CURRENT PASSWORD;
Wait for the password change to replicate throughout the system to all slave servers.
Modify each application that uses the appuser1 account so that it connects to the servers using a password of 'password_b' rather than 'password_a'.
At this point, the secondary password is no longer needed. On each server that is not a replication slave, discard the secondary password:
1. ALTER USER 'appuser1'@'host1.example.com'
2. DISCARD OLD PASSWORD;
After the discard-password change has replicated to all slave servers, the credential change is complete.

The RETAIN CURRENT PASSWORD and DISCARD OLD PASSWORD clauses have the following effects:

RETAIN CURRENT PASSWORD retains an account current password as its secondary password, replacing any existing secondary password. The new password becomes the primary password, but clients can use the account to connect to the server using either the primary or secondary password. (Exception: If the new password specified by the ALTER USER or SET PASSWORD statement is empty, the secondary password becomes empty as well, even if RETAIN CURRENT PASSWORD is given.)
If you specify RETAIN CURRENT PASSWORD for an account that has an empty primary password, the statement fails.
If an account has a secondary password and you change its primary password without specifying RETAIN CURRENT PASSWORD, the secondary password remains unchanged.
For ALTER USER, if you change the authentication plugin assigned to the account, the secondary password is discarded. If you change the authentication plugin and also specify RETAIN CURRENT PASSWORD, the statement fails.
For ALTER USER, DISCARD OLD PASSWORD discards the secondary password, if one exists. The account retains only its primary password, and clients can use the account to connect to the server only with the primary password.

Statements that modify secondary passwords require these privileges:

The APPLICATION_PASSWORD_ADMIN privilege is required to use the RETAIN CURRENT PASSWORD or DISCARD OLD PASSWORD clause for ALTER USER and SET PASSWORD statements that apply to your own account. The privilege is required to manipulate your own secondary password because most users require only one password.
If an account is to be permitted to manipulate secondary passwords for all accounts, it should be granted the CREATE USER privilege rather than APPLICATION_PASSWORD_ADMIN.

Find a PHP function

Document created the 26/06/2006, last modified the 26/10/2018
Source of the printed document:https://www.gaudry.be/en/mysql-rf-password-management.html

The infobrol is a personal site whose content is my sole responsibility. The text is available under CreativeCommons license (BY-NC-SA). More info on the terms of use and the author.

References

Manuel MySQL : https://dev.mysql.com/

These references and links indicate documents consulted during the writing of this page, or which may provide additional information, but the authors of these sources can not be held responsible for the content of this page.
The author This site is solely responsible for the way in which the various concepts, and the freedoms that are taken with the reference works, are presented here. Remember that you must cross multiple source information to reduce the risk of errors.

Contents Haut