Blocking drop database command

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?

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.

StackExchange Database Administrators Q#27372, answer score: 11