Author: Sri sakthivel
Original link:https://www.percona.com/blog/…
MySQL 8.0 has many improvements in password management. This article will introduce the following two features.
- Password reuse policy
- Generate random password
|1 password reuse policy
This policy simply means that when setting a new password, you can restrict and prohibit the use of the used password. There are two strategies:
- Historical password_ history
- Interval password_ reuse_ interval
1.1 historical password
MySQL official manual Description:
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.
In the experimental environment, create users and add conditionspassword history 2, the last two historical passwords cannot be reused.
mysql> create user 'herc'@'localhost' identified by '[email protected]' 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 willmysql.password_historyRecord password change information on the table.
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)Next we try to change the user[email protected]Password for.
mysql> alter user 'herc'@'localhost' identified by '[email protected]';
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)After the change is successful, viewmysql.password_historyTable. It can be seen that this table contains the information of the last two passwords.
Change the user password again, and the password is the password value set when creating the user（ [email protected] ）。
mysql> alter user 'herc'@'localhost' identified by '[email protected]';
ERROR 3638 (HY000): Cannot use these credentials for '[email protected]' because they contradict the password history policyModification failed!Because according to the restriction policy we set, themysql.password_policyThe last two passwords recorded in the table. Therefore, if you want to reuse the first password again, you cannot make it appear in themysql.password_policyTable.
After setting a new password (the first password is not the last two), try to use the first password value（ [email protected] ）Make modifications,Modification succeeded!
mysql> alter user 'herc'@'localhost' identified by '[email protected]';
Query OK, 0 rows affected (0.01 sec)
mysql> alter user 'herc'@'localhost' identified by '[email protected]';
Query OK, 0 rows affected (0.02 sec)It can also be configured globally at startuppassword_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 interval
MySQL official manual Description:
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.
Before testing, the user is created[email protected]And set the time interval for user password reuse to five days.
mysql> create user 'sri'@'localhost' identified by '[email protected]' 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)This means that after each password takes effect, it can no longer be set repeatedly within five days.
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)Execute alter to change the password.
mysql> alter user 'sri'@'localhost' identified by '[email protected]';
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)Now try to set it to the first password again.
mysql> alter user 'sri'@'localhost' identified by '[email protected]';
ERROR 3638 (HY000): Cannot use these credentials for '[email protected]' because they contradict the password history policySetting failed!
It can also be configured globally at startuppassword_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 random password
Since MySQL 8.0.18, MySQL has been able to create random passwords for user accounts. This means that you do not have to assign the specified password. It supports the following statements:
- Create user
- Change user
- Set password
We need to useRANDOM PASSWORDTo avoid clear text display on the screen when changing the password. For example:
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)Password hash value will be stored inmysql.userTable.
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)By default, the password is 20 characters long. We can use variablesgenerated_random_password_lengthDefine the password length. The allowed length range is 5 to 255.
mysql> select @@generated_random_password_length;
+------------------------------------+
| @@generated_random_password_length |
+------------------------------------+
| 20 |
+------------------------------------+
1 row in set (0.00 sec)Ifvalidate_passwordThe component has been installed and will not affect the random password policy.
There are also some features that will be covered in the next article.
reference resources
MySQL Manual: