3

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 ?

Improve this question
9
  • 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'; Commented Feb 9, 2012 at 21:04
  • Only 1 unique 'user'. I'm granting rights to this user coming from a range of ip xx.xx.xx.% Commented Feb 9, 2012 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. Commented Feb 9, 2012 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? Commented Feb 9, 2012 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. Commented Feb 9, 2012 at 22:02

1 Answer 1

Reset to default
2

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.

Improve this answer
2
  • 1
    The query return : [email protected] | [email protected].% , 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) ? Commented Feb 10, 2012 at 14:17
  • @RolandoMySQLDBA, What do you mean by "a weird user"? Commented Jan 23, 2015 at 12:28

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.