mysql8 关闭密码策略_mysql8的新增密码策略

--设置每个用户的旧密码历史记录数

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)