Blocking drop database command

Question

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?

Answer 1

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';

Answer 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.