The 8.0 version of mysql adds the functions related to password policy management. The new version can do more control and operation on account password requirements, such as setting reusable restrictions on passwords, modifying password verification policies, etc. the security has been improved compared with the 5.7 version. Let's look at it together.

 

mysql> show variables like 'password%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| password_history         | 0     |
| password_require_current | OFF   |
| password_reuse_interval  | 0     |
+--------------------------+-------+
3 rows in set (0.00 sec)

Password "History: there is no duplicate password in the last few times saved by each user. The default value is 0, which means there is no limit
Password "reuse" interval: the password saved by each user will not be repeated in recent days. The default value is 0, which means there is no limit
Password "require" current: whether to enter the current password when modifying a new password. The default value is OFF, which means it is OFF

 

 

--Set the number of old password history per user
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)

--The password history table contains the user's initial password creation information
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)

--First password change
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)

--The second time, if the old password is used, an error will be reported and the current password policy will be violated
mysql> alter user kenyon identified by '1aaa';
ERROR 3638 (HY000): Cannot use these credentials for 'kenyon@%' because they contradict the password history policy

--If it is feasible to use the new password for the second time, the oldest record data in the password record table will be cleared at the same time. Because the number of global password records set by the user is 2, please pay attention to the modification time
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)

--Test other user impact
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)

--Delete a user's information
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)

--The old password can be modified successfully by deleting the relevant data in the password record table
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. Verify mode before modifying new password

--Can be changed online without rebooting:
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)

--Ordinary users do not have permission to modify these parameters dynamically
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

3, Summary:

1. In the new version, the detection mechanism of old password is added. It is not allowed to use the old password in the password record table to prevent the reuse of the old password in the near future

2. Support the old password when changing the new password, which can prevent the user password from being tampered with maliciously

3. Modifying the new password authentication policy is only valid for ordinary users. root or users with system variable management permission are not restricted

4. Delete user information, and delete the old password record information of the user

 

https://dev.mysql.com/doc/refman/8.0/en/password-management.html