MySQL user’s grant are not working – hidden permission in thesql.db

MySQL

I have a mysql user connecting from Server2 to Server1 over SSL (mysql 5.0.77).

Here is the grants for this user on Server1, (server2 has an ip in the range xx.xx.xx.%)

mysql> show grants for user@'xx.xx.xx.%';
+-------------------------------------------------------------------------------------------------------------------------------+ 
| Grants for user@xx.xx.xx.%                                      |
+-------------------------------------------------------------------------------------------------------------------------------+ 
| GRANT USAGE ON *.* TO 'user'@'xx.xx.xx.%' IDENTIFIED BY PASSWORD 'xxx' REQUIRE SSL |

I did a flush privileges, but even with no privileges, I'm still able to show databases, show tables, select. How is that possible ?!!!

I checked that in 'show full processlist' the user is not always connected, fresh new mysql connection. Once the connection opened, I do a 'show grants', the effective permission is the same… USAGE.

—

The table mysql.db have an entry for this user that says he has select,insert,update and delete in the database. So is this kind of a hidden permission table ? How those permission could have gone there instead of mysql.user ?

Best Answer

It may be possible that you probably have an anonymous user in mysql.user.

First things first. Please run this query:

SELECT USER(),CURRENT_USER();

USER() reports how you attempted to authenticate

CURRENT_USER() reports how mysqld allowed to authenticate

If the second function reveals a weird user, chances are it may be the anonymous user.

Go to my DBA StackExchange answer on how MySQL performs user authentication plus how and why to remove anonymous users.

Related Topic