Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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 ?

share|improve this question
    
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

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

share|improve this answer
    
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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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