How To Resolve Access Denied For User ‘root’@’localhost’ (using Password: Yes) When Connect MySQL Database

When I connect MySQL databases in source code, such as Java code or Python code. I encounter an error which I do not meet when I use MySQL client tool software to connect it. The error message is Access Denied For User ‘root’@’localhost’ (using Password: Yes). To resolve this issue is not hard, this example will tell you how to do it.

1. The Access Denied Reason.

1. The reason for this issue is because you do not grant any global privileges to the user.
2. So after you grant the select global privilege to the user, then the issue will be fixed.

2. How To Grant MySQL Database Select Privilege To The User.

2.1 Grant Privilege To User Through MySQL Workbench.

If you use MySQL client tools such as MySQL Workbench to manage MySQL database then follow the below steps.

2.1.1 Add Global Privileges.

1. Open MySQL Workbench, click the Users and Privileges menu item in the left panel.
2. Click the user account name in the middle panel, then click the Administrative Roles tab in the right panel.
3. Then check the SELECT checkbox in the Global Privileges area.
4. Click Apply button to save the changes.

2.1.2 Add Schema Privileges.

1. If you want to execute SQL command on a special schema database table, then you should add related schema privileges of the schema(database) to the user account.
2. Open MySQL Workbench, then click Users and Privileges menu item in the left panel, then click the user account in the middle panel, then click Schema Privileges tab in the right panel.
3. Click Add Entry button to open the schema selection popup window to select the wanted schema.
4. Then select the related privileges checkbox ( for example SELECT, INSERT, UPDATE, and DELETE checkbox) to assign the privileges to the user account.
5. Click the Apply button to save the changes.

2.2 Change User Privilege By Command-Line.

If you want to grant global or schema privileges to MySQL user account by command line, please follow the below steps.

2.2.1 Grant Global Privileges To User Account By Command-Line.

1. Open a terminal and run the below command.

   :~$ mysql -u root -p
   Enter password: 
   mysql> GRANT SELECT ON *.* TO 'jerry'@'localhost' WITH GRANT OPTION;
   Query OK, 0 rows affected (0.00 sec)
   

2.2.2 Grant Special Schema Privileges To User Account.

1. Open terminal run below command.

   :~$ mysql -u root -p
   Enter password: 
   
   mysql> GRANT SELECT,UPDATE,INSERT,DELETE ON dev2qa_example.* TO 'jerry'@'localhost';
   Query OK, 0 rows affected (0.00 sec)
   
   mysql> GRANT ALL PRIVILEGES ON dev2qa_example.* TO 'jerry'@'localhost';
   Query OK, 0 rows affected (0.00 sec)