Permission denied on Connect with table only privileges set

Question

(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?

Answer 1

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'));

Answer 2

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.