MySQL enables database administrators to expire account passwords manually, and to establish a policy for automatic password expiration.
Be aware that, if you make no changes to the
variable nor to the individual user accounts, all user passwords
will expire after 360 days, and all user accounts will start
running in restricted mode when this happens. Clients (which are
effectively users) connecting to the server will then get an
error indicating that the password must be changed:
ERROR 1820 (HY000): You must reset your password using
ALTER USER statement before executing this statement.
However, this is easy to miss for clients that automatically connect to the server, like 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, although this is not
recommended for security reasons.
To expire a password manually, the database administratior uses
ALTER USER statement:
ALTER USER 'jeffrey'@'localhost' PASSWORD EXPIRE;
This operation marks the password expired in the corresponding
mysql.user table row.
Automatic password expiration is available in MySQL 5.7.4 and
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 password expiration.
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
The default global policy is that passwords have a lifetime of 360
days. To change the policy, change the value of the
default_password_lifetime. If the
value is a positive integer, it indicates the permitted password
lifetime in days. A value of 0 disables automatic expiration.
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 GLOBAL default_password_lifetime = 180;
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;
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 client session operates in restricted mode if the account password was expired manually or if the password is considered past its lifetime per the automatic expiration policy. In restricted mode, operations performed within the session 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 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.
It is possible for an administrative user to reset the account password, but any existing sessions for the 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.