MySQL provides password-expiration capability, which enables database administrators to require that users reset their password. Passwords can be expired manually, and on the basis of a policy for automatic expiration (see Section 5.7, “Password Management”).
For each connection that uses an account with an expired password, the server either disconnects the client or restricts the client to “sandbox mode,” in which the server permits to the client only those operations necessary to reset the expired password. Which action is taken by the server depends on both client and server settings, as discussed later.
If the server disconnects the client, it returns an
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 restricts the client to sandbox mode, these operations are permitted within the client session:
The client can reset the account password with
SET PASSWORD. 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
SETstatements, which is useful before MySQL 5.7.6 if
SET PASSWORDmust be used instead of
ALTER USERand the account password has a hashing format that requires
old_passwordsto be set to a value different from its default.
For any operation not permitted within the session, the server
mysql> USE performance_schema; ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement. mysql> SELECT 1; ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
That is what normally happens for interactive invocations of the mysql client because by default such invocations are put in sandbox mode. To clear the error and resume normal functioning, select a new password.
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
noninteractive mysql invocations 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 restricts it to sandbox mode depends on a combination of client and server settings. The following discussion describes the relevant settings and how they interact. The discussion applies only for accounts with expired passwords. If a client connects using a nonexpired password, the server handles the client normally.
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 connecting:
arg = 1; result = mysql_options(mysql, MYSQL_OPT_CAN_HANDLE_EXPIRED_PASSWORDS, &arg);
The mysql client enables
MYSQL_OPT_CAN_HANDLE_EXPIRED_PASSWORDSif invoked interactively or the
--connect-expired-passwordoption is given.
mysql_real_connect()at connection time:
mysql = mysql_real_connect(mysql, host, user, password, db, port, unix_socket, CLIENT_CAN_HANDLE_EXPIRED_PASSWORDS);
Other MySQL Connectors have their own conventions for indicating readiness to handle sandbox mode. See the documentation for the Connector in which you are interested.
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