Hello,
I had a weird behavior on some MySQL servers with validate_password plugin.
After mysql restart, the validate_password_policy go back to MEDIUM.
Server version: 5.7.24-0ubuntu0.18.04.1 (Ubuntu)
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 |
+--------------------------------------+--------+
mysql> set global validate_password_length=3;
Query OK, 0 rows affected (0.00 sec)
mysql> set global validate_password_policy=0; Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'validate_password%';
+--------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------+-------+
| validate_password_check_user_name | OFF |
| validate_password_dictionary_file | |
| validate_password_length | 4 |
| validate_password_mixed_case_count | 1 |
| validate_password_number_count | 1 |
| validate_password_policy | LOW |
| validate_password_special_char_count | 1 |
+--------------------------------------+-------+
7 rows in set (0.01 sec)
Confirmation on the errorlog :
2019-01-16T12:32:32.160144Z 10 [Warning] Plugin validate_password reported: 'Effective value of validate_password_length is changed. New value is 4'
(bug:
https://bugs.mysql.com/bug.php?id=81523)
mysql> create user test identified by 'testt';
Query OK, 0 rows affected (0.00 sec)
Until this point, the MySQL behavior is normal.
# sudo service mysql restart
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)
* I checked into the configuration files without finding any reference to validate_password
* I tried to add on /etc/mysql/mysql.conf.d/mysqld.cnf
validate_password_policy=LOW
validate_password_policy=0
validate_password.policy=0
I add an error on journalctl -xe:
[ERROR] unknown variable 'validate_password_policy=LOW'
* I tried to uninstall and install the plugin validate_password
* I re-execute mysql_secure_installation, but the script didn't ask me again about password policy
(First time, I executed the script like /usr/bin/mysql_secure_installation -D)
I reinstalled MySQL
Executed mysql_secure_installation
Securing the MySQL server deployment.
Enter password for user root:
VALIDATE PASSWORD PLUGIN can be used to test passwords
and improve security. It checks the strength of password
and allows the users to set only those passwords which are
secure enough. Would you like to setup VALIDATE PASSWORD plugin?
Press y|Y for Yes, any other key for No: yes
There are three levels of password validation policy:
LOW Length >= 8
MEDIUM Length >= 8, numeric, mixed case, and special characters
STRONG Length >= 8, numeric, mixed case, special characters and dictionary file
Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG: 0
Using existing password for root.
...
All done!
sudo service mysql restart
mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.24-0ubuntu0.18.04.1 (Ubuntu)
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
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 |
+--------------------------------------+--------+
Do you have any idea ?