There Is No Such Grant Error

August 7, 2016 3.3k views

I followed the instructions here to create a new user.

The only difference was that when giving the user original privileges I gave it to only one database.

Now I'm trying to remove the users access to a single table in that database with the following:

REVOKE ALL PRIVILEGES ON [database name].[table name] FROM ‘[username]’@‘localhost’;

But I keep getting the following error.

ERROR 1147 (42000): There is no such grant defined for user 'username' on host 'localhost' on table 'table_name'

2 Answers
ryanpq MOD August 9, 2016
Accepted Answer

What result is displayed if you run:

SHOW GRANTS FOR '[username]'@'localhost';

To remove permission grants you will need to be specific and reference grants explicitly listed.

Thanks for your reply ryanpq, you are correct. Because I applied the All Privileges permissions at the database.* level, I couldn't only revoke privileges at the database.* level as the All Privileges was not simply carried down to the individual tables.

Once I wiped privileges at the database level all together, I was then able to grant and revoke them on a table basis.

Have another answer? Share your knowledge.