MySQL privileges – DROP tables, not databases

Michal M asked:

Can someone help me with privileges here.
I need to create a user that can DROP tables within databases but cannot DROP the databases?

From what I understand from MySQL docs you cannot simply do this:

The DROP privilege enables you to drop (remove) existing databases, tables, and views. Beginning with MySQL 5.1.10, the DROP privilege is also required in order to use the statement ALTER TABLE … DROP PARTITION on a partitioned table. Beginning with MySQL 5.1.16, the DROP privilege is required for TRUNCATE TABLE (before that, TRUNCATE TABLE requires the DELETE privilege).

Any ideas?

Oh, and the dabase in use is 5.0.45.

My answer:

If a user can DROP a table, then he could DROP all the tables, which effectively drops the database. So there’s no point in separate privileges for DROP TABLE and DROP DATABASE.

If you want to grant a privilege for a user to DROP only a single specified table within the database, that’s something that might be useful, but MySQL doesn’t support it.

View the full question and any other answers on Server Fault.

Creative Commons License
This work is licensed under a Creative Commons Attribution-ShareAlike 3.0 Unported License.