Understanding and Managing MySQL User Password Expiration

Overview

We first revisit the fields related to password expiration in the mysql.user table.

mysql> use mysql
Database changed
mysql> desc user;
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Field                  | Type                              | Null | Key | Default               | Extra |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
...
| password_expired       | enum('N','Y')                     | NO   |     | N                     |       |
| password_last_changed  | timestamp                         | YES  |     | NULL                  |       |
| password_lifetime      | smallint(5) unsigned              | YES  |     | NULL                  |       |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
45 rows in set (0.01 sec)
mysql>

password_expired : introduced in MySQL 5.6.6, it indicates whether a user’s password is expired.

password_last_changed : the timestamp of the last password change.

password_lifetime : the lifetime of the password for the user; default is NULL , meaning the global policy applies unless overridden.

Another related parameter is default_password_lifetime , a global variable introduced in MySQL 5.7.4 that defines a default automatic expiration policy for accounts that use the default password lifetime.

Testing

1. password_expired: manual expiration

1) Set password to never expire

mysql> grant all on *.* to test@'localhost' identified by '123';
Query OK, 0 rows affected, 2 warnings (0.03 sec)
mysql> select User,password_last_changed,password_lifetime,password_expired from mysql.user;
+-------+-----------------------+-------------------+------------------+
| User  | password_last_changed | password_lifetime | password_expired |
+-------+-----------------------+-------------------+------------------+
| root  | 2021-03-31 14:11:10  | NULL              | N                |
| ...   | ...                   | ...               | ...              |
+-------+-----------------------+-------------------+------------------+
7 rows in set (0.00 sec)
mysql> ALTER USER 'test'@'localhost' PASSWORD EXPIRE NEVER;
Query OK, 0 rows affected (0.00 sec)
mysql> select User,password_last_changed,password_lifetime,password_expired from mysql.user;
... (output shows password_expired = N and password_lifetime = 0) ...
mysql>

Note: setting the parameter to 0 means the password never expires.

2) Set the user password to expire after 30 days (overrides the global policy)

mysql> ALTER USER 'test'@'localhost' PASSWORD EXPIRE INTERVAL 30 DAY;
Query OK, 0 rows affected (0.01 sec)
mysql> select User,password_last_changed,password_lifetime,password_expired from mysql.user;
... (output shows password_lifetime = 30 and password_expired = N) ...
mysql>

3) Force immediate expiration

mysql> ALTER USER 'hhh'@'%' PASSWORD EXPIRE;
Query OK, 0 rows affected (0.01 sec)
mysql> select User,password_last_changed,password_lifetime,password_expired from mysql.user;
... (output shows password_expired = Y for user hhh) ...
mysql> exit
Bye
[root@manage01 ~]# /opt/mysql/base/5.7.25/bin/mysql -uhhh -p -S /opt/mysql/data/3306/mysqld.sock
Logging to file '/mysqldata/mysql_3306/log/test.log'
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 39469
Server version: 5.7.25-log
...
mysql> show databases;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
mysql>

2. default_password_lifetime: automatic expiration mechanism

1) Set global password expiration time

# In my.cnf
[mysqld]
default_password_lifetime=90
# Or via command line
SET GLOBAL default_password_lifetime = 90;
Query OK, 0 rows affected (0.00 sec)
SHOW VARIABLES LIKE "default_password_lifetime";
+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| default_password_lifetime | 90    |
+---------------------------+-------+
1 row in set (0.00 sec)

2) Create a user and observe the effect

mysql> grant all on *.* to hhh@'%' identified by '123';
Query OK, 0 rows affected, 1 warning (0.04 sec)
mysql> select User,password_last_changed,password_lifetime,password_expired from mysql.user;
... (output shows password_lifetime = NULL for most users, because the per‑user value overrides the global only when explicitly set) ...
mysql>

Many wonder why password_lifetime does not become 90 after setting the global variable; this is not a bug. The global default_password_lifetime acts as a default policy stored as NULL in the system table. When a user’s password lifetime is set to 0 (via PASSWORD EXPIRE NEVER ) or to a specific number of days (via PASSWORD EXPIRE INTERVAL N DAY ), that value overrides the global setting.

Thus, all accounts without a specific per‑user password lifetime follow the value of the global variable.