MySQL Bugs: #12437: Operations with temporary tables require extra permissions

Bug #12437	Operations with temporary tables require extra permissions
Submitted:	8 Aug 2005 15:11	Modified:	30 Jun 2009 17:02
Reporter:	Vahan Sardaryan	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server	Severity:	S4 (Feature request)
Version:	4.1.12a	OS:	Windows (Windows XP SP2)
Assigned to:		CPU Architecture:	Any

Description:
When CREATE_TMP_TABLE is granted to user on specific DB, temporary tables may be created, but any operation with them requires corresponding privilege defined at least on database level (SELECT for selecting, etc.). User should have full access to his temporary tables by default without having other DB-level permissions, as it opens access to regular tables of other users. 

How to repeat:
Create a database, grant only CREATE_TMP_TABLE privilege on it for some user. CREATE TEMPORARY TABLE temptable ..... executes successfully, but then
SELECT * from temptable fails with "access denied" error.

Sorry I was not able to reproduce the bug. Could you please write some test case ?

MySQL script files and batch file to execute them

Attachment: bugdemo.zip (application/x-zip-compressed, text), 646 bytes.

I've created a test case for it. The batch file run root script first, that creates a database, a simple table in it and sets permissions to a user (basic privileges on regular table, plus CREATE TEMPORARY TABLES on database). User password is set to 111 (this is to simulate failing environmen completely).

After that user script runs, that creates a temporary table from content of regular one (this operation succeeds), and tries to select data from temporary table (Access denied error reported).

Thanks,

Vahan

Thanks for test case.I was able to reproduce the bug on Windows 2003

D:\STORE ODBC FILES\12437>mysql -u testuser -p -e "source usercmd.sql"
Enter password: ***
ERROR 1142 (42000) at line 3 in file: 'usercmd.sql': SELECT command denied to user 'testuser'@'localhost' for table 'tes
ttbl'

I'm going to re-test it on Linux

Vahan, I don't see bug here. 'create temporary table' grants to create temptable only, the similar as 'create' grants to create normal table. 

If you grant 'create' option _only_ to user, he will not be able to select, insert  , update tables that he has created even in case of ordinary tables.

I don't think that 'create temporary table' must provide more privileges for temptables than 'create' for ordinary tables.

Alexey,

Sorry, I disagree with you. First of all, temporary tables are visible to creating connection only, and are deleted when connection is closed. So, there is no security issue at all with allowing full privileges for user on his own temporary tables. Naturally, when you create a temporary table you expect to be able reading from it (at least), and sometimes you don't know the name of this table, as many applications generate random names. 

Second, please imagine a situation when user needs to work with temporary tables, and we need to provide him with database-wide privileges to do this. It automatically implies that we allow that user such access to ALL tables in database, including UPDATE access if he needs to update his temporary tables. Having such security flaw is impossible, so we just have to deny using temporary tables that is unlikely. 

Finally, please note that it was working normally in server 4.1.9, but behaviour has changes in one of later versions. 

Thank you,

Vahan

hm....according to MySQL Reference Manual the privilege grants only "Allows use of CREATE TEMPORARY TABLE".

Anyway, it renders temporary tables unusable in secure environments. So be it!

Not really. It only proves that this is not a bug but a documented behaviour, so this bugreport is turned into a feature request. The behaviour still can be changed.

I'd say this is a duplicate of bug #27480.