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

[8 Jan 2004 6:36] Philippe Jausions

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,

[8 Jan 2004 6:51] Dean Ellis
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.
[21 Apr 2004 7:23] Viktor Kaydalov
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.
[28 Jan 2006 12:38] Valeriy Kravchuk
Thank you for a reasonable feature request. Bug #16254 is marked as a duplicate of this one.
[21 Nov 2008 9:23] Sveta Smirnova
Bug #40776 was marked as duplicate of this one.

Bug #21100 and bug #16664 look like duplicate of this one too.
[2 Feb 2009 12:15] Valeriy Kravchuk
Bug #42540 was marked as a duplicate of this one.
[7 Sep 2009 20:54] James Day
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.
[18 Sep 2009 16:03] Valeriy Kravchuk
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.
[18 Sep 2009 16:05] MySQL Verification Team
Consolidating this and the related bugs as duplicates of bug #27480