作者：Sri Sakthivel

原文鏈接：https://www.percona.com/blog/enhanced-password-management-systems-in-mysql-8-part-1

MySQL 8.0 在密碼管理方面有很多改善，本文將介紹以下兩個特性，

密碼重用策略
生成隨機密碼

| 1 密碼重用策略

該策略簡單說，就是設定新密碼時，可以限制禁止使用曾經用過的密碼，有以下兩種策略：

歷史密碼 password_history
間隔時間 password_reuse_interval

1.1 歷史密碼

MySQL 官方手冊描述：

If an account is restricted on the basis of number of password changes, a new password cannot be chosen from a specified number of the most recent passwords.

在實驗環境中，創建用戶并添加條件password history 2，歷史密碼中最近的兩個不能被重新使用，

mysql> create user 'herc'@'localhost' identified by 'Percona@321' password history 2;
Query OK, 0 rows affected (0.02 sec)

mysql> select user, host, password_reuse_history from mysql.user where user='herc'\G
*************************** 1. row ***************************
                  user: herc
                  host: localhost
password_reuse_history: 2
1 row in set (0.00 sec)

MySQL 將在 mysql.password_history 表上記錄密碼更改的資訊，

mysql> select * from mysql.password_history;
+-----------+------+----------------------------+------------------------------------------------------------------------+
| Host      | User | Password_timestamp         | Password                                                               |
+-----------+------+----------------------------+------------------------------------------------------------------------+
| localhost | herc | 2021-09-20 15:44:42.295778 | $A$005$=R:q'M(Kh#D];c~SdCLyluq2UVHFobjWOFTwn2JYVFDyI042sl56B7DCPSK5 |
+-----------+------+----------------------------+------------------------------------------------------------------------+
1 row in set (0.00 sec)

接下來我們嘗試更改用戶 herc@localhost 的密碼，

mysql> alter user 'herc'@'localhost' identified by 'MySQL@321';
Query OK, 0 rows affected (0.02 sec)

mysql> select * from mysql.password_history\G
*************************** 1. row ***************************
              Host: localhost
              User: herc
Password_timestamp: 2021-09-20 15:49:15.459018
CGeRQT31UUwtw194KOKGdNbgj3558VUB.dxcoS8r4IKpG8
*************************** 2. row ***************************
              Host: localhost
              User: herc
Password_timestamp: 2021-09-20 15:44:42.295778
          Password: $A$005$=R:q'M(Kh#D];c~SdCLyluq2UVHFobjWOFTwn2JYVFDyI042sl56B7DCPSK5
2 rows in set (0.00 sec)

更改成功后，查看 mysql.password_history 表，可見該表存有最近兩個密碼的資訊，

再次更改該用戶密碼，且密碼為創建用戶時設定的密碼值（Percona@321），

mysql> alter user 'herc'@'localhost' identified by 'Percona@321';
ERROR 3638 (HY000): Cannot use these credentials for 'herc@localhost' because they contradict the password history policy

修改失敗! 因為根據我們設定的限制策略，不能重用在 mysql.password_policy 表中被記錄的最近的兩個密碼，因此，如果想再次重復使用第一個密碼，就不能讓該密碼出現在 mysql.password_policy 表中，

再設定一個新密碼后（第一個密碼已經不是最近的兩個密碼了），再嘗試用第一個密碼值（Percona@321）進行修改，修改成功！

mysql> alter user 'herc'@'localhost' identified by 'Herc@321';
Query OK, 0 rows affected (0.01 sec)

mysql> alter user 'herc'@'localhost' identified by 'Percona@321';
Query OK, 0 rows affected (0.02 sec)

也可以在啟動時在全域配置 password_history，

#vi my.cnf
[mysqld]
password_history=6

#set global
mysql> set global password_history=5;
Query OK, 0 rows affected (0.00 sec)

1.2 間隔時間

MySQL 官方手冊描述：

If an account is restricted based on time elapsed, a new password cannot be chosen from passwords in the history that are newer than a specified number of days.

測驗前，創建了用戶 sri@localhost，并設定用戶密碼可重用的時間間隔為五天，

mysql> create user 'sri'@'localhost' identified by 'Percona@321' password reuse interval 5 day;
Query OK, 0 rows affected (0.01 sec)

mysql> select user, host, password_reuse_time from mysql.user where user='sri'\G
*************************** 1. row ***************************
               user: sri
               host: localhost
password_reuse_time: 5
1 row in set (0.00 sec)

這意味著每個密碼生效后，在五天內都不能再被重復設定，

mysql> select * from mysql.password_history where user='sri'\G
*************************** 1. row ***************************
              Host: localhost
              User: sri
Password_timestamp: 2021-09-20 16:09:27.918585
          Password: $A$005$+B   e3!C9&8m
                                         eFRG~IqRWX4b6PtzLA8I4VsdYvWU3qRs/nip/QRhXXR5phT6
1 row in set (0.00 sec)

執行 ALTER 來更改密碼，

mysql> alter user 'sri'@'localhost' identified by 'Herc@321';
Query OK, 0 rows affected (0.02 sec)

mysql> select * from mysql.password_history where user='sri'\G
*************************** 1. row ***************************
              Host: localhost
              User: sri
Password_timestamp: 2021-09-20 16:17:51.840483
          Password: $A$005$~k7qp8.OP=^#e79qwtiYd7/cmCFLvHM7MHFbvfX2WlhXqzjmrN03gGZ4
*************************** 2. row ***************************
              Host: localhost
              User: sri
Password_timestamp: 2021-09-20 16:09:27.918585
          Password: $A$005$+B   e3!C9&8m
                                         eFRG~IqRWX4b6PtzLA8I4VsdYvWU3qRs/nip/QRhXXR5phT6
2 rows in set (0.00 sec)

現在嘗試再次設定為第一個密碼，

mysql> alter user 'sri'@'localhost' identified by 'Percona@321';
ERROR 3638 (HY000): Cannot use these credentials for 'sri@localhost' because they contradict the password history policy

設定失敗！

也可以在啟動時在全域配置 password_reuse_interval，

#vi my.cnf
[mysqld]
password_reuse_interval=365

#set global
mysql> set global password_reuse_interval=365;
Query OK, 0 rows affected (0.00 sec)

| 2 隨機密碼

從 MySQL 8.0.18 開始，MySQL 能夠為用戶帳戶創建隨機密碼，這意味著可以不必分配指定密碼，它支持以下陳述句：

創建用戶
更改用戶
設定密碼

我們需要使用 RANDOM PASSWORD 以避免修改密碼時螢屏上有明文顯示，例如：

mysql> create user 'sakthi'@'localhost' identified by random password;
+--------+-----------+----------------------+
| user   | host      | generated password   |
+--------+-----------+----------------------+
| sakthi | localhost | .vZYy+<<BO7l1;vtIufH |
+--------+-----------+----------------------+
1 row in set (0.01 sec)

mysql> alter user 'sri'@'localhost' identified by random password;
+------+-----------+----------------------+
| user | host      | generated password   |
+------+-----------+----------------------+
| sri  | localhost | 5wb>2[]q*jbDsFvlN-i_ |
+------+-----------+----------------------+
1 row in set (0.02 sec)

密碼哈希值將存盤在 mysql.user 表中，

mysql> select user, authentication_string from mysql.user where user in ('sakthi','sri')\G
*************************** 1. row ***************************
                 user: sakthi
authentication_string: $A$005$L`PYcedj%3tz*J>ioBP1.Rsrj7H8wtelqijvV0CFnXVnWLNIc/RZL0C06l4oA
*************************** 2. row ***************************
                 user: sri
authentication_string: $A$005$/k?aO&ap.#b=
                                          ^zt[E|x9q3w9uHn1oEumXUgnqNMH8xWo4xd/s26hTPKs1AbC2
2 rows in set (0.00 sec)

默認情況下，密碼長度為 20 個字符，我們可以使用變數 generated_random_password_length 定義密碼長度，允許的長度范圍是 5 到 255，

mysql> select @@generated_random_password_length;
+------------------------------------+
| @@generated_random_password_length |
+------------------------------------+
|                                 20 |
+------------------------------------+
1 row in set (0.00 sec)

如果 validate_password 組件已經安裝，對隨機密碼策略不會有影響，

還有一些特性，將在下一篇文章中介紹，

參考

MySQL 手冊：

https://dev.mysql.com/doc/mysql-security-excerpt/8.0/en/password-management.html
https://dev.mysql.com/doc/refman/8.0/en/password-management.html#random-password-generation

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/304224.html

標籤：MySQL

上一篇：MySQL優化之路

下一篇：redis

譯文 | MySQL 8.0 密碼管理策略（一）

| 1 密碼重用策略

1.1 歷史密碼

1.2 間隔時間

| 2 隨機密碼

參考