MySQL provides password-expiration capability to enable database administrators to expire account passwords and require users to reset their password. This section describes 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 to 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 -p
Password: ******
ERROR 1862 (HY000): Your password has expired. To log in you must
change 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 performance_schema;
ERROR 1820 (HY000): You must SET PASSWORD before executing this statement

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

For noninteractive invocations of the mysql client (for example, in batch mode), the server normally disconnects the client if the password is expired. To permit mysql to stay connected so that the password can be changed (using the statements just described), add the --connect-expired-password option to the mysql command.

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