MySQL Bugs: #47576: TRUNCATE TABLE for temporary tables maps to DROP TABLE

Bug #47576	TRUNCATE TABLE for temporary tables maps to DROP TABLE
Submitted:	24 Sep 2009 0:05	Modified:	5 Jul 2010 13:22
Reporter:	Jonathon Coombes	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S4 (Feature request)
Version:	5.1.37	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	drop table, drop temporary table, truncate table

Description:
The TRUNCATE TABLE command maps to a DROP instead of a DELETE as of 5.1.16, but it seems to ignore temporary tables. Instead of mapping to DROP TEMPORARY TABLE it maps instead to a plain DROP TABLE which causes need for DROP privileges to have been assigned. 

Temporary tables do not need the DROP privilege due to them being local to the session.

How to repeat:
mysql> grant select,insert,update,delete,create temporary tables, lock tables on mydb.* to 'dbuser'@'%';

mysql> create temporary table mytest(a int);
Query OK, 0 rows affected (0.00 sec)
mysql> truncate table mytest;     
ERROR 1142 (42000): DROP command denied to user 'dbuser'@'localhost' for table 'mytest'
mysql> drop table mytest;
ERROR 1142 (42000): DROP command denied to user 'dbuser'@'localhost' for table 'mytest'
mysql> drop temporary table mytest;
Query OK, 0 rows affected (0.00 sec)

Suggested fix:
Correct the mapping to DROP TEMPORARY TABLE for temporary tables.

Thank you for reporting this bug.

Verified as described.

This is expected and documented behavior.
The manual clearly says that "TRUNCATE TABLE  requires the DROP privilege":

http://dev.mysql.com/doc/refman/5.1/en/truncate.html

DROP TEMPORARY TABLE does not require the DROP privilege, but it is irrelevant for TRUNCATE.

It's the other way around:

A TRUNCATE should map to DROP TEMPORARY TABLE for a temporary table if it uses DROP TABLE at all.

Why otherwise have a DROP *TEMPORARY* TABLE at all?

Also, the manual states for DROP TEMPORARY TABLE:
'No access rights are checked. (A TEMPORARY table is visible only to the session that created it, so no check is necessary.)'

If no rights are necessary for DROPping a TEMPORARY table, why would they be necessary for TRUNCATE-ing a TEMPORARY table?

Also, this is not documented behavior: rather, TRUNCATE table does not have a [TEMPORARY] option.

you're arguing, again, what the behavior you think "should" be.
It doesn't change the fact that the current one is intended and documented.

A request to change an intended and documented behavior is a feature request

I think that the existing implantation (TRUNCATE TABLE maps to DROP TABLE) is incorrect for temporary tables because:

1. DROP TEMPORARY TABLE exists. At the time when DROP TEMPORARY TABLE was created, TRUNCATE TABLE should have been remapped to DROP TEMPORARY TABLE for TEMPORARY tables, or a TRUNCATE TEMPORARY TABLE (with the same mapping) should have been created.

2. It is a bug to fail on TRUNCATE of a TEMPORARY table, given that no DROP rights are needed for TEMPORARY TABLES (as another documentation section points out, see my comment above).

Another interesting point to notice in the manual is this: 'Using TEMPORARY is a good way to ensure that you do not accidentally drop a non-TEMPORARY table.' (http://dev.mysql.com/doc/refman/5.1/en/drop-table.html) - It is clear the same goes for TRUNCATE.

When considering the suitability of the current TRUNCATE TABLE to DROP TABLE mapping, there are a number of points that need to be addressed:

1. The historical TRUNCATE -> DELETE mapping (pre-5.1.6) did not have to be concerned too greatly with privilege changes since one was working "within" the table on the data itself, so whether the table was temporary was not really an issue.

With the new TRUNCATE -> DROP mapping we are now affected by a difference of data vs schema. TRUNCATE will remove the data from within the table, but DROP removes not only the data, but the schema definition also.

2. Looking at the existing mapping (TRUNCATE -> DROP), we have to consider required changes to privileges to accommodate the DROP statement. It should be noted that most users who have rights for data manipulation would already have the usual INSERT/UPDATE/DELETE etc privileges, but not necessarily DROP/CREATE privileges.

It was mentioned above that the manual says that "TRUNCATE TABLE requires the DROP privilege", but this adds complications when using temporary tables. We are essentially asking customers to grant DROP privileges to the user, for all tables within a database, just so they are able to delete the temporary tables.

3. The explanation in the manual describes only how the mapping has changed, not how to overcome the problems introduced by this change. People are confused when they see DROP privilege errors on a TRUNCATE command, particularly when it is a temporary table that will be destroyed when they disconnect the session.

And, improving the statement I made earlier:

'At the time when DROP TEMPORARY TABLE was created, TRUNCATE TABLE should have been remapped to DROP TEMPORARY TABLE for TEMPORARY tables, or a TRUNCATE TEMPORARY TABLE (with the same mapping) should have been created.'

To:

'At the time when the mapping for TRUNCATE was changed from DELETE to DROP (5.1.16), TRUNCATE TABLE should have been mapped to DROP TEMPORARY TABLE for TEMPORARY tables, or a TRUNCATE TEMPORARY TABLE (with the same mapping) should have been created.'

Also see:
http://dev.mysql.com/doc/refman/5.1/en/news-5-1-16.html
bug #23556

By the way, if you ask me, we shouldn't do anything with TRUNCATE in this regard, but fix all operations with temporary tables to require only CREATE TEMPORARY privilege (at last).

see bug#27480

Hopefully the change as recommended in bug #27480 will fix this behavior for TRUNCATE.

Also, how about uniformity after this bug is fixed? Should we drop 'DROP TEMPORARY TABLE' or create 'TRUNCATE TEMPORARY TABLE'?

Sergei, I agree about one privilege for all temporary table operations as the end result. Might be more efficient to just do that instead of addressing individual bugs.

TRUNCATE is a DDL.

This bug will be fixed by the fix for bug #27480. Making it a duplicate.

A workaround is to replace TRUNCATE with a DROP TEMPORARY TABLE; CREATE TEMPORARY TABLE sequence, which does not require drop privilege.