MySQL ROOT User Change Password Issue Fix & Password Validation Plugin

MySQL ROOT User Change Password Issue Fix

New MySQL Password Validation Plugin has default requirements for UPPERCASE, lowercase and Special Non-AlphaNumeric Characters (example: _-+=$%#@!* etc.) symbols for increased security. If you updated your server (Ubuntu/Debian/CentOS/…) regularly and gradually from older MySQL versions, it will probably not affect you/your server (e.g. it will not prompt you to update your good old database(s) management password(s) with new more restrictive rules), and you will be completely unaware of it!

However, when you later rebuild your server from scratch, including latest MySQL server version, and try to change password for ‘root’@’localhost’ user, those StackOverflow and ServerFault questions/answers might NOT always be able to help you! (see [1], [2], [3])

Why? Well, because, now your old password must meet the new password validation policy plugin’s requirements, and you need to DETERMINE those requirements FIRST and ADAPT them to suite your OWN password!

mysql> ALTER USER ‘root’@’localhost’ IDENTIFIED WITH mysql_native_password BY ‘mygoodoldpassword123‘; [ENTER]
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

Because, you can try to change the default blank/no password all day long to the rest of your life, you will not succeed IF you use only lowercase + numbers type of passwords. In case you do use MiXeD uppercase/lowercase letters, at least one “special” (uncommon) character and number(s), then you are good. But, not all of us have that exhausting habit! :)

Ok, let’s get to the business.

NOTE:
[ENTER] indicates that you must hit “ENTER” key on your keyboard, not actually type it!

STEP 1:
Determine the policy rules which are in effect:

root@server: mysql -u root -p [ENTER]
mysql> SHOW VARIABLES LIKE ‘validate_password%’; [ENTER]

EXAMPLE RESULT:

+--------------------------------------+--------+
| Variable_name                        | Value  |
+--------------------------------------+--------+
| validate_password_check_user_name    | OFF    |
| validate_password_dictionary_file    |        |
| validate_password_length             | 9      |
| validate_password_mixed_case_count   | 1      |
| validate_password_number_count       | 6      |
| validate_password_policy             | MEDIUM |
| validate_password_special_char_count | 1      |
+--------------------------------------+--------+

This means that our new password MUST:

1. have at least 9 characters in total length
2. have at least 1 UPPERCASE and 1 lowercase letter
3. have at least 1 special character symbol (!@#$%^&*…)

At this point, you may stop reading this and create a password that meets above requirements. In fact, these requirements are for your own good and (theoretically) work in your favor. After all, passwords should be as strong as possible and as hard to guess as if the world will end.

However, if you work on a daily basis with lots of projects and stuff, it is not always practical. So, we need to tweak and relax these rules a bit, in order to make our traditional password acceptable. Read on!

STEP 2:

Let’s disable UPPERCASE/lowercase and SPECIAL CHARACTER nonsense (ok, it is not… but it’s painful):

mysql> SET GLOBAL validate_password_mixed_case_count = 0; [ENTER]
Query OK, 0 rows affected (0.00 sec)

mysql> SET GLOBAL validate_password_special_char_count = 0; [ENTER]
Query OK, 0 rows affected (0.00 sec)

Let’s check our new rules:

mysql> SHOW VARIABLES LIKE ‘validate_password%’; [ENTER]

EXAMPLE RESULT:

+--------------------------------------+--------+
| Variable_name                        | Value  |
+--------------------------------------+--------+
| validate_password_check_user_name    | OFF    |
| validate_password_dictionary_file    |        |
| validate_password_length             | 9      |
| validate_password_mixed_case_count   | 0      |
| validate_password_number_count       | 6      |
| validate_password_policy             | MEDIUM |
| validate_password_special_char_count | 0      |
+--------------------------------------+--------+

So far – so good!

STEP 3:

mysql> FLUSH PRIVILEGES; [ENTER]
Query OK, 0 rows affected (0.00 sec)

STEP 4:

Let’s actually set new password:

mysql> ALTER USER ‘root’@’localhost’ IDENTIFIED WITH mysql_native_password BY ‘mygoodoldpassword123‘; [ENTER]
Query OK, 0 rows affected (0.00 sec)

mysql> exit [ENTER]

STEP 5:

Let’s verify that it is working:

root@server: mysql -u root -p [ENTER]
Enter password: [ENTER] (to leave blank just hit ENTER or improvise fake password if you like)

Ooops! Error is returned – MySQL has denied our login request:

ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: NO)