MySQL 5.7 provides a 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
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
mysql -u myuser -pPassword:
******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:
The client can reset the account password with
SET PASSWORD. This modifies the
row of the
mysql.user table associated with
the current account, setting the
password_expired column to
'N'. After the password has been reset, the
server restores normal access for the session, as well as for
subsequent connections that use the account.
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.
The client can use
statements. This might be necessary prior to resetting the
password; for example, if the account password uses a hashing
format that requires the
old_passwords system variable
to be set to a value different from its default.
For any operation not permitted within the session, the server
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:
mysql_options() prior to
arg = 1; result = mysql_options(mysql, MYSQL_OPT_CAN_HANDLE_EXPIRED_PASSWORDS, &arg);
CLIENT_CAN_HANDLE_EXPIRED_PASSWORDS flag to
mysql = mysql_real_connect(mysql, host, user, password, "test", port, unix_socket, CLIENT_CAN_HANDLE_EXPIRED_PASSWORDS);
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
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.