MySQL User Grant and Password Policy Reset

MySQL User Grant and Password Policy Reset.md

Background story

It is a common scenario that we often forget out database password. When we try to log in to our mysql DB, we face the following error message. If we use a password:

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

If we don't use a password:

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

In this case we need to reset the password to access our mysql databases (atttention: only for ethical purpose).

To forcibly access the mysql shell, follow the below steps.

1. Open and edit /etc/my.conf or /etc/mysql/my.conf depending on your distro. In my case, I edited it with vim.

$ sudo vim /etc/mysql/my.cnf

2. Add skip-grant-tables under [mysqld]
3. Restart Mysql with command:

$ sudo service mysqld restart

4. You should be able to login to mysql now using the below command mysql -u root -p

In case step 4 doesn't work it may give the following error:

Access denied for user 'root'@'localhost'

To log in to the server with system admin privilage, you can also run command:

$ sudo mysql

5. Run mysql> flush privileges;
6. If you want to see the users with username root, use the following command:

mysql> select host, user, authentication_string from mysql.user;

7. Try to set new password by mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'NewPassword'; or
   mysql> UPDATE mysql.user SET authentication_string='NewPassword' WHERE User='root'; [1]
8. Go back to /etc/mysql/my.cnf and remove/comment skip-grant-tables that you added earlier in step 2.
9. Restart Mysql
10. Now you will be able to login with the new password using the command mysql -u root -p

[1] If your mysql server has strong password policy, you may see the following message when you try to execute step 7.

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

In this case, follow the below steps.

• For checking the current variables for validating the password you should run the following command.

mysql> SHOW VARIABLES LIKE 'validate_password%';

• Validate_password is a variable that is used to inform the server about the validate_password plugin. This plugin tests the passwords and improve security. Following output will be displayed, if you run the above command.

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

• If you want to enable setting a weak password, you have to change the values of few global variables shown above by running the following commands.

SET GLOBAL validate_password.length=0;
SET GLOBAL validate_password.mixed_case_count=0;
SET GLOBAL validate_password.number_count=0;
SET GLOBAL validate_password.special_char_count=0;
SET GLOBAL validate_password.policy=LOW;

Keep in mind that these changes are temporary. If you want these variables to have the values set permanently, you need to change these values in /etc/my.conf file. To do that go to /etc/my.conf and add the following lines.

validate_password.policy=LOW
validate_password.length=0
validate_password.mixed_case_count=0
validate_password.number_count=0
validate_password.special_char_count=0

• Now restart MySQL and try to execute the command in link 6.

If you have done everything successfully, hopefully you have become successful to reset the mysql password.