mysql.user表的資料準確性問題

潇湘隐者發表於2024-08-20
MySql

mysql.user這個系統表中有些欄位的資料是不準確的(或者說是不一定準確，這樣表達更嚴謹一點)。這是一個讓人頭疼的問題，下面簡單述說一下問題，主要是mysql.user表中的password_lifetime,password_reuse_history,password_reuse_time這幾個欄位的資料都不一定準確。

下面簡單演示一下，當前測試環境為MySQL 8.0.35。

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

mysql> show  global variables like 'password_history';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| password_history | 6     |
+------------------+-------+
1 row in set (0.01 sec)

mysql> show global variables like 'password_reuse_interval';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| password_reuse_interval | 365   |
+-------------------------+-------+
1 row in set (0.00 sec)

mysql>

此時，如果我建立一個使用者test,如下所示，你會看到password_lifetime值為null，password_reuse_history的值為null，這個值明顯不正確，是不是Bug呢？

mysql> CREATE USER `test`@`192.168.7.10%` IDENTIFIED BY  'Kjdh#234289dj';
Query OK, 0 rows affected (0.03 sec)

mysql> select user
    ->       ,host
    ->       ,account_locked 
    ->       ,password_last_changed
    ->       ,password_expired
    ->       ,password_lifetime
    ->       ,plugin 
    ->       ,password_reuse_history
    ->       ,Password_reuse_time
    -> from mysql.user
    -> where user='test'\G
*************************** 1. row ***************************
                  user: test
                  host: 192.168.7.10%
        account_locked: N
 password_last_changed: 2024-08-20 11:05:39
      password_expired: N
     password_lifetime: NULL
                plugin: caching_sha2_password
password_reuse_history: NULL
   Password_reuse_time: NULL
1 row in set (0.01 sec)

mysql>

搜尋相關資料時，發現已經有人反饋這個問題了，Bug #112128 The parameters such as MySQL password_history does not take effect[1] 但是官方的解釋是這是一個"正常現象",它不是一個Bug，具體解釋如下所示：

The way it works is as follows:
1. There is the global system variable default_password_lifetime that specifies the policy for all accounts that are set to use the default password lifetime. This is done by ALTER USER PASSWORD EXPIRE DEFAULT. In the system table (not that it matters, but still) this stores a NULL. The NULL value is used as a flag that the account in question does not have a special per user password lifetime. The special per-user password lifetime is set via ALTER USER PASSWORD EXPIRE NEVER (which sets the column to 0) or ALTER USER PASSWORD EXPIRE INTERVAL N DAY (which sets the column to N). 
As a consequence all password lifetimes for all users that do not have a specific password lifetime set will follow the value of the global variable.
And if you store a specific password lifefile for a user account it will "detach" itself from the global variable's value and will be pegged to whatever you've set for it. Until you reset it back to the default of course.

簡單翻譯如下：

它的工作原理如下：

有一個全域性系統變數 default_password_lifetime，它指定了所有使用預設密碼生命週期的帳戶的策略。這是透過 ALTER USER PASSWORD EXPIRE DEFAULT命令完成的。在系統表中（這並不重要，但還是說一下），它儲存了一個 NULL 值。NULL 值被用作一個標誌，表示相關的帳戶沒有特別的使用者密碼生命週期。 每個使用者的特殊的密碼生命週期是透過 ALTER USER PASSWORD EXPIRE NEVER（這將列設定為 0）或 ALTER USER PASSWORD EXPIRE INTERVAL N DAY（這將列設定為 N）來設定的。 因此，所有沒有設定特定/特殊密碼生命週期的使用者的所有密碼生命週期都將遵循全域性變數的值。 如果你為一個使用者帳戶儲存了一個特定的密碼生命週期，它將“脫離”全域性變數的值，並被固定為你為它設定的任何值。當然，除非你將其重置回預設值。

搜尋資料的過程，發現反饋這種現象("bug")的還不止一個，另外一個連結也是反饋了同樣的問題，Bug #89349 password_lifetime set to Null for user after changing default_password_lifetime[2]

這裡簡單說明一下，就是mysql.user中，password_lifetime值為null，表示它使用全域性系統變數的值，如果你為某個使用者指定了 特殊的密碼過期時間，那麼mysql.user的password_lifetime欄位才會儲存特定的密碼過期時間。也就是說如果我們建立使用者的時候指定密碼過期時間，或者使用SQL語句指定使用者的密碼過期時間，此時mysql.user中的password_lifetime等欄位值就是正確的。如下所示

mysql> ALTER USER 'test'@'192.168.7.10%' PASSWORD EXPIRE INTERVAL 60 DAY;
Query OK, 0 rows affected (0.01 sec)

mysql> select user
    ->       ,host
    ->       ,account_locked 
    ->       ,password_last_changed
    ->       ,password_expired
    ->       ,password_lifetime
    ->       ,plugin 
    ->       ,password_reuse_history
    ->       ,Password_reuse_time
    -> from mysql.user
    -> where user='test'\G
*************************** 1. row ***************************
                  user: test
                  host: 192.168.7.10%
        account_locked: N
 password_last_changed: 2024-08-20 11:05:39
      password_expired: N
     password_lifetime: 60
                plugin: caching_sha2_password
password_reuse_history: NULL
   Password_reuse_time: NULL
1 row in set (0.00 sec)

mysql> CREATE USER test1@'%' IDENTIFIED BY 'Kjdh#234289dj' 
    -> PASSWORD EXPIRE INTERVAL 20 DAY 
    -> PASSWORD HISTORY 30  
    -> PASSWORD REUSE INTERVAL 60 DAY;
Query OK, 0 rows affected (0.03 sec)

mysql> select user
    ->       ,host
    ->       ,account_locked 
    ->       ,password_last_changed
    ->       ,password_expired
    ->       ,password_lifetime
    ->       ,plugin 
    ->       ,password_reuse_history
    ->       ,Password_reuse_time
    -> from mysql.user
    -> where user='test1'\G
*************************** 1. row ***************************
                  user: test1
                  host: %
        account_locked: N
 password_last_changed: 2024-08-20 11:57:18
      password_expired: N
     password_lifetime: 20
                plugin: caching_sha2_password
password_reuse_history: 30
   Password_reuse_time: 60
1 row in set (0.00 sec)

mysql>

總結

雖然官方的這種邏輯處理也沒有什麼大問題，但是不能準確的反映使用者密碼過期時間，讓普通使用者也挺困惑的。為什麼不能統一致呢？

參考資料
[1]

1: https://bugs.mysql.com/bug.php?id=112128

 [2]

2: https://bugs.mysql.com/bug.php?id=89349

相關文章