Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I created a MySQL database and created a new user dedicated to that database with all permissions. The new user then proceeded of dropping the database in addition to all tables. I need to block that user from being allowed to drop the database, but allow him to drop anything else.

My question is: is there a server level trigger in MySQL similar to MSSql, or is there another method to do this?

share|improve this question

migrated from stackoverflow.com Oct 22 '12 at 15:04

This question came from our site for professional and enthusiast programmers.

    
Please login as that user and run SHOW GRANTS; (hide the password) –  RolandoMySQLDBA Oct 19 '12 at 21:32
    
Use GRANT function: kb.mediatemple.net/questions/788/… –  Marin Sagovac Oct 19 '12 at 21:32
    
Maybe you can use revoke (if you've already granted permissions):REVOKE DROP ON... for more info see dev.mysql.com/doc/refman/5.0/en/revoke.html –  dudelgrincen Oct 19 '12 at 21:37
    
thanks :) yep, just for the example I wrote Select. I forgot for a moment he asked for drop :) –  dudelgrincen Oct 19 '12 at 21:40
    
You should live by the <a href="en.wikipedia.org/wiki/… of Least Privilege</a> - when you create a user only give them the privileges they absolutely must have. Using a tool like PHPMyAdmin might make it easier for you to see what permissions a user has. –  Benny Hill Oct 19 '12 at 21:43

2 Answers 2

Perhaps you want to remove the DROP privilege at the global user level

UPDATE mysql.user SET drop_priv = 'N' WHERE user='...' AND host='...' and db='...';
FLUSH PRIVILEGES;

Something to keep in mind: The DROP privilege encompasses databases, table, and views. Unfortunately, DROP DATABASE and DROP TABLE fall under the same privilege scope.

For example

I create a user called kumar and a database called kumar

mysql> create database kumar;
Query OK, 1 row affected (0.00 sec)

mysql> grant all on kumar.* to kumar@'%';
Query OK, 0 rows affected (0.06 sec)

mysql> use kumar
Database changed
mysql> create table rolando (a int);
Query OK, 0 rows affected (0.08 sec)

I logged into mysql as kumar and dropped the database kumar

mysql> drop database kumar;
Query OK, 1 row affected (0.07 sec)

So, I recreated it as a SUPER user. I also create a database kumar2

mysql> create database kumar;
Query OK, 1 row affected (0.00 sec)

mysql> use kumar
Database changed
mysql> create table rolando (a int);
Query OK, 0 rows affected (0.07 sec)

mysql> create database kumar2;
Query OK, 1 row affected (0.00 sec)

mysql>

I tried to drop kumar2

mysql> show grants for kumar@'%';
+--------------------------------------------------+
| Grants for kumar@%                               |
+--------------------------------------------------+
| GRANT USAGE ON *.* TO 'kumar'@'%'                |
| GRANT ALL PRIVILEGES ON `kumar`.* TO 'kumar'@'%' |
+--------------------------------------------------+
2 rows in set (0.00 sec)

mysql> select user(),current_user();
+-----------------+----------------+
| user()          | current_user() |
+-----------------+----------------+
| kumar@localhost | kumar@%        |
+-----------------+----------------+
1 row in set (0.00 sec)

mysql> drop database kumar2;
ERROR 1044 (42000): Access denied for user 'kumar'@'%' to database 'kumar2'
mysql>

Thus, kumar@'%' can only drop the kumar database. kumar@'%' cannot drop any other database.

CONCLUSION

The user kumar@'%' can drop the following:

  • All Views in the kumar database
  • All Tables in the kumar database
  • The kumar database itself

You can either allow all three or restrict all three fro this user's access to the kumar database.

share|improve this answer
    
Hi if DROP DATABASE and DROP TABLE fall under the same privilege scope . In my case i have to give DROP TABLE permission to that user and not DROP DATABASE , so i can say there is noway in mysql to do this to my client? –  Kumar_2002 Oct 19 '12 at 22:06
    
Thanks for your time and stuff , and the last thing in your example can you make kumar user can drop all tables in Kumar database and also he should NOT ABEL to drop kumar database –  Kumar_2002 Oct 19 '12 at 22:25
    
Sadly, the answer to that is no. That is what I meant by privilege scope. Databases, tables, and views in mysql.db have the same privilege scope on the drop_priv column. –  RolandoMySQLDBA Oct 19 '12 at 22:27
    
Thanks saved my time .. –  Kumar_2002 Oct 19 '12 at 22:28

Do not give your user ALL permissions on the databases and/or server. Use the GRANT permissions on your databases, servers, and tables independently.

Mysql "permissions" : http://dev.mysql.com/doc/refman/5.1/en/grant.html#grant-database-privileges

For example :

GRANT SELECT, INSERT ON mydb.* TO 'someuser'@'somehost';
GRANT ALL ON mydb.mytbl TO 'someuser'@'somehost';
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.