重庆思庄Oracle、Redhat认证学习论坛

 找回密码
 注册

QQ登录

只需一步,快速开始

搜索
查看: 105|回复: 0

[参数配置] How To Identify Password Expire Date For Any User (Doc ID 2315846.1)

[复制链接]
发表于 2024-6-17 00:17:27 | 显示全部楼层 |阅读模式
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;


回复

使用道具 举报

您需要登录后才可以回帖 登录 | 注册

本版积分规则

QQ|手机版|小黑屋|重庆思庄Oracle、Redhat认证学习论坛 ( 渝ICP备12004239号-4 )

GMT+8, 2024-7-23 19:03 , Processed in 0.108494 second(s), 21 queries .

重庆思庄学习中心论坛-重庆思庄科技有限公司论坛

© 2001-2020

快速回复 返回顶部 返回列表