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
• Status: Verified
• Category: MySQL Server: SYS Schema
• Severity: S4 (Feature request)
• Version: 5.7
• OS: Any
• CPU Architecture: Any

[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] MySQL Verification Team

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)