Unable to remove permission for mysql.user

Question

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

Answer 1

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.