MySQL 5.6 introduces password-expiration capability, which enables database administrators to require that users 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, use the most recent available version of MySQL if possible.
The ALTER USER
statement enables
account password expiration. For example:
ALTER USER 'myuser'@'localhost' PASSWORD EXPIRE;
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 the client to perform 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
ER_MUST_CHANGE_PASSWORD_LOGIN
error:
$> 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 that has been done, the server restores normal access for the session, as well as for subsequent connections that use the account.NoteAlthough 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.
The client can use the
SET
statement. This might be necessary prior to usingSET PASSWORD
to reset the password if the account uses an authentication plugin for which theold_passwords
system variable must first be set to a nondefault value to perform password hashing in a specific way.
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
That is what normally happens for interactive invocations of the mysql client because by default such invocations are put in sandbox mode. To 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 permitted in sandbox mode), 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.
This 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:
Pass the
MYSQL_OPT_CAN_HANDLE_EXPIRED_PASSWORDS
flag tomysql_options()
prior to connecting:my_bool arg = 1; mysql_options(mysql, MYSQL_OPT_CAN_HANDLE_EXPIRED_PASSWORDS, &arg);
This is the technique used within the mysql client, which enables
MYSQL_OPT_CAN_HANDLE_EXPIRED_PASSWORDS
if invoked interactively or with the--connect-expired-password
option.Pass the
CLIENT_CAN_HANDLE_EXPIRED_PASSWORDS
flag tomysql_real_connect()
at connect time:MYSQL mysql; mysql_init(&mysql); if (!mysql_real_connect(&mysql, host, user, password, db, port, unix_socket, CLIENT_CAN_HANDLE_EXPIRED_PASSWORDS)) { ... handle error ... }
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
disconnect_on_expired_password
system variable:
If
disconnect_on_expired_password
is enabled (the default), the server disconnects the client with anER_MUST_CHANGE_PASSWORD_LOGIN
error.If
disconnect_on_expired_password
is disabled, the server puts the client in sandbox mode.
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 themysql.user
system table to enable DBAs to expire account passwords. The column default value is'N'
(not expired).The
ALTER USER ... PASSWORD EXPIRE
statement is introduced as the SQL interface for setting thepassword_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 anER_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 setspassword_expired
to'N'
.A bug in the initial implementation is that
ALTER USER
sets thePassword
column in themysql.user
system 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 thePassword
column to the empty string.MySQL 5.6.8:
ALTER USER
can be used as a prepared statement.mysqladmin password is made capable of resetting expired passwords for accounts that use the
mysql_native_password
ormysql_old_password
authentication plugin.Sandbox mode is changed to permit clients to execute
SET
statements in addition toSET PASSWORD
ProhibitingSET
prevented clients that needed to setold_passwords
from resetting their password. It also broke some Connectors, which useSET
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:
The
disconnect_on_expired_password
system variable is added, which controls how the server treats expired-password accounts.Two flags are added to the C API client library:
MYSQL_OPT_CAN_HANDLE_EXPIRED_PASSWORDS
formysql_options()
andCLIENT_CAN_HANDLE_EXPIRED_PASSWORDS
formysql_real_connect()
. Each flag enables a client program to indicate whether it can handle sandbox mode for accounts with expired passwords.MYSQL_OPT_CAN_HANDLE_EXPIRED_PASSWORDS
is enabled for mysqltest unconditionally, for mysql in interactive mode, and for mysqladmin if the first command ispassword
.The
ER_MUST_CHANGE_PASSWORD_LOGIN
error is added. The server returns this error when it disconnects a client that has an expired password.MySQL 5.6.12: The
--connect-expired-password
option is added to the mysql client to enable password-change statement execution in batch mode for accounts with an expired password.
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.