MySQL 5.6 Reference Manual Including MySQL NDB Cluster 7.3-7.4 Reference Guide
ALTER USERuser_specification
[,user_specification
] ...user_specification
:user
PASSWORD EXPIRE
The ALTER USER
statement modifies
MySQL accounts. An error occurs if you try to modify a
nonexistent account.
To use ALTER USER
, you must have
the global CREATE USER
privilege
or the UPDATE
privilege for the
mysql
system database. When the
read_only
system variable is
enabled, ALTER USER
additionally
requires the SUPER
privilege.
Each account name uses the format described in
Section 6.2.4, “Specifying Account Names”. The host name part of the
account name, if omitted, defaults to '%'
. It
is also possible to specify
CURRENT_USER
or
CURRENT_USER()
to refer to the
account associated with the current session.
For each account, ALTER USER
expires its password. For example:
ALTER USER 'jeffrey'@'localhost' PASSWORD EXPIRE;
Password expiration for an account affects the corresponding row
of the mysql.user
system table: The server
sets the password_expired
column to
'Y'
.
A client session operates in restricted mode if the account password has been expired. In restricted mode, operations performed within the session result in an error until the user establishes a new account password:
mysql>SELECT 1;
ERROR 1820 (HY000): You must SET PASSWORD before executing this statement mysql>SET PASSWORD = PASSWORD('
Query OK, 0 rows affected (0.01 sec) mysql>new_password
');SELECT 1;
+---+ | 1 | +---+ | 1 | +---+ 1 row in set (0.00 sec)
This restricted mode of operation permits
SET
statements, which is useful if the account password has a
hashing format that requires
old_passwords
to be set to a
value different from its default before using
SET PASSWORD
.
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.
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.