Pre-General Availability Draft: 2017-05-26
MySQL enables database administrators to expire account passwords manually, and to establish a policy for automatic password expiration.
To expire a password manually, the database administrator uses the
ALTER USER statement:
ALTER USER 'jeffrey'@'localhost' PASSWORD EXPIRE;
This operation marks the password expired in the corresponding
mysql.user table row. 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 it is
past its permitted lifetime. This works with no explicit manual
system variable defines the global automatic password expiration
policy. It applies to accounts that use MySQL built-in
authentication methods (accounts that use an authentication plugin
is 0, which disables automatic password expiration. If the value
default_password_lifetime is a
N, it indicates the
permitted password lifetime; passwords must be changed every
To establish a global policy that passwords have a lifetime of approximately six months, start the server with these lines in an option file:
To establish a global policy such that passwords never expire, set
SET GLOBAL default_password_lifetime = 180; SET GLOBAL default_password_lifetime = 0;
No matter the global policy, it can be overridden for individual
Require the password to be changed every 90 days:
ALTER USER 'jeffrey'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;
Disable password expiration:
ALTER USER 'jeffrey'@'localhost' PASSWORD EXPIRE NEVER;
Defer to the global expiration policy:
ALTER USER 'jeffrey'@'localhost' PASSWORD EXPIRE DEFAULT;
PASSWORD EXPIRE DEFAULTdefers to the global exipration policy and in the
mysql.usertable sets the
NULLfor the named account.
ALTER USER statements update
mysql.user table row.
When a client successfully connects, the server determines whether the account password is expired:
The server checks whether the password has been manually expired and, if so, restricts the session.
Otherwise, the server checks whether the password is past its lifetime according to the automatic password expiration policy. If so, the server considers the password expired and restricts the session.
A restricted client operates in “sandbox mode,”, which limits the operations permitted to the client (see Section 7.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:
SELECT 1;ERROR 1820 (HY000): You must SET PASSWORD before executing this statement mysql>
ALTER USER USER() IDENTIFIED BY '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 if the deprecated
SET PASSWORD is used instead of
ALTER USER and the account password
has a hashing format that requires
old_passwords to be set to a
value different from its default.
It is possible for an administrative user to reset the account password, but any existing 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.