Bug #42143 DROP TABLE IF EXISTS cause access error even when the table doesn't exist
Submitted: 15 Jan 2009 22:29 Modified: 16 Jan 2009 21:36
Reporter: Dmitry Samborskiy Email Updates:
Status: Not a Bug Impact on me:
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.1.30-community OS:Any
Assigned to: CPU Architecture:Any

[15 Jan 2009 22:29] Dmitry Samborskiy
Hi All,

Thanks for MySQL, the great tool which is highly
useful in many applications.

My problem occured when I've tried to use
unprivileged user which is allowed to create
tables in the database but is not allowed to
drop any table except the choosen subset.

Apparently, it's a problem of the order
of authentication check vs. table existsence

How to repeat:
1. Create MySQL user that has no DROP privileges on
the database level.

2. Using newly created account, run the following command:

mysql> drop table if exists nosuchtable;
ERROR 1142 (42000): DROP command denied to user 'newuser'@'localhost' for table 'nosuchtable'

Suggested MySQL behavior:
Since there is no table to delete, DROP TABLE IF EXISTS ...
should be successful.
[16 Jan 2009 6:25] Sveta Smirnova
Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Please read about how MySQL Privilege system works at http://dev.mysql.com/doc/refman/5.1/en/privilege-system.html and about this particular case at http://dev.mysql.com/doc/refman/5.1/en/privilege-system-overview.html: "For example, if you try to ... drop a table from the database, the server verifies that you have the SELECT privilege for the table or the DROP privilege for the database. "
[16 Jan 2009 6:26] Sveta Smirnova
Also read at http://dev.mysql.com/doc/refman/5.1/en/grant.html: "In standard SQL, when you drop a table, all privileges for the table are revoked. In standard SQL, when you revoke a privilege, all privileges that were granted based on that privilege are also revoked. In MySQL, privileges can be dropped only with explicit REVOKE statements or by manipulating values stored in the MySQL grant tables." So error you get is correct.
[16 Jan 2009 8:52] Dmitry Samborskiy
But in this case the table does NOT
exist!? That's the problem.

Therefore I think 'IF EXISTS' clause should prevent
any further checks (since there is nothing to
do at all).

I assume MySQL behavior as erroneous there
since in many cases it's convenient to use 
DROP TABLE IF EXISTS for the tables which
may or may not exist (there is no easy way to check
existence of some table, so 'DROP TABLE IF EXISTS'
is convenient for SQL scripting).

There is a workaround, though. I can make a procedure
in which SQLEXCEPTION is just ignored:

delimiter |
CREATE PROCEDURE drp ( tbl CHAR(128) )
COMMENT 'safe drop table for unprivileged users'
SET @cmd = CONCAT('DROP TABLE IF EXISTS `', tbl, '`');
PREPARE stmt FROM @cmd; EXECUTE stmt;
delimiter ;

Thanks for prompt answers.
[16 Jan 2009 9:42] Sergei Golubchik
The problem is that no matter if the table exists or not you should get the same error in both cases. If you have no privileges on the table you shouldn't be able to probe table's existence with DROP TABLE IF EXISTS.
[16 Jan 2009 21:36] Dmitry Samborskiy
Thank you for the answer, now I see the reason behind it.
It makes impossible to check table existence
by probing with DROP IF EXISTS.

Nevertheless, I'm afraid it will cause users
complaints like "I'm trying to delete my table
but due to its absence I get exception".

So, it's a trade off 'security vs. convenience'
and SQL standard has made decision in favor of security.

Thanks a lot for you answers!