Access denied error in MySQL
Have you been frustrated with Access denied for user ‘chinnu’@’localhost’ errors even though as root you granted following privileges:
GRANT ALL PRIVILEGES ON MyDb.* to 'chinnu'@'%' WITH GRANT OPTION;
While % means any host, you probably did not see the behavior you expected.
MySQL performs access control in two stages. First, it tries to connect you with the username/password provided. Next, it checks if you have the privileges to perform your request. Entries in tables user, db and host of mysql database are used for access control.
Lets say this is our user table:
-------------------- Host | User -------------------- % | root % | chinnu localhost | root localhost | -------------------
% in Host means ‘any host’ and blank in User means any user or anonymous user. Note that the value % is less specific when compared to value localhost. MySQL, when performing access control, sorts this table with the most-specific Host at the top. So, we have something like
-------------------- Host | User -------------------- localhost | root localhost | % | chinnu % | root -------------------
When chinnu tries to login from localhost, there are two matching rows. Guess what, the first match wins. This means that chinnu is effectively logged in as an anonymous user. The anonymous user may not have privileges for MyDb, hence the access denied errors.
How to verify?
If you managed to connect execute SELECT CURRENT_USER(). If you get result as @localhost and not chinnu@localhost, you have the issue described above.
How to solve this?
Solution-1: Grant privileges for ‘chinnu’@’localhost’ in addition to ‘chinnu’@’%’
Solution-2: Just get rid of the anonymous user