1

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.

2

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

1
  • Someone may drop database by wrong operation
    – Kymo Wang
    Aug 2 at 7:45
1

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.

1

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

2
  • 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
0
  1. create user
  2. give drop permissions to the user for said database
2
  • 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
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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