MySQL 8.0 password management strategy (I)

Time:2022-6-7

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 policy

Modification 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 policy

Setting 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:

Recommended Today

CommunityToolkit.Mvvm-IOC

CommunityToolkit.Mvvm does not have IOC built in, you can use Microsoft.Extensions.DependencyInjection. Register ViewModel and other services in App public partial class App : Application { public App() { Services = ConfigureServices(); this.InitializeComponent(); } public new static App Current => (App)Application.Current; public IServiceProvider Services { get; } private IServiceProvider ConfigureServices() { var sc = new ServiceCollection(); […]