MySQL 8.0.34 brings us a new password validation parameter. Using this, we can control the minimum number of characters in a password that a user must change before validate_password accepts a new password for the user’s account.
In this blog, I offer a few scenarios showing how the parameter validate_password.changed_characters_percentage affects user password changes.
Requirement
To make this work, we should enable the “Password Verification-Required Policy” (introduced in MySQL 8.0.13). We can allow it to GLOBALLY by using the parameter “password_require_current” or by specifying “PASSWORD REQUIRE CURRENT” while creating or altering the user. This topic was already explained very well by Brain Sumpter in his post, MySQL 8: Password Verification Policy. I recommend you read it to get to know more about the “Password Verification-Required policy.” In my case, I just enabled the parameter “password_require_current” to enforce the “Password Verification-Required Policy” globally.
1 2 3 4 5 6 7 8 9 10 | percona labs MySQL 8.0.34 > set persist password_require_current = 1; Query OK, 0 rows affected (0.05 sec) percona labs MySQL 8.0.34 > select @@password_require_current; +----------------------------+ | @@password_require_current | +----------------------------+ | 1 | +----------------------------+ 1 row in set (0.00 sec) |
Once we enable the “password_require_corrent” option, we should provide the old password in the REPLACE clause. Otherwise, it will not allow you to change the password. You will get the following error:
1 2 | percona labs MySQL 8.0.34 > alter user 'test'@'localhost' identified by 'Test@321'; ERROR 3892 (HY000): Current password needs to be specified in the REPLACE clause in order to change it. |
Note: The user with global CREATE USER and UPDATE privileges on “mysql” system database can still change the password without specifying the current password.
Creating a test environment
I have installed the MySQL 8.0.34 version in my test server and installed the “validate_password” component.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | percona labs MySQL 8.0.34 > select @@version, @@version_comment; +-----------+------------------------------+ | @@version | @@version_comment | +-----------+------------------------------+ | 8.0.34 | MySQL Community Server - GPL | +-----------+------------------------------+ 1 row in set (0.00 sec) percona labs MySQL 8.0.34 > INSTALL COMPONENT 'file://component_validate_password'; Query OK, 0 rows affected (0.00 sec) percona labs MySQL 8.0.34 > select @@validate_password.changed_characters_percentage; +---------------------------------------------------+ | @@validate_password.changed_characters_percentage | +---------------------------------------------------+ | 0 | +---------------------------------------------------+ 1 row in set (0.00 sec) |
I recommend installing the “validate_password” component instead of the “validate_password” plugin (deprecated). You might not see this feature when installing it as a plugin.
Testing “changed_characters_percentage”
I have set the changed_characters_percentage value to 50. This means whenever the user tries to reset the password, the new password should not contain 50% of any of the old characters.
1 2 3 4 5 6 7 8 9 10 | percona labs MySQL 8.0.34 > set global validate_password.changed_characters_percentage=50; Query OK, 0 rows affected (0.00 sec) percona labs MySQL 8.0.34 > select @@validate_password.changed_characters_percentage; +---------------------------------------------------+ | @@validate_password.changed_characters_percentage | +---------------------------------------------------+ | 50 | +---------------------------------------------------+ 1 row in set (0.00 sec) |
Then, I created the user “percona1” with the password “Percona@321”.
1 2 3 4 5 6 7 8 | percona labs MySQL 8.0.34 > create user 'percona1'@'localhost' identified by 'Percona@321'; Query OK, 0 rows affected (0.00 sec) percona labs MySQL 8.0.34 > grant select on *.* to 'percona1'@'localhost'; Query OK, 0 rows affected (0.00 sec) percona labs MySQL 8.0.34 > flush privileges; Query OK, 0 rows affected (0.01 sec) |
Now, let’s try to change the password to “Percona@567”.
1 2 3 4 5 6 7 8 9 10 | percona labs MySQL 8.0.34 > select user(); +--------------------+ | user() | +--------------------+ | percona1@localhost | +--------------------+ 1 row in set (0.00 sec) percona labs MySQL 8.0.34 > alter user percona1@localhost identified by 'Percona@567' replace 'Percona@321'; ERROR 4165 (HY000): The new password must have at least '5' characters that are different from the old password. It has only '3' character(s) different. For this comparison, uppercase letters and lowercase letters are considered to be equal. |
It is not allowing me to change the password from “Percona@321” to “Percona@567” and the error explains the situation pretty clearly. I have a password with 11 characters and my new password only has three character differences ( Percona@321 to Percona@567 ). As per my “changed_characters_percentage” value, the new password should contain 50% new characters. This means my new password should have at least five different characters. So, the new password does not meet the requirement.
Now, let’s try with another new password, “Percona%#567”. It has five characters different from the previous password.
1 2 | percona labs MySQL 8.0.34 > alter user percona1@localhost identified by 'Percona%#567' replace 'Percona@321'; Query OK, 0 rows affected (0.01 sec) |
It works as it meets the requirement!
How does it work with UPPER/LOWER case letters?
To explain this situation, I have created another user, “percona2” with the password “PERCONa@321”.
1 2 | percona labs MySQL 8.0.34 > create user 'percona2'@'localhost' identified by 'PERCONa@321'; Query OK, 0 rows affected (0.00 sec) |
The password has 11 characters. So, we have to make at least five character changes in the new password. I will update the password from “PERCONa@321” to “perconA@321”. In this case, I will change seven characters from upper-lower and lower-upper cases.
1 2 | percona labs MySQL 8.0.34 > alter user percona2@localhost identified by 'perconA@321' replace 'PERCONa@321'; ERROR 4165 (HY000): The new password must have at least '5' characters that are different from the old password. It has only '0' character(s) different. For this comparison, uppercase letters and lowercase letters are considered to be equal. |
Not working. It cannot change because the UPPER and LOWER case letters are considered equal.
How does it work with different character counts?
To test this scenario, I have created a user “percona3” with the password “Percona@321”. We can test the following scenarios.
- More existing characters
- More non-existing characters
More existing characters
To test this, I will change the password from “Percona@321” to “Percona@3213333333”. (Just adding seven “3” characters in the existing password ).
1 2 3 4 5 6 7 8 9 10 | percona labs MySQL 8.0.34 > select user(); +--------------------+ | user() | +--------------------+ | percona3@localhost | +--------------------+ 1 row in set (0.00 sec) percona labs MySQL 8.0.34 > alter user percona3@localhost identified by 'Percona@3213333333' replace 'Percona@321'; ERROR 4165 (HY000): The new password must have at least '5' characters that are different from the old password. It has only '0' character(s) different. For this comparison, uppercase letters and lowercase letters are considered to be equal. |
The error reports “0” characters difference because we added seven new characters in the password. But, the character (3) already exists in the password “Percona@3213333333”. In this case, it is not acceptable.
More non-existing characters
To test this, I am now changing the password from “Percona@321” to “Percona@3214455667788”. So, in this case, I am adding ten new characters to the existing password. But, I have five non-existing characters (4,5,6,7,8).
1 2 | percona labs MySQL 8.0.34 > alter user percona3@localhost identified by 'Percona@3214455667788' replace 'Percona@321'; Query OK, 0 rows affected (0.01 sec) |
It works!
So, from the above two examples, the password length may differ. But, it should meet the percentage of the changed characters.
Conclusion
MySQL 8 has a lot of security improvements and new implementations, and I will say this feature is really nice to improve password validations and add more security when changing the users’ passwords.
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!
Try Percona Distribution for MySQL today!
