MySQL 5.6 introduces password-expiration capability, to enable database administrators to expire account passwords and require users to reset their password. The immediately following discussion describes how password expiration works currently. Later, the development of this capability is detailed as it occurred over several versions, as background to help you understand what features are available when. However, to ensure that you can take advantage of all features and fixes, you should use the most recent available version of MySQL 5.6 if possible.

How Password Expiration Works

To expire an account password, use the ALTER USER statement. For example:

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

This statement modifies the row of the mysql.user table associated with the named account, setting the password_expired column to 'Y'. This does not affect any current connections the account has open. For each subsequent connection that uses the account, the server either disconnects the client or handles the client in "sandbox mode," in which the server permits the client only those operations necessary to reset the expired password. (The action taken by the server depends on both client and server settings.)

If the server disconnects the client, it returns an ER_MUST_CHANGE_PASSWORD_LOGIN error:

shell> mysql -u myuser
        -pPassword: ******ERROR 1862 (HY000): Your password has expired. To log in you mustchange it using a client that supports expired passwords.

If the server puts the client in sandbox mode, these operations are permitted within the client session:

For any operation not permitted within the session, the server returns an ER_MUST_CHANGE_PASSWORD error:

mysql> USE test;ERROR 1820 (HY000): You must SET PASSWORD before executing this statement

As mentioned previously, whether the server disconnects an expired-password client or puts it in sandbox mode depends on a combination of client and server settings. The following discussion describes the relevant settings and how they interact.

On the client side, a given client indicates whether it can handle sandbox mode for expired passwords. For clients that use the C client library, there are two ways to do this:

Other MySQL Connectors have their own conventions for indicating readiness to handle sandbox mode. See the relevant Connector documentation.

On the server side, if a client indicates that it can handle expired passwords, the server puts it in sandbox mode.

If a client does not indicate that it can handle expired passwords (or uses an older version of the client library that cannot so indicate), the server action depends on the value of the disconnect_on_expired_passwords system variable:

The preceding client and server settings apply only for accounts with expired passwords. If a client connects using a nonexpired password, the server handles the client normally.

Development of Password-Expiration Capability

The following timeline describes the versions in which various password-expiration features were added.

MySQL 5.6.6: Initial implementation of password expiration

The password_expired column is introduced in the mysql.user table to enable DBAs to expire account passwords. The column default value is 'N' (not expired).

The ALTER USER statement is introduced as the SQL interface for setting the password_expired column to 'Y'.

Connections that use an account with an expired password enter "sandbox mode" that permits only SET PASSWORD statements. For other statements, the server returns an ER_MUST_CHANGE_PASSWORD error. The intent is to force the client to reset the password before the server permits any other operations. SET PASSWORD resets the account password and sets password_expired to 'N'.

A bug in the initial implementation is that ALTER USER sets the Password column in the mysql.user table to the empty string. The implication is that users should wait until MySQL 5.6.7 to use this statement.

MySQL 5.6.7:

ALTER USER is fixed to not set the Password column to the empty string.

MySQL 5.6.8:

ALTER USER can be used as a prepared statement.

mysqladmin password is made capable of setting passwords for accounts with expired native or old-native passwords.

Sandbox mode is changed to permit clients to execute SET statements in addition to SET PASSWORD Prohibiting SET prevented clients that needed to set old_passwords from resetting their password. It also broke some Connectors, which use SET extensively at connect time to initialize the session environment.

MySQL 5.6.9:

Sandbox mode is changed to permit SET PASSWORD only if the account named in the statement matches the account the client authenticated as.

MySQL 5.6.10:

Sandbox mode is changed to permit better control over how the server handles client connections for accounts with expired passwords, and to permit clients to signal whether they are capable of handling expired passwords:

Concurrent with these changes to sandbox mode in MySQL Server and the C API client library, work begins to modify Connectors for conformance to the changes.