This project has moved and is read-only. For the latest updates, please go here.

SELECT command denied to user 'myuser'@'localhost' for table 'user' when calling ReadAll()

May 23, 2014 at 9:14 PM
Edited May 23, 2014 at 9:18 PM
I'm getting the following exception when calling DatabaseReader.ReadAll() against a MySQL database when logged in as a user with full proveleges for that particular database:
A MySql.Data.MySqlClient.MySqlException was thrown.
SELECT command denied to user 'myuser'@'localhost' for table 'user'
The user "myuser" has full read/write priveleges for the database being queried. He does not have, nor should he have, any other priveleges outside of this scope.

Any idea why this is happening and how to deal with it?
May 24, 2014 at 4:24 PM
The MySql connector driver does this for users:
SELECT Host, User FROM mysql.user

To get the list of users, 'myuser' needs to read the mysql.user table.

It also needs to get other mysql metadata, such as the Information_schema database.

If you call ReadAll(), it actually does AllUsers(), AllTables(), AllViews() and AllStoredProcedures() in that order.
You can call them individually, but you may have a similar error when it tries to get the other metadata.

I'm not sure it's practical to use a user that only has grants for the specific database. I think it will need wider read access (at least mysql and information_schema). You shouldn't need write access at all (to anything).
Marked as answer by engineering90xi on 5/29/2014 at 4:52 PM
May 30, 2014 at 12:52 AM
Seems reasonable to allow read-only access to system/schema tables. Not a deal breaker. Thanks.