MySQL validate_password_policy unknown system variable

blue picture blue · Mar 19, 2019 · Viewed 15.4k times · Source

I'm using MySQL 5.7.25 and i want to increase my MySQL password policy by doing this in MySQL command:

SET GLOBAL validate_password_policy=2;

But i always get an error:

ERROR 1193 (HY000): Unknown system variable 'validate_password_policy'

I tried to list the validate_password variable:

SHOW VARIABLES LIKE 'validate_password%'

but it always return empty set

Answer

Mohammad  Al-Tamimi picture Mohammad Al-Tamimi · Nov 1, 2019

This problem has happened because validate_password plugin is by default NOT activated. You can solve by these commands:

mysql> select plugin_name, plugin_status from information_schema.plugins where plugin_name like 'validate%';

Empty set (0.00 sec)

mysql> install plugin validate_password soname 'validate_password.so';

Query OK, 0 rows affected (0.02 sec)

mysql> select plugin_name, plugin_status from information_schema.plugins where plugin_name like 'validate%';

Finally, you will show the following:

+-------------------+---------------+
| plugin_name       | plugin_status |
+-------------------+---------------+
| validate_password | ACTIVE        |
+-------------------+---------------+
1 row in set (0.00 sec)

Then you can run your command:

mysql> SHOW VARIABLES LIKE 'validate_password%';

+--------------------------------------+--------+
| Variable_name                        | Value  |
+--------------------------------------+--------+
| validate_password_check_user_name    | OFF    |
| 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.00 sec).