MySQL Password Policy

I provisioned a local Vagrant VM with MySQL installed. I tried to check the password policy by executing the following statement.

SHOW VARIABLES LIKE 'validate_password.%';

But I got the following error.

ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

Hmm I was going to just use the default password but I guess I have no choice. I am changing the root password now with the following statement. After executing sudo mysql, I changed my root password.

ALTER USER 'root'@'localhost' IDENTIFIED BY 'Nyaaa!@#$%1234';

Flush the privilege.

FLUSH PRIVILEGES;

Now exit mysql console.

\q

Now change the password in /root/.my.cnf and you are good to go! Now I am executing the password policy check.

Now I see the following.

+--------------------------------------+--------+
| 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      |
+--------------------------------------+--------+

Before you execute ALTER statement to change password, you can alternatively check if the password you are thinking of changing to is going to work by executing the following.

SELECT VALIDATE_PASSWORD_STRENGTH('weak');

Now you get an output like this indicating the score of the password you specified in the statement above.

+------------------------------------+
| VALIDATE_PASSWORD_STRENGTH('weak') |
+------------------------------------+
|                                 25 |
+------------------------------------+
1 row in set (0.00 sec)

To get the score of 100%, it has to be quite strong.

mysql> SELECT VALIDATE_PASSWORD_STRENGTH('12SDFsdfs^*&^');
+---------------------------------------------+
| VALIDATE_PASSWORD_STRENGTH('12SDFsdfs^*&^') |
+---------------------------------------------+
|                                         100 |
+---------------------------------------------+
1 row in set (0.00 sec)

Leave a Reply

Your email address will not be published.