MySQL如何查看/監控/處理賬號密碼過期問題

MySQL 8.0.x中，我們如果按安全規範配置了賬號密碼過期策略的話，那麼如何查看賬號密碼還有多長時間就會過期；如何做好賬號密碼過期監控；以及提前及時處理賬號密碼過期問題就是DBA必須處理的一些事情。這裡簡單討論一下這些事情。個人經驗僅供參考，如有不足或錯誤的地方，敬請指正一二。這篇文章的具體測試環境為MySQL 8.0.35.

設置賬號密碼過期

一般系統變數default_password_lifetime控制著全局範圍的賬號密碼過期時間。

mysql> show variables like 'default_password_lifetime';
+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| default_password_lifetime | 180   |
+---------------------------+-------+
1 row in set (0.02 sec)

mysql> 

這個系統系統變數可以在my.cnf中設置。也可以在MySQL運行時設置並持久化。

SET PERSIST default_password_lifetime = 365;

你創建賬號時可以指定賬號密碼過期使用預設的全局策略，也可以不指定，它也會使用全局的密碼過期策略，如下所示， 下麵兩個SQL其實是等價的。

create user test2@'%' identified by random password ;
create user test2@'%' identified by random password password expire default;

另外，你也可以在創建賬號時設置賬號密碼過期時間，如下所示，那麼此過期時間會覆蓋系統變數default_password_lifetime的值（優先順序高於全局變數）。這個值也會記錄到系統表mysql.user中password_lifetime中，如果創建用戶時沒有指定賬號密碼過期期限，而是使用全局策略，那麼此時password_lifetime的值為NULL。

CREATE USER 'test'@'%' IDENTIFIED BY RANDOM PASSWORD PASSWORD EXPIRE INTERVAL 20 DAY;

手動設置賬號密碼過期

我們也可以手工設置賬號密碼過期，如下所示：

ALTER USER 'USER_NAME'@'HOSTNAME' PASSWORD EXPIRE;
ALTER USER 'USER_NAME'@'HOSTNAME' PASSWORD EXPIRE INTERVAL 90 DAY;

如果手工將一個賬號手工設置為賬號密碼過期，那麼系統表mysql.user中的password_expired會變為Y，而密碼全局過期策略下過期的賬號的欄位password_expired則永遠為N。這些細節不知你有沒有註意過。

查詢賬號密碼過期期限

根據MySQL密碼過期策略，密碼過期是自動的，並基於密碼期限，對於給定帳戶，密碼期限是從其最近一次密碼更改的日期和時間開始評估的。系統表為每個帳戶指示上次更改其密碼的時間(mysql.user表的password_last_changed欄位值），如果密碼的期限大於其允許的生存期，伺服器會自動將密碼視為在客戶端連接時已過期。這適用於沒有明確的手動設置密碼過期的賬戶。

查看資料庫中賬號密碼還有多久即將過期，可以使用下麵這個SQL：

select user
     , host
     , plugin
     , password_expired
     , password_last_changed
     , if(ifnull(password_lifetime, CAST(@@default_password_lifetime as signed))<1,'NEVER',
       concat(
            cast(   ifnull(password_lifetime, @@default_password_lifetime) as signed)
          + cast(datediff(password_last_changed, now()) as signed), " days")) as 
       days_till_expires
from mysql.user;

或者使用下麵優化調整過的SQL

--如果使用ALTER USER 'test'@'%' PASSWORD EXPIRE命令使賬號密碼過期，但是days_till_expires依然顯示賬號密碼還有N天才過期。
select user
     , host
     , plugin
     , password_expired
     , password_last_changed
     , if(password_expired='Y','password_expired',
          if(ifnull(password_lifetime, CAST(@@default_password_lifetime as signed))<1,'NEVER',
          concat(
               cast(   ifnull(password_lifetime, @@default_password_lifetime) as signed)
             + cast(datediff(password_last_changed, now()) as signed), " days")))  
       as days_till_expires
from mysql.user;

監控賬號密碼過期

監控賬號密碼過期，我們可以使用python腳本監控MySQL賬號密碼過期問題，如果密碼小於N(N可以是30，60)天即將過期，那麼發出下麵告警郵件：

處理即將密碼過期賬號

對於即將過期的賬號密碼，分兩種情況：

1：password_lifetime為null，default_password_lifetime不為null的情況，重置密碼後，它的過期期限變為default_password_lifetime的值。

mysql> alter user zbx_monitor@'localhost' identified by "*******";
Query OK, 0 rows affected (0.04 sec)

2：password_lifetime不為null的情況（手工設置密碼過期期限的情況），那麼你可以有以下幾種方式來處理

mysql> alter user test@'%' identified by "******";
Query OK, 0 rows affected (0.01 sec)

此時password_lifetime會變為之前指定的過期期限，當然，你也可以在修改賬號密碼時指定過期期限。

#不修改賬號密碼

mysql> alter user test@'%'  password expire interval 180 day;
Query OK, 0 rows affected (0.01 sec)

#修改賬號密碼

mysql> alter user test@'%' identified by "******" password expire interval 190 day;
Query OK, 0 rows affected (0.06 sec)

賬號密碼過期後，依然能連接上MySQL，但是此時做任何操操作都會報ERROR 1820（HY000)錯誤，如下所示：

mysql> select * from dual;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
mysql> 

從 MySQL 8.0.14 開始，允許用戶帳戶擁有雙重密碼，指定為主密碼和輔助密碼。雙密碼功能可以在以下場景中無縫執行憑據更改：

• 一個系統有大量的MySQL伺服器，可能涉及複製。

• 多個應用程式連接到不同的 MySQL 伺服器。

• 必須對應用程式用來連接伺服器的一個或多個帳戶定期更改憑據。

DBA如果經歷過的，也許都知道有時候修改資料庫賬號密碼是比較麻煩的事情，系統開發人員或系統Support人員會覺得比較麻煩，因為對於複雜系統，一不小心，賬號密碼配置出了差錯或紕漏，可能會導致生產事故，另外，修改賬號密碼也會影響系統業務的可用性（可能需要短暫的停止應用系統服務...），所以對於定期修改賬號密碼，很多時候是名存實亡。因此要麼不會修改資料庫賬號密碼，要麼按規範定期修改賬號密碼，也是使用相同的賬號密碼，並不會修改賬戶密碼。

如果使用雙密碼，就可以更輕鬆地分階段修改密碼，無需密切合作，也無需停機。確實是一個很了不起的創新。其他資料庫暫時還沒有這種特性。

#建立新的主密碼，保留當前密碼作為輔助密碼

ALTER USER 'test'@'%' IDENTIFIED BY '****' RETAIN CURRENT PASSWORD;

#等到應用程式切換密碼後，丟棄舊密碼（輔助密碼）

ALTER USER 'test'@'%' DISCARD OLD PASSWORD;