Documentation Home
MySQL 5.7 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 35.1Mb
PDF (A4) - 35.2Mb
Man Pages (TGZ) - 256.4Kb
Man Pages (Zip) - 361.2Kb
Info (Gzip) - 3.4Mb
Info (Zip) - 3.4Mb
Excerpts from this Manual

MySQL 5.7 Reference Manual  / ...  / Security  / Access Control and Account Management  /  Server Handling of Expired Passwords

6.2.12 Server Handling of Expired Passwords

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 (seeSection 6.2.11, “Password Management”).

TheALTER 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 anER_MUST_CHANGE_PASSWORD_LOGIN error:

$> 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 restricts the client to sandbox mode, these operations are permitted within the client session:

  • The client can reset the account password withALTER USER orSET PASSWORD. After that has been done, the server restores normal access for the session, as well as for subsequent connections that use the account.

    Note

    Although 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 theSET statement, which is useful before MySQL 5.7.6 ifSET PASSWORD must be used instead ofALTER USER and 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 anER_MUST_CHANGE_PASSWORD error:

mysql> USE performance_schema;ERROR 1820 (HY000): You must reset your password using ALTER USERstatement before executing this statement.mysql> SELECT 1;ERROR 1820 (HY000): You must reset your password using ALTER USERstatement before executing this statement.

That is what normally happens for interactive invocations of themysql client because by default such invocations are put in sandbox mode. To resume normal functioning, select a new password.

For noninteractive invocations of themysql client (for example, in batch mode), the server normally disconnects the client if the password is expired. To permit noninteractivemysql 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 themysql 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.

Note

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 theMYSQL_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 themysql client, which enablesMYSQL_OPT_CAN_HANDLE_EXPIRED_PASSWORDS if invoked interactively or with the--connect-expired-password option.

  • Pass theCLIENT_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 thedisconnect_on_expired_password system variable: