MySQL Bugs: #2317: TEMPORARY TABLE PERMISSIONS

Bug #2317	TEMPORARY TABLE PERMISSIONS
Submitted:	8 Jan 2004 6:36	Modified:	18 Sep 2009 16:03
Reporter:	Philippe Jausions	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Security: Privileges	Severity:	S4 (Feature request)
Version:	> 4.0	OS:	Any (ALL)
Assigned to:		CPU Architecture:	Any

Description:
Hi,

First of all, great job on MySQL, I've been using it for years and I am very pleased with it, and excited by the new developments coming up in the next releases.

About the "bug":

In a web site environment with database connections **pooled**, the temporary tables are not destroyed automatically, forcing the user to have the "DROP" privilege for all the tables in the database. The user may also want to drop temp HEAP tables during a program execution to free some memory space.

How to repeat:
Create a user with no DROP privileges, create a temporary table, try to drop the table.

Suggested fix:
The CREATE TEMPORARY TABLE permission is good, but not enough. There should also have a "DROP TEMPORARY TABLE" permission and so forth... Actually, it may make more sense to have a "OPERATE TEMPORARY TABLE" permission that would allow all basic operations on a temporary table such as: CREATE, DROP, INDEX, ALTER, SELECT, UPDATE, DELETE and INSERT. FILE permission is probably to be left alone for this for security reasons. The idea being that a connection that create a temporary table should also be able to manage it without needing to have the same privileges for the whole database. IT makes sense to me since the temp table is only visible from that connection...

Hopefully, this won't be too difficult to code in.

Best regards,

 -Philippe

I am changing this to a feature request, but I must mention that version 4.1 already has DROP TEMPORARY TABLE which provides the functionality you are requesting.

Yes, version 4.1 already has DROP TEMPORARY TABLE statement, but to INSERT/UPDATE temporary table I have to have corresponding database permission, and it's a security hole.
It would be nice to extend syntax to be able to INSERT INTO TEMPORARY TABLE and UPDATE TEMPORARY TABLE without permission check, as it already done for DROP TEMPORARY TABLE.

Thank you for a reasonable feature request. Bug #16254 is marked as a duplicate of this one.

Bug #40776 was marked as duplicate of this one.

Bug #21100 and bug #16664 look like duplicate of this one too.

Bug #42540 was marked as a duplicate of this one.

A workaround for this is to create a database for temporary tables and grant permissions for that database only. The temporary table rules will prevent a user from seeing temporary tables created by other users.

Let's mark this as a duplicate of Bug #27480 - that bug had already got a lot of attention and has highest possible priority of all other reports related to this problem.

Consolidating this and the related bugs as duplicates of bug #27480