Unable to remove permission for mysql.user I am trying to remove permission from mysql user to not be able to delete specific database. But if i run this command:

revoke all privileges on test.* from 'demo'@'localhost';


or this

revoke drop on test.* from 'demo'@'localhost';


I get this error


  ERROR 1141 (42000): There is no such grant defined for user 'demo' on host 'localhost'


test is the name of the databse.

The permissions for demo user are this:

mysql&gt; show grants for demo@'localhost';
+----------------------------------------------------------------------------------------------------------------------+
| Grants for demo@localhost |
+----------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'demo'@'localhost' IDENTIFIED BY PASSWORD '*C142FB215B6E05B7C134B1A653AD4B455157FD79' |


Also, when i login as user demo i am able to delete the database test.

Why i am getting this error when i try to remove the permissions from user demo?
There is a big reason for this. There are four levels of grants in MySQL


Global (stored in mysql.user)
Database (stored in mysql.db)
Table (stored in mysql.tables_priv)
Column (stored in mysql.columns_priv)


When you ran 

show grants for demo@'localhost';


mysqld read the grants and saw demo@'localhost' in mysql.user

When you ran

revoke all privileges on test.* from 'demo'@'localhost';


mysqld looked for the grants, not in mysql.user, but mysql.db.

You cannot yank grants on individual database from a user with global grants.