MySQL Password Validation Plugin system variables.

As you might have noticed, you will be prompted to enable "VALIDATE   PASSWORD" component while setting up password for MySQL root user. If enabled, the Validate Password component will automatically check the strength of the given password and enforces the users to set only those passwords which are secure enough. If you provide a weak password, you will encounter with an error like - "ERROR 1819 (HY000): Your password does not satisfy the current policy requirements.". Technically speaking, it is not actually an error. This is a built-in security mechanism that notifies the users to provide only the strong passwords based on the current password policy requirements.

Let's log in to MySQL server as root user using command:

# mysql -u root -p

Create a database user with a weak password:

mysql> create user 'testuser'@'localhost' identified by 'newpassword';

On execution MySQL shows the following error:

ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

The Validate Password components doesn't allow to create a user with a weak password. You will keep getting this error until the password meets the requirements of the current password policy or you disable the Validate Password component.

There are three levels of password validation policy enforced when Validate Password is enabled:

• LOW Length >= 8 characters.
• MEDIUM Length >= 8, numeric, mixed case, and special characters.
• STRONG Length >= 8, numeric, mixed case, special characters and dictionary file.

Based on these policy levels, you need to set an appropriate password. For example, if the password validation policy is set to Medium, you must set a password that has at least 8 characters including a number, lowercase, uppercase and special characters.

First we need to  find the current password policy level. To do so, run the following command to show Password Validation Plugin system variables:

 

mysql> SHOW VARIABLES LIKE 'validate_password%';

will show output like this:

+--------------------------------------+--------+
| Variable_name                        | Value  |
+--------------------------------------+--------+
| validate_password.check_user_name    | ON     |
| validate_password.dictionary_file    |        |
| validate_password.length             | 8      |
| validate_password.mixed_case_count   | 1      |
| validate_password.number_count       | 1      |
| validate_password.policy             | MEDIUM |
| validate_password.special_char_count | 1      |
+--------------------------------------+--------+
7 rows in set (0.09 sec) 

You can change the values of  password validation plugin system variables using SET GLOBAL command.

 

To lower level password policy:

mysql> SET GLOBAL validate_password.policy = 0;

or

mysql> SET GLOBAL validate_password.policy=LOW;

You can even change the password length as per your requirement. For that type the following command:

  
mysql> SET GLOBAL validate_password.length=5;