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: | |
| Category: | MySQL Server: SYS Schema | Severity: | S4 (Feature request) |
| Version: | 5.7 | OS: | Any |
| Assigned to: | CPU Architecture: | Any |
[13 Jan 2016 16:50]
Morgan Tocker
[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)
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.067 sec. using MySQL 8.4.7-u6-cloud
Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the
respective copyright holders.
It is not reviewed in advance
by Oracle and does not necessarily represent the opinion of
Oracle or any other party.