4

Enhanced Password Management Systems in MySQL 8: Part 1

 2 years ago
source link: https://www.percona.com/blog/enhanced-password-management-systems-in-mysql-8-part-1/
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
neoserver,ios ssh client

Enhanced Password Management Systems in MySQLMySQL 8 comes with a lot of good features, and recently I explored its password management systems. I wanted to put together a series of blogs about it, and this is the first part. In this post, I am going to explain the following topics in detail.

  • Password Reuse Policy
  • Random Password Generation

Password Reuse Policy

MySQL has implemented restrictions on reusing passwords. Restriction can be established in two ways:

  • Number of password changes
  • Time elapsed

Number of Password Changes

From the MySQL documents:

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.

To test this, in my local environment I have created the user with “number of password changes = 2”.

Shell
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)

Here “password history 2” will define the number of password changes. MySQL will track the password changes on the table “mysql.password_history”.

Shell
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)

Now, I am going to change the password for the account “herc@localhost”.

Shell
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)

It worked. After changing the password, I verified the “mysql.password_history” table. Now, the table has the track of the last two passwords.

Now, I am going to change the password for the account “herc@localhost” again. This time, I am going to assign the same password which was assigned during the user creation “Percona@321”.

Shell
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

It doesn’t work; I am not able to reuse the first password. Because as per my reuse policy, I can’t reuse the last two passwords and they are being tracked in the “mysql.password_policy” table. So, in my case, if I want to reuse my first password again, then it cannot be in that list.

So I assigned a different password. Now, my first password is removed from the list of the last two passwords) and I tried to assign the first password.

Shell
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)

This is working now. This is the way you can restrict the reuse of the passwords based on the number of password changes.

This can be implemented globally and during the startup for all the users using the variable “password_history”.

Shell
#vi my.cnf
[mysqld]
password_history=6
#set global
mysql> set global password_history=5;
Query OK, 0 rows affected (0.00 sec)

Password Reuse Policy Based on Time Elapsed

From the MySQL document:

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.

To test this in my local environment, I have created the user “sri@localhost” with a password reuse interval of five days.

Shell
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)

So, this means for five days, I can’t reuse the password for the account “sri@localhost”.

Shell
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)

Now, I am going to do the ALTER to change the password.

Shell
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)

It is working. But, if I am going to reuse any of those passwords, based on the reuse policy, it will not be allowed for five days. Let me try with the first password now.

Shell
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

It gives the error as expected. This restriction can be implemented globally and during startup for all the users using the variable “password_reuse_interval”.

Shell
#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)

Random Password Generation

From MySQL 8.0.18, MySQL has the capability of creating random passwords for user accounts. This means we don’t need to assign the passwords and MySQL will take care of it. It has the support for the following statements:

  • CREATE USER
  • ALTER USER
  • SET PASSWORD

We need to use the “RANDOM PASSWORD” instead of providing the password text, and the password will be displayed on the screen during the creation.

For example:

Shell
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 hashes will be stored in the “mysql.user” table.

Shell
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 length is 20 characters based on the variable “generated_random_password_length”. We can define the password length using that variable. and the allowed length is 5 to 255.

Shell
mysql> select @@generated_random_password_length;
+------------------------------------+
| @@generated_random_password_length |
+------------------------------------+
+------------------------------------+
1 row in set (0.00 sec)

The random passwords will not mind the “validate_password” policy if the component is implemented in MySQL.

Hopefully, this blog will be helpful for you to learn about the password reuse policy and random passwords in MySQL 8. There are a few more features to go over, which will be covered in the next part of the blog series. Stay tuned!

Percona Distribution for MySQL is the most complete, stable, scalable, and secure, open-source MySQL solution available, delivering enterprise-grade database environments for your most critical business applications… and it’s free to use!

Download Percona Distribution for MySQL Today


report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK