Bug #18938 Disallowing DROP DATABASE via privileges
Submitted: 10 Apr 2006 10:47 Modified: 24 Apr 2006 9:39
Reporter: Jelle Raaijmakers Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Security: Privileges Severity:S4 (Feature request)
Version: OS:Any
Assigned to: CPU Architecture:Any

[10 Apr 2006 10:47] Jelle Raaijmakers
Description:
I run a small shared webhosting server and offer amungst other things a certain amount of MySQL databases. My users are able to create these databases via a control panel and have full rights to these databases:

GRANT ALL PRIVILEGES ON databasename.* TO username

But this includes the DROP-privilege. At the moment there is no separation between the DROP TABLE and DROP DATABASE privileges, which causes the users to be able to drop a database without using my control panel.

So I think a DROP DATABASE/TABLE privilege separation should be introduced. I don't know the logic behind the current privilege layout, there is no logical explanation why dropping tables should include the privilege to drop the entire database.

If I give a user ALTER and DELETE privileges on a database, they are able to truncate and/or mutilate the tables. But they are not able to drop the table! How is this extrapolated to the DROP-privilege for databases?

How to repeat:
-- As the administrator user:
CREATE USER 'testuser'@'%';
GRANT USAGE ON *.* TO 'testuser';
CREATE DATABASE `testuserdb`;
GRANT ALL PRIVILEGES ON `testuserdb` TO 'testuser';

-- As the testuser user:
DROP DATABASE `testuserdb`;
-- I think this requires a separate privilege, next to DROP TABLE
[10 Apr 2006 10:51] Jelle Raaijmakers
In the 'how to repeat'-section, I meant this instead:

GRANT ALL PRIVILEGES ON `testuserdb`.* TO 'testuser';
[24 Apr 2006 9:39] Valeriy Kravchuk
Thank you for a reasonable feature request. I think, only user who created the database should be able to drop it by default and, indeed, privilege to drop any table in the database is not exactly the same that DROP DATABASE privilege (at least, in, say, Informix RDBMS). 

Do not expect this feature to be added really soon, though.
[14 Mar 2007 17:21] john danilson
In my opinion the create database should similarly be restricted.  We allow developers to manage tables, etc. in their databases which the dba creates for them.  Once the db work goes to qa we do all further work on the database.  However, we must grant create privileges to developers to do drop/create tables in their dev server but do not want them creating new databases without approval and input from the dba group.  I would assert, to be compatible with products like db2, sybase, oracle, that create and drop database would better belong to the super privilege.
[4 Jul 2007 2:56] Chu Yeow Cheah
Sorry to be adding more noise, but I really think this is a very important feature to have. Especially for Rails applications where developers usually drop and create tables freely, but you don't really want them accidentally dropping an entire database!

+1 (couldn't resist)
[13 Dec 2007 18:54] michael brown
Definitely definitely required. I've been driven crazy this last week searching for a way to prevent my users dropping databases, just because I allow them to drop tables. These two permissions should be separate.

The only ways I can think to do it just now are to physically prevent MySQL from deleting the database folder using SELinux, but SELinux scares me, or to completely dis-allow drop permission and have a cron job pro-actively granting drop privileges at the table level to each user...  Ugly.

For now MySQL will have to come with a health warning :(.
[21 Nov 2008 21:49] Kate Salholm
I'm not sure if anything has come of this, but as part-DBA and part-developer I was troubled when I accidentally dropped one of my databases this morning and in an effort to avoid this happening in future, tried to change my grant permissions. As I need to be able to DROP TABLES but don't ever want to be able to DROP my DATABASE, I think it would be a real advantage to MySQL to separate this privilege. If I do need to DROP the database I can get my admin account to do this. Please consider!
Thanks
[26 Nov 2008 5:16] Valeriy Kravchuk
Bug #40945 was marked as a duplicate of this one.
[19 Apr 2013 20:54] Ben Krug
Even granting DROP TABLE on a database allows the user to drop the database.  Eg, GRANT DROP ON TABLE DB1.* TO USERNAME allows USERNAME to drop the database DB1.
[20 Feb 2014 0:22] Lalit Pratihari
Hi,

Is there any progress on this bug or any ETA by the dev team ?

We are facing the similar situation and there are situations where the app developers are taking the control and creating/dropping the databases which we want to restrict.

Thanks
Lalit P
[7 Apr 2015 20:25] Allan Moraes
Hi,
There is same problem in my servers and the problem to CREATE privilege. Why you not divide this privileges? DROP DATABASE, DROP TABLE, CRATE DATABASE, CRATE TABLE?