MySQL enables database administrators to expire account passwords
manually, and to establish a policy for automatic password
expiration. It is possible to establish expiration policy
globally, as well as on a per-account basis. These capabilities
apply to accounts that use a MySQL built-in authentication plugin
sha256_password). For accounts that use plugins
that perform authentication against an external credential system,
password expiration must be handled externally as well.
To expire an account password manually, use the
ALTER USER statement:
ALTER USER 'jeffrey'@'localhost' PASSWORD EXPIRE;
This operation marks the password expired in the corresponding
mysql.user table row.
Password expiration according to policy is automatic and is based
on password age, which for a given account is assessed from the
date and time of its most recent password change. The
mysql.user table indicates for each account
when its password was last changed, and the server automatically
treats the password as expired at client connection time if its
age is greater than its permitted lifetime. This works with no
explicit manual password expiration.
To establish automatic password-expiration policy globally, use
system variable. Its default value is 0, which disables automatic
password expiration. If the value of
default_password_lifetime is a
N, it indicates the
permitted password lifetime, such that passwords must be changed
Prior to 5.7.11, the default
is 360 (passwords must be changed approximately once per year).
For such versions, be aware that, if you make no changes to the
variable or to individual user accounts, each user password
expires after 360 days and the account starts running in
restricted mode. Clients that connect to the server using the
account then get an error indicating that the password must be
ERROR 1820 (HY000): You must reset your
password using ALTER USER statement before executing this
However, this is easy to miss for clients that automatically connect to the server, such as connections made from scripts. To avoid having such clients suddenly stop working due to a password expiring, make sure to change the password expiration settings for those clients, like this:
ALTER USER 'script'@'localhost' PASSWORD EXPIRE NEVER
Alternatively, set the
0, thus disabling automatic
password expiration for all users.
To establish a global policy that passwords have a lifetime of approximately six months, start the server with these lines in a server
To establish a global policy such that passwords never expire, set
default_password_lifetimecan also be changed at runtime:
SET GLOBAL default_password_lifetime = 180; SET GLOBAL default_password_lifetime = 0;
To establish password-expiration policy for individual accounts,
PASSWORD EXPIRE options of the
CREATE USER and
ALTER USER statements. See
Section 220.127.116.11, “CREATE USER Syntax”, and Section 18.104.22.168, “ALTER USER Syntax”.
Example account-specific expiration statements:
Require the password to be changed every 90 days:
CREATE USER 'jeffrey'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY; ALTER USER 'jeffrey'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;
Disable password expiration:
CREATE USER 'jeffrey'@'localhost' PASSWORD EXPIRE NEVER; ALTER USER 'jeffrey'@'localhost' PASSWORD EXPIRE NEVER;
Defer to the global expiration policy:
CREATE USER 'jeffrey'@'localhost' PASSWORD EXPIRE DEFAULT; ALTER USER 'jeffrey'@'localhost' PASSWORD EXPIRE DEFAULT;
When a client successfully connects, the server determines whether the account password has expired:
The server checks whether the password has been manually expired.
Otherwise, the server checks whether the password age is greater than its permitted lifetime according to the automatic password expiration policy. If so, the server considers the password expired.
If the password is expired (whether manually or automatically), the server either disconnects the client or restricts the operations permitted to it (see Section 6.3.8, “Password Expiration and Sandbox Mode”). Operations performed by a restricted client result in an error until the user establishes a new account password:
mysql> SELECT 1; ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement. mysql> ALTER USER USER() IDENTIFIED BY 'password'; Query OK, 0 rows affected (0.01 sec) mysql> SELECT 1; +---+ | 1 | +---+ | 1 | +---+ 1 row in set (0.00 sec)
This restricted mode of operation permits
statements, which is useful before MySQL 5.7.6 if
SET PASSWORD must be used instead
ALTER USER and the account
password has a hashing format that requires
old_passwords to be set to a
value different from its default.
After the client resets the password, the server restores normal access for the session, as well as for subsequent connections that use the account. It is also possible for an administrative user to reset the account password, but any existing restricted sessions for that account remain restricted. A client using the account must disconnect and reconnect before statements can be executed successfully.
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.