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’
These answers are provided by our Community. If you find them useful, show some love by clicking the heart. If you run into issues leave a comment, or add your own answer to help others.
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.