Author: Sri Sakthivel
Original link: https://www.percona.com/blog/enhanced-password-management-systems-in-mysql-8-part-1
MySQL 8.0 has many improvements in password management. This article will introduce the following two features.
- Password reuse strategy
- Generate random password
|1 password reuse strategy
This policy is simply to restrict and prohibit the use of used passwords when setting new passwords. There are two strategies:
- History 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 a user and add the condition password history 2. The last two historical passwords cannot be reused.
mysql> create user 'herc'@'localhost' identified by 'Percona@321' 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 will be in mysql.password_ Record the password change information on the history 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 herc@localhost Your password.
mysql> alter user 'herc'@'localhost' identified by 'MySQL@321';
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, view mysql.password_history table. 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( Percona@321 ).
mysql> alter user 'herc'@'localhost' identified by 'Percona@321'; ERROR 3638 (HY000): Cannot use these credentials for 'herc@localhost' because they contradict the password history policy
Modification failed! Because according to the restriction policy we set, it cannot be reused in mysql.password_ The last two passwords recorded in the policy table. Therefore, if you want to reuse the first password again, you cannot make the password appear in mysql.password_policy table.
After setting a new password (the first password is not the last two passwords), try to use the first password value( Percona@321 )Modify successfully!
mysql> alter user 'herc'@'localhost' identified by 'Herc@321'; Query OK, 0 rows affected (0.01 sec) mysql> alter user 'herc'@'localhost' identified by 'Percona@321'; Query OK, 0 rows affected (0.02 sec)
You can also configure password globally at startup_ 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, a user was created sri@localhost And set the time interval for user password reuse to five days.
mysql> create user 'sri'@'localhost' identified by 'Percona@321' 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 'Herc@321';
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 setting it to the first password again.
mysql> alter user 'sri'@'localhost' identified by 'Percona@321'; ERROR 3638 (HY000): Cannot use these credentials for 'sri@localhost' because they contradict the password history policy
Setting failed!
You can also configure password globally at startup_ 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
Starting with MySQL 8.0.18, MySQL can create random passwords for user accounts. This means that you do not have to assign a specified password. It supports the following statements:
- Create user
- Change user
- Set password
We need to use RANDOM PASSWORD to avoid clear text 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)
The password hash value will be stored in the mysql.user table.
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 the variable generated_random_password_length defines the password length. The allowable length range is 5 to 255.
mysql> select @@generated_random_password_length; +------------------------------------+ | @@generated_random_password_length | +------------------------------------+ | 20 | +------------------------------------+ 1 row in set (0.00 sec)
If validate_ The password component has been installed and will not affect the random password policy.
There are also some features that will be introduced in the next article.
reference resources
MySQL Manual:
-
https://dev.mysql.com/doc/mysql-security-excerpt/8.0/en/password-management.html
-
https://dev.mysql.com/doc/refman/8.0/en/password-management.html#random-password-generation
This article is composed of blog one article multi posting platform OpenWrite release!