3

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?

Improve this question

1 Answer 1

Reset to default
5

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.

Improve this answer
7
  • So, is it possible then to forbid user demo from deleting database test?
    – user1800997
    Jul 16, 2015 at 19:56
  • I am going to go out on a limb and yes, but there is a lot of work involved. If you are ready, let know me. But first tell me how many databases do you have in the MySQL instance, and how many databases the demo user will and will not have access to ???
    – RolandoMySQLDBA
    Jul 16, 2015 at 20:07
  • I was hoping that there is a simple solution for this. I don't want to get involved in more complicated things because i have only basic knowledge about mysql. I was asking this because i want to hire a person to maintain the mysql databases on my server but i don't want him to be able to have the permission to delete some databases. I guess i will use both phpmyadmin interface and mysql terminal and see what i can do. By the way, is there a third party package that i can install on ubuntu server that can make the mysql database management more easy?
    – user1800997
    Jul 16, 2015 at 20:28
  • 2
    I don't understand something: If i create a user and grant him permission globally then i can't revoke this global permission, i have to delete the user and then create it again and grant him permissions not globally but only for specific databases if i don't want him to have global permission. Is that correct?
    – user1800997
    Jul 16, 2015 at 21:07
  • 1
    Yes you got it right, and there is a great reason why. When someone has GRANT ALL PRIVILEGES on *.*, such a user would have SUPER and SHUTDOWN privileges (dba.stackexchange.com/a/72157/877). A demo user should never have these.
    – RolandoMySQLDBA
    Jul 16, 2015 at 21:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.