MySQL 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”.
1 2 3 4 5 6 7 8 9 | 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”.
1 2 3 4 5 6 7 | 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”.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | mysql> alter user 'herc'@'localhost' identified by 'MySQL@321'; Query OK, 0 rows affected (0.02 sec) mysql> select * from mysql.password_historyG *************************** 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”.
1 2 | 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.
1 2 3 4 5 | 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”.
1 2 3 4 5 6 7 | #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.
1 2 3 4 5 6 7 8 9 | 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”.
1 2 3 4 5 6 7 8 | 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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | 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.
1 2 | 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”.
1 2 3 4 5 6 7 | #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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | 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.
1 2 3 4 5 6 7 8 9 | 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.
1 2 3 4 5 6 7 | mysql> select @@generated_random_password_length; +------------------------------------+ | @@generated_random_password_length | +------------------------------------+ | 20 | +------------------------------------+ 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!
This is an interesting feature as the DBA can educate other database users to create new passwords without repeating the last ones and create a new habit. As shown on the blog, we can think of having even a prohibitive repetition for the previous X passwords already used, or even the same password cannot be adopted for some time.
One quick topic that I had on my mind while reading the blog was that, can the password generator somewhat interact with the Password Validation Plugin if it’s set up to generate passwords not only based on length (enerated_random_password_length)?
The mentioned plugin can give a password weak or strong, even given it more muscles based on how you validate a password. If we think that a password will be generated so that the plugin accepts it, user creation will be easier.
Making sense or not, this is what I got reading further the manual:
If the validate_password component is installed, the policy that it implements does not affect generated passwords. (The purpose of password validation is to help humans create better passwords.)
By the way, congrats, Sri!
this is really insightful , this is core power of any DBMS should be implemented with caution.
thank you!!