MySQL: when will the password of my users expire ?

Has you may already know, in MySQL it is possible to set an expiration period for passwords.

It’s not always obvious to see when the password will expire. And most of the time if you don’t pay much attention you will get something like this:

ERROR 1820 (HY000): You must reset your password using
ALTER USER statement before executing this statement.

Let me share you a small query that check for how long your password is still valid in case it was created with an expiration delay or if the global default_password_lifetime is set on the server (from MySQL 5.7.4 to 5.7.10 it was set to 360 by default)!

select user, password_last_changed,
concat(
cast(
 IFNULL(password_lifetime, @@default_password_lifetime) as signed)
 + cast(datediff(password_last_changed, now()) as signed), " days") expires_in
from mysql.user
where 
cast(
 IFNULL(password_lifetime, @@default_password_lifetime) as signed)
 + cast(datediff(password_last_changed, now()) as signed) >= 0 
 and user not like 'mysql.%';

Let’s see the result when default_password_lifetime is set to 0 (actual default):

+-------+-----------------------+------------+
| user  | password_last_changed | expires_in |
+-------+-----------------------+------------+
| test  | 2018-11-16 13:12:11   | 2 days     |
| test2 | 2018-11-10 13:16:44   | 4 days     |
+-------+-----------------------+------------+

Now let’s change that global variable:

mysql> set global default_password_lifetime=20;

+-------+-----------------------+------------+
| user  | password_last_changed | expires_in |
+-------+-----------------------+------------+
| fred  | 2018-11-12 21:59:56   | 16 days    |
| test  | 2018-11-16 13:12:11   | 2 days     |
| test2 | 2018-11-10 13:16:44   | 4 days     |
+-------+-----------------------+------------+

That was the easy useful query of the day 😉