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

When i connect MySQL database 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.

The reason for this issue is because you do not grant any global privileges to the user. So after you grant 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 tool such as MySQL Workbench to manage MySQL database then follow below steps.

2.1.1 Add Global Privileges.
  1. Open MySQL Workbench, click Users and Privileges menu item in left panel. Click the user account name in middle panel, then click Administrative Roles tab in right panel.
    add mysql database user account and assign privileges
  2. Then check SELECT checkbox in the Global Privileges area.
  3. Click Apply button to save the changes.
2.1.2 Add Schema Privileges.
  1. If you want to execute sql command on 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 left panel, then click the user account in middle panel, then click Schema Privileges tab in right panel.
    add schema privileges to user account in mysql
  3. Click Add Entry button to open schema selection popup window to select wanted schema. Then select related privileges checkbox ( for example : SELECT, INSERT,UPDATE and DELETE checkbox) to assign the privileges to the user account. Click Apply button to save the changes.
READ :   JDBC Statement Example - Batch Insert, Update, Delete

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 below steps.

2.2.1 Grant Global Privileges To User Account By Command Line.
  1. Open a terminal and run 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)

     

 

1 Comment

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.