mysql8的新增密码策略

Z时代
2024-01-10
分类：综合

database

二、功能实测：

1、不得使用最近2次用过的旧密码

--设置每个用户的旧密码历史记录数
mysql> set persist password_history = 2;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like "password%";
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| password_history         | 2     |
| password_require_current | OFF   |
| password_reuse_interval  | 0     |
+--------------------------+-------+
3 rows in set (0.00 sec)
mysql> create user kenyon identified by "1aaa";
Query OK, 0 rows affected (0.01 sec)
mysql> grant all on db_kenyon.* to kenyon;
Query OK, 0 rows affected (0.00 sec)
--密码历史记录表中有该用户的初次密码创建信息
mysql> select * from mysql.password_history;
+------+--------+----------------------------+------------------------------------------------------------------------+
| Host | User   | Password_timestamp         | Password                                                               |
+------+--------+----------------------------+------------------------------------------------------------------------+
| %    | kenyon | 2020-02-13 11:42:44.913000 | $A$005$V~}u%K.O8,l? >zc/kFPmoNtkMgu.EQWz7dw4BK1788T3K8fxVVi/HAlodo65 |
+------+--------+----------------------------+------------------------------------------------------------------------+
1 row in set (0.00 sec)
--第一次修改密码
mysql> alter user kenyon identified by "2aaa";
Query OK, 0 rows affected (0.00 sec)
mysql> select * from mysql.password_history;
+------+--------+----------------------------+------------------------------------------------------------------------+
| Host | User   | Password_timestamp         | Password                                                               |
+------+--------+----------------------------+------------------------------------------------------------------------+
| %    | kenyon | 2020-02-13 11:52:08.149997 | $A$005$3RsD!y^E.4#Oz6ppAx9UOx3IpdznWipv.6Buhg1NljmAFEzQ2YqXBdzjTDD |
| %    | kenyon | 2020-02-13 11:42:44.913000 | $A$005$V~}u%K.O8,l? >zc/kFPmoNtkMgu.EQWz7dw4BK1788T3K8fxVVi/HAlodo65 |
+------+--------+----------------------------+------------------------------------------------------------------------+
2 rows in set (0.00 sec)
--第二次如果使用旧密码则会报错违反当前密码策略
mysql> alter user kenyon identified by "1aaa";
ERROR 3638 (HY000): Cannot use these credentials for "kenyon@%" because they contradict the password history policy
--第二次如果使用新密码是可行的，同时会把密码记录表最老的记录数据清掉，因为该用户设置的全局密码记录数是2，注意看修改时间
mysql> alter user kenyon identified by "3aaa";
Query OK, 0 rows affected (0.01 sec)
mysql> select * from mysql.password_history;
+------+--------+----------------------------+------------------------------------------------------------------------+
| Host | User   | Password_timestamp         | Password                                                               |
+------+--------+----------------------------+------------------------------------------------------------------------+
| %    | kenyon | 2020-02-13 11:55:11.382348 | $A$005$2d,-?!6*Y1L1wYPLa/WGwD3zPzsAXE7vIQtmzhDerHRXJpLP3LpNtYF7 |
| %    | kenyon | 2020-02-13 11:52:08.149997 | $A$005$3RsD!y^E.4#Oz6ppAx9UOx3IpdznWipv.6Buhg1NljmAFEzQ2YqXBdzjTDD |
+------+--------+----------------------------+------------------------------------------------------------------------+
2 rows in set (0.00 sec)
--测试其它用户影响
mysql> create user salah identified by "salah";
Query OK, 0 rows affected (0.00 sec)
mysql> create user henderson identified by "henderson";
Query OK, 0 rows affected (0.00 sec)
mysql> alter user salah identified by "salah";
ERROR 3638 (HY000): Cannot use these credentials for "salah@%" because they contradict the password history policy
mysql>
mysql> alter user salah identified by "123456";
Query OK, 0 rows affected (0.00 sec)
mysql> select * from mysql.password_history;
+------+-----------+----------------------------+------------------------------------------------------------------------+
| Host | User      | Password_timestamp         | Password                                                               |
+------+-----------+----------------------------+------------------------------------------------------------------------+
| %    | henderson | 2020-02-13 12:08:04.592152 | $A$005$/?XvmZ7STd}1raVkrtQGCc9MJxtqF9YKWTdZSwU3x8FKPNb7GPd.JahbQr0 |
| %    | kenyon    | 2020-02-13 11:55:11.382348 | $A$005$2d,-?!6*Y1L1wYPLa/WGwD3zPzsAXE7vIQtmzhDerHRXJpLP3LpNtYF7 |
| %    | kenyon    | 2020-02-13 11:52:08.149997 | $A$005$3RsD!y^E.4#Oz6ppAx9UOx3IpdznWipv.6Buhg1NljmAFEzQ2YqXBdzjTDD |
| %    | salah     | 2020-02-13 12:08:37.506260 | $A$005$MoqqV}Z
                                                                #H+KFS3xS754Hoa6PECsJUV2il8/YqpkuHr9X0jFhmPew25 |
"Y0aHCx0)CBr0RMnAkE4ExnvuFqiafv0xQiG.FHFvoEvmwcrOiRtx2 |$jgx*
+------+-----------+----------------------------+------------------------------------------------------------------------+
5 rows in set (0.00 sec)
--删除某用户信息
mysql> drop user henderson;
Query OK, 0 rows affected (0.00 sec)
mysql> drop user salah;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from mysql.password_history;
+------+--------+----------------------------+------------------------------------------------------------------------+
| Host | User   | Password_timestamp         | Password                                                               |
+------+--------+----------------------------+------------------------------------------------------------------------+
| %    | kenyon | 2020-02-13 11:55:11.382348 | $A$005$2d,-?!6*Y1L1wYPLa/WGwD3zPzsAXE7vIQtmzhDerHRXJpLP3LpNtYF7 |
| %    | kenyon | 2020-02-13 11:52:08.149997 | $A$005$3RsD!y^E.4#Oz6ppAx9UOx3IpdznWipv.6Buhg1NljmAFEzQ2YqXBdzjTDD |
+------+--------+----------------------------+------------------------------------------------------------------------+
2 rows in set (0.00 sec)
--可以通过删除密码记录表相关数据使得修改旧密码得以成功
mysql> alter user kenyon identified by "1aaa";
ERROR 3638 (HY000): Cannot use these credentials for "kenyon@%" because they contradict the password history policy
mysql>
mysql> delete from mysql.password_history;
Query OK, 2 rows affected (0.00 sec)
mysql> alter user kenyon identified by "1aaa";Query OK, 0 rows affected (0.00 sec)

2、修改新改密码前验证模式

--可以在线更改，无需重启：
mysql80>set persist password_require_current = on;
Query OK, 0 rows affected (0.00 sec)
[root@kenyon ~]# mysql -uusr_kenyon -p
mysql> prompt mysql80>
PROMPT set to "mysql80>"
mysql80> alter user usr_kenyon@localhost identified by "456123";
ERROR 3892 (HY000): Current password needs to be specified in the REPLACE clause in order to change it.
mysql80> alter user usr_kenyon@localhost identified by "456123" replace "123456";
Query OK, 0 rows affected (0.02 sec)
--普通用户没有权限动态修改这些参数
mysql80>set persist password_history = 2;ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER or SYSTEM_VARIABLES_ADMIN privilege(s) for this operation