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 😉

Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

7 Comments

    • Hi Al,

      Your comment is not constructive at all but I approved it.

      Nobody forces you to read my blog.

      Instead of injuring people you should maybe explain what’s your issue.

      .

  1. It is useful, Im new to mysql and working on a script to send alerts in case an account is coming to expire, I can use this as a begining. Tks a lot.

Leave a Reply to TCCancel Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

As MySQL Community Manager, I am an employee of Oracle and the views expressed on this blog are my own and do not necessarily reflect the views of Oracle.

You can find articles I wrote on Oracle’s blog.