This article continues to introduce the new password authentication strategy of MySQL 8.0.

Suppose there are such requirements: the administrator creates two users, whose responsibilities are development and operation and maintenance, and requires these two users to meet the following requirements,

1. The development user requires to change the password regularly, and the password cannot overlap with the recently changed password, that is, the historical password cannot be reused. Here, the number of historical passwords is limited to 3;
2. The operation and maintenance user also requires to change the password regularly, and the password cannot overlap with the password changed in a certain period of time, that is, the historical password cannot be reused. The time limit here is one week.

The above two password change requirements cannot be realized on the database side temporarily. You can only take a "small book to remember the number of historical passwords and the number of days of historical passwords". Before the user changes the password each time, check whether there is a historical password overlapping the new password on the small book.

MySQL 8.0 implements the above two password change requirements directly from the database side, and users can throw away the "little book".

Let me explain the specific implementation of the above password change requirements in MySQL 8.0 in two parts.

First, write global parameters in the configuration file

Parameter password_history indicates the number of password retention times recently used;

Parameter password_reuse_interval indicates the number of days the password was last used.

First, meet the needs of development users: keep the number of historical passwords as 3.

Log in as an administrator user and set global parameters:

mysql:(none)>set persist password_history=3;
Query OK, 0 rows affected (0.00 sec)

Exit reconnection and create user ytt_dev :

root@ytt-ubuntu:/home/ytt# mysql -S /opt/mysql/mysqld.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 33
Server version: 8.0.27 MySQL Community Server - GPL

...

mysql:(none)>create user ytt_dev identified by 'root123';
Query OK, 0 rows affected (0.15 sec)

Exit connection, user ytt_dev reconnects to the database and changes the password twice:

root@ytt-ubuntu:/home/ytt# mysql -uytt_dev -hytt-ubuntu -proot123
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 34
Server version: 8.0.27 MySQL Community Server - GPL

...

mysql:(none)>
mysql:(none)>alter user ytt_dev identified by 'root456';
Query OK, 0 rows affected (0.03 sec)

mysql:(none)>alter user ytt_dev identified by 'root789';
Query OK, 0 rows affected (0.17 sec)

Add the original password, that is, change the password three times, and then change the password again. At this time, it is not allowed to change the password. The error prompt conflicts with the password history policy:

mysql:(none)>alter user ytt_dev identified by 'root123';
ERROR 3638 (HY000): Cannot use these credentials for 'ytt_dev@%' because they contradict the password history policy

Next, select a new password that does not conflict with the historical password to modify. At this time, the password is modified successfully:

mysql:(none)>alter user ytt_dev identified by 'rootnew';
Query OK, 0 rows affected (0.04 sec)

Then realize the needs of operation and maintenance users: keep the password for 7 days.

Similarly, the administrator user logs in to MySQL and sets the global parameters:

mysql:(none)>set persist password_reuse_interval = 7;
Query OK, 0 rows affected (0.00 sec)

mysql:(none)>set persist password_history=default;
Query OK, 0 rows affected (0.00 sec)

Exit and reconnect to create O & M user ytt_dba :

mysql:(none)>create user ytt_dba identified by 'root123';
Query OK, 0 rows affected (0.01 sec)

mysql:(none)>\q
Bye

As user ytt_dba logs into the database and changes the password five times:

root@ytt-ubuntu:/home/ytt# mysql -uytt_dba -hytt-ubuntu -proot123
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 39
Server version: 8.0.27 MySQL Community Server - GPL

...

mysql:(none)>alter user ytt_dba identified by 'root456';
Query OK, 0 rows affected (0.15 sec)

mysql:(none)>alter user ytt_dba identified by 'root789';
Query OK, 0 rows affected (0.08 sec)

mysql:(none)>alter user ytt_dba identified by 'root000';
Query OK, 0 rows affected (0.02 sec)

mysql:(none)>alter user ytt_dba identified by 'root888';
Query OK, 0 rows affected (0.02 sec)

mysql:(none)>alter user ytt_dba identified by 'root999';
Query OK, 0 rows affected (0.12 sec)

Next, verify the historical password verification policy. Because we have set the number of days to keep the password history, any historical password within the set time can not be used as a new password: MySQL refuses the user to change the password, and the error prompt conflicts with the password history policy:

mysql:(none)>alter user ytt_dba identified by 'root123';
ERROR 3638 (HY000): Cannot use these credentials for 'ytt_dba@%' because they contradict the password history policy
mysql:(none)>alter user ytt_dba identified by 'root456';
ERROR 3638 (HY000): Cannot use these credentials for 'ytt_dba@%' because they contradict the password history policy
mysql:(none)>

Select a new password that has not been changed recently, and the password is changed successfully:

mysql:(none)>alter user ytt_dba identified by 'rootnew';
Query OK, 0 rows affected (0.10 sec)

If a user needs to meet the password restrictions of development users and operation and maintenance users at the same time, two global parameters can be modified together: the number of historical passwords is 7 days and the number of historical passwords is 3 times.

mysql:(none)>set persist password_reuse_interval = 7;
Query OK, 0 rows affected (0.00 sec)

mysql:(none)>set persist password_history=3;
Query OK, 0 rows affected (0.00 sec)

Second, administrators can define password authentication policies for individual users when creating users or changing user attributes

Reset the global parameters to the default, that is, turn off the password authentication policy:

mysql:(none)>set persist password_reuse_interval = default;
Query OK, 0 rows affected (0.00 sec)

mysql:(none)>set persist password_history=default;
Query OK, 0 rows affected (0.00 sec)

The administrator exits the connection and re enters, creating two users ytt_dev1 and ytt_dba1 :

mysql:(none)>create user ytt_dev1 identified by 'root123';
Query OK, 0 rows affected (0.04 sec)

mysql:(none)>create user ytt_dba1 identified by 'root123';
Query OK, 0 rows affected (0.02 sec)

Change the password history retention policy for two users:

mysql:(none)>alter user ytt_dev1 password history 3;
Query OK, 0 rows affected (0.01 sec)

mysql:(none)>alter user ytt_dba1 password reuse interval 7 day;
Query OK, 0 rows affected (0.02 sec)

Retrieve mysql User table to see if the change is successful:

mysql:(none)>select user,password_reuse_history,password_reuse_time from mysql.user where password_reuse_history is not null or password_reuse_time is not null;
+----------+------------------------+---------------------+
| user     | password_reuse_history | password_reuse_time |
+----------+------------------------+---------------------+
| ytt_dba1 |                   NULL |                   7 |
| ytt_dev1 |                      3 |                NULL |
+----------+------------------------+---------------------+
2 rows in set (0.00 sec)

The specific verification method is similar to the global parameter setting part, which is omitted here.

Summary:

The historical password authentication strategy launched by MySQL 8.0 is another new improvement to the user password security mechanism, which can save the cumbersome implementation of such requirements on the non database side.