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.

(originally asked on StackOverflow but it was suggested that I ask here) - MySQL 5.1.41 community (Windows Server 2003) - .Net/Connector 6.1.3.0

I've just created a new user and rather than grant schema privileges I've granted rights on only specific tables. In other words, there are NO global or schema level permissions set, only permissions set are for a couple specific tables. This seemed like a perfectly acceptable policy and from what I've ready MySQL will search:

  1. global
  2. schema/DB
  3. Table
  4. Column

for appropriate permissions. However when I try to open a connection to the server using this new user account I receive a permission denied error.

Access denied for user 'xxxxxx'@'%' to database 'xxxxxxx'

I have tried with several different clients and the result is the same. I have also checked the server log and there are no additional details, just this:

110816 10:23:15 543 Connect xxxxxx@192.168.1.xxx on xxxxxxxx 543 Init DB Access denied for user 'xxxxx'@'%' to database 'xxxxxxx'"

If I grant SELECT on the schema in question I can connect just fine. Am I misunderstanding the way the permission system works or should it work like I think?

share|improve this question

2 Answers 2

If you add a user with a manual insert into mysql.user, you need to run flush privileges before you can login with that user. If you use a GRANT to create the user, the flush is automatically done for you.

Here's an example insert:

INSERT INTO mysql.user (user, host, password) 
  VALUES ('user', 'localhost', password('yourpassword'));
share|improve this answer
    
The user was created with MySQL Administrator so I checked the logs to see what it did. It called flush privileges. plus I have been able to connect with this user WHEN I grant SELECT tot he schema - So maybe this is REQUIRED? –  Steve K Aug 16 '11 at 22:42
1  
I can add a user with a direct insert statement, flush privileges and login without difficulty, even with no privileges. –  sreimer Aug 17 '11 at 1:29
    
I've never added a user with a sql statement, do you mind posting your statement (with bits redacted) so I can test on my end? –  Steve K Aug 17 '11 at 1:54
    
added the query to my answer –  sreimer Aug 17 '11 at 3:28
    
Thanks for the statement - I could have tried myself but wanted to be sure I used what you did. I ran: " INSERT INTO mysql.user (user, host, password, ssl_cipher, x509_issuer, x509_subject) VALUES ('user1', 'localhost', 'password1', '','','');" and still get access denied when trying to connect –  Steve K Aug 17 '11 at 4:23

The error message tells you what you need to know: No access to the database. Can't access the table without accessing the enclosing database, so grant appropriate permissions to the latter. It's kind of like being granted access to a file on a server but denied access to the server itself.

share|improve this answer
    
I just didn't believe that MySQL would REQUIRE a user to have SELECT access for the entire database in order to connect. You can't "take away" permissions at the table level so if I want to limit a user to only select a subset of tables I can't. –  Steve K Aug 17 '11 at 5:11
    
also sreimer seems to have been able to create a user with no permissions granted and still be able to connect. –  Steve K Aug 17 '11 at 5:11
    
@Steve, you've read more into sreimer's answer than what is there. Simply being able to log in to the server does not imply access to databases or tables. –  John Gardeniers Aug 17 '11 at 5:22
    
I see your point, however I can't even login/connect so I was holding out hope that once connected I would be able to work with objects that I have permissions for (e.g. select from table foo) To be crystal clear: Are you saying that I must grant permission to the schema to be able to perform CRUD operations with specific tables within that database? And by extension grant select to all tables within that database? –  Steve K Aug 17 '11 at 6:44
    
@Steve, if you can't even log in you need to solve that first, then work your way through the other two layers. Never having restricted a user to just a table I'm not sure just what rights they need on the database to be able to access the table but I'm sure that would be covered in the documentation. –  John Gardeniers Aug 17 '11 at 21:26

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.