Home / server / Questions / 358535
In Process
Bastien974
Asked: 2012-02-10 12:57:48 +0800 CST

MySQL user's grant are not working - hidden permission in mysql.db

  • 772

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 [email protected].%                                      |
+-------------------------------------------------------------------------------------------------------------------------------+ 
| 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 ?

mysql

1 Answers

  1. 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.

    • 2