Bug #79959 SYS should have a view of user accounts expiring
Submitted: 13 Jan 2016 16:50 Modified: 19 Jan 2016 8:12
Reporter: Morgan Tocker Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: SYS Schema Severity:S4 (Feature request)
Version:5.7 OS:Any
Assigned to: CPU Architecture:Any
Triage: Needs Triage: D5 (Feature request)

[13 Jan 2016 16:50] Morgan Tocker
Description:
The SYS schema should have a view that shows user accounts that are expired or about to expire ordered by first to expire.

This will be useful for DBAs that are using the password expiry feature of MySQL 5.7 and above.

How to repeat:
N/A

Suggested fix:
SELECT * FROM sys.user_account_expiry
[19 Jan 2016 8:12] Umesh Shastry
Hello Morgan,

Thank you for the feature request!

Thanks,
Umesh
[26 Jan 2016 21:38] Todd Farmer
Very much agree with the request.  I demonstrate a SELECT query to find future expiring accounts in this blog post:

http://mysqlblog.fivefarmers.com/2014/03/31/password-expiration-policy-in-mysql-server-5-7...

The hard part is defining how far into the future DBAs may need to look (one day?  a week?  a month?).  That has to be coded into the view.  An alternative would be to use a stored procedure:

mysql> CREATE PROCEDURE sys.password_expiration_warning(IN expire_in_days INT)
    -> SELECT
    ->   user,
    ->   host,
    ->   password_lifetime,
    ->   password_last_changed,
    ->   IF(password_lifetime = 0,
    ->     NULL,
    ->     IF(@@GLOBAL .default_password_lifetime <> 0,
    ->       password_last_changed + INTERVAL IF(password_lifetime IS NULL,
    ->         @@GLOBAL .default_password_lifetime,
    ->         password_lifetime) DAY,
    ->       IF(password_lifetime IS NULL,
    ->         NULL,
    ->         password_last_changed + INTERVAL password_lifetime DAY))
    ->   ) expiration_date
    -> FROM
    ->   mysql.user
    -> WHERE
    ->   password_lifetime <> 0
    ->     AND password_last_changed IS NOT NULL
    ->     AND ((@@GLOBAL .default_password_lifetime <> 0
    ->     AND password_lifetime IS NULL)
    ->     OR (password_lifetime IS NOT NULL
    ->       AND password_last_changed < NOW()
    ->         + INTERVAL expire_in_days DAY
    ->         - INTERVAL password_lifetime DAY
    ->       AND password_last_changed >= NOW()
    ->         - INTERVAL password_lifetime DAY)
    ->     OR (password_lifetime IS NULL
    ->       AND password_last_changed < NOW()
    ->         + INTERVAL expire_in_days DAY
    ->         - INTERVAL @@GLOBAL .default_password_lifetime DAY
    ->       AND password_last_changed >= NOW()
    ->         - INTERVAL @@GLOBAL .default_password_lifetime DAY));
Query OK, 0 rows affected (0.00 sec)

mysql> CALL sys.password_expiration_warning(6);
+-------+-----------+-------------------+-----------------------+---------------
------+
| user  | host      | password_lifetime | password_last_changed | expiration_dat
e     |
+-------+-----------+-------------------+-----------------------+---------------
------+
| test1 | localhost |                 5 | 2016-01-26 14:31:48   | 2016-01-31 14:
31:48 |
+-------+-----------+-------------------+-----------------------+---------------
------+
1 row in set (0.00 sec)