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

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 ?

  • Do you see anything for the user if you do a show grants for user@'%'? What do you see if you do a select * from mysql.user where user='user'; – Zoredache Feb 9 '12 at 21:04
  • Only 1 unique 'user'. I'm granting rights to this user coming from a range of ip xx.xx.xx.% – Bastien974 Feb 9 '12 at 21:09
  • Ok, but what I am getting at, is that mysql doesn't default to allow, so if the user is getting access, then you likely have something else in your privilege tables granting more access then intend. Showing us only the grant that you think should apply probably won't give us much to help solve your problem. – Zoredache Feb 9 '12 at 21:18
  • 1
    Ok, so then take a look at your mysql.db table? Is there anything in there matching a user of % perhaps? – Zoredache Feb 9 '12 at 21:24
  • 1
    The reason why I asked you to update your question with what you saw, is so someone might be able to actually tell you want is going on. – Zoredache Feb 9 '12 at 22:02

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

First things first. Please run this query:


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.

  • 1
    The query return : user@xx.xx.xx.1 | user@xx.xx.xx.% , also there is no anonymous '' user in mysql.user. Could this be that I gave more permissions to this user in the past, then removed it but somehow it stayed in mysql.db (maybe because the user was still connected) ? – Bastien974 Feb 10 '12 at 14:17
  • @RolandoMySQLDBA, What do you mean by "a weird user"? – Pacerier Jan 23 '15 at 12:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.