MySQL Bugs: #42143: DROP TABLE IF EXISTS cause access error even when the table doesn't exist

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:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.1.30-community	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
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
check.

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.

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

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.

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'
SQL SECURITY INVOKER
BEGIN
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET @dummy = 0;
SET @cmd = CONCAT('DROP TABLE IF EXISTS `', tbl, '`');
PREPARE stmt FROM @cmd; EXECUTE stmt;
END|
delimiter ;

Thanks for prompt answers.

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.

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!