MySQL Server - Find Password Expire Date For Any User

Question: How to get the exact number of days left for the password to expire when the password expiry policy is enabled?

There’s no single column in any table having countdown value of password lifetime. Rather, to get the remaining days for password expiry of any particular user, you need to calculate manually the value of below 2 fields from mysql.user table:

• password_last_changed - This indicates the date when password was set or changed.
• password_lifetime - This holds the password expire intervals in days. If this is NULL, @@global.default_password_lifetime is used instead.

The password policy compares these 2 values and expires password when it’s more than the lifetime. So, if you would like to get the exact number of days left or the exact date of expiry for a particular user’s password, use the below query:

mysql> SELECT user, host, password_last_changed,
              CONCAT(
                     CAST(IFNULL(password_lifetime, @@global.default_password_lifetime) AS signed)
                         + CAST(DATEDIFF(password_last_changed, now()) as signed), ' days'
                    ) AS expires_in,
              CAST(IFNULL(password_lifetime, @@global.default_password_lifetime) AS signed)
                   + CAST(DATEDIFF(password_last_changed, now()) as signed) AS expires_in_days,
              (password_last_changed
                    + INTERVAL CAST(IFNULL(password_lifetime, @@global.default_password_lifetime) AS signed) DAY
              ) AS expires_datetime
         FROM mysql.user
        WHERE account_locked = 'N' AND IFNULL(password_lifetime, @@global.default_password_lifetime) > 0;

In MySQL 8.0 you can use a common table expression to simplify the query:

mysql> WITH users AS (
           SELECT User, Host, password_last_changed,
                  CAST(IFNULL(password_lifetime, @@default_password_lifetime) AS signed) AS password_lifetime,
                  CAST(DATEDIFF(password_last_changed, now()) as signed) AS last_changed_days
             FROM mysql.user
            WHERE account_locked = 'N' AND IFNULL(password_lifetime, @@global.default_password_lifetime) > 0
       )
       SELECT user, host, password_last_changed,
              CONCAT(password_lifetime + last_changed_days, ' Days') AS expires_in,
              password_lifetime + last_changed_days AS expires_in_days,
              password_last_changed + INTERVAL password_lifetime DAY AS expires_datetime
         FROM users;