How To Identify Password Expire Date For Any User (Doc ID 2315846.1)

郑全

How To Identify Password Expire Date For Any User (Doc ID 2315846.1)

In this Document
Goal
Solution
References

APPLIES TO:
MySQL Server - Version 5.1 and later
Information in this document applies to any platform.
GOAL
How to get the exact number of days left for the password to expire when the password expiry policy is enabled.

SOLUTION
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 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;