Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

share|improve this question
add comment

5 Answers

up vote 1 down vote accepted

I have a same problem :( I think if user can drop all tables then empty database is no value

share|improve this answer
add comment

I'm just adding this as the other answers look more like comments to me... The short answer is no. Some alternatives are:

  • Consider putting tables in another database if you really need some users to have full control over some tables and not others. More privileged users can query across multiple databases.

  • Allow DELETE and ALTER so you can at least remove and/or redefine the data as needed. Everything you need except drop.

Neither are particularly convenient but if your need is that important you could do it.

share|improve this answer
add comment

Well, there's been a bug for this for 6 years and nothing has happened.

http://bugs.mysql.com/bug.php?id=18938

We may have to resort to adding the functionality to something like MariaDB (a fork of mysql) ...

share|improve this answer
    
Lol, nope. Fixed now. –  Brad Jul 12 '12 at 16:34
    
Well, last night in phpmyadmin, I clicked the check boxes for 3 tables and hit the drop button on the top right. This of course dropped the whole database instead of just the 3 tables. I should have had backups and I didn't, but it luckily was not that important. Having global drop table privileges instead of also drop databases would have prevented this. Mysql however does not support this. –  Brad Jul 12 '12 at 17:45
add comment

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.

share|improve this answer
add comment
  1. create user
  2. give drop permissions to the user for said database
share|improve this answer
    
Nope. Doesn't work. If you give DROP permission to the user on a database, he'll be able to drop this particular database as well as the tables. –  Michal M May 27 '10 at 8:25
    
It turns out that this is a constant in mysql 5, if the user has drop permissions, they'll be able to drop the database in which they have that permission. cite: dev.mysql.com/doc/refman/5.1/en/… –  zamabe May 27 '10 at 15:14
add comment

Your Answer

 
discard

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

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