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:Microsoft Windows (Windows XP SP2)
Assigned to: CPU Architecture:Any

[8 Aug 2005 15:11] Vahan Sardaryan
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.
[9 Aug 2005 8:44] Vasily Kishkin
Sorry I was not able to reproduce the bug. Could you please write some test case ?
[9 Aug 2005 13:59] Vahan Sardaryan
MySQL script files and batch file to execute them

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

[9 Aug 2005 14:02] Vahan Sardaryan
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
[13 Aug 2005 7:51] Vasily Kishkin
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
[13 Aug 2005 17:19] Aleksey Kishkin
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.
[14 Aug 2005 8:26] Vahan Sardaryan
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
[28 Aug 2005 6:15] Vasily Kishkin
hm....according to MySQL Reference Manual the privilege grants only "Allows use of CREATE TEMPORARY TABLE".
[28 Aug 2005 7:11] Vahan Sardaryan
Anyway, it renders temporary tables unusable in secure environments. So be it!
[14 Sep 2005 8:34] Sergei Golubchik
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.
[30 Jun 2009 17:02] Valeriy Kravchuk
I'd say this is a duplicate of bug #27480.