Bug #45653 database-level privilege required to access temporary tables
Submitted: 22 Jun 2009 11:55 Modified: 22 Jun 2009 15:11
Reporter: Peter Laursen (Basic Quality Contributor) Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Security: Privileges Severity:S3 (Non-critical)
Version:any OS:Any
Assigned to: CPU Architecture:Any
Tags: qc

[22 Jun 2009 11:55] Peter Laursen
Description:
There was an old report here:
http://bugs.mysql.com/bug.php?id=179

It is marked as 'closed' but I do not find a proper reply or conclusion.

How to repeat:
SHOW GRANTS;
/*
Grants for temptest@localhost                                 
--------------------------------------------------------------
GRANT CREATE TEMPORARY TABLES ON *.* TO 'temptest'@'localhost'
*/

USE test;

CREATE TEMPORARY TABLE temptab (id INT, txt VARCHAR(20));
-- success

INSERT INTO temptab VALUES (1,'a');
/
Error CODE : 1142
INSERT command denied TO USER 'temptest'@'localhost' FOR TABLE 'temptab'
*/

-- as root 
GRANT ALL ON test.* TO 'temptest'@'localhost';

-- new connection as temptest
USE test; 

CREATE TEMPORARY TABLE temptab (id INT, txt VARCHAR(20));

INSERT INTO temptab VALUES (1,'a');
-- success

-- now revoking most privileges to test database (only privileges to an empty table `t1` is still there) and reconnecting;
SHOW GRANTS;
/*
Grants for temptest@localhost                                 
--------------------------------------------------------------
GRANT CREATE TEMPORARY TABLES ON *.* TO 'temptest'@'localhost'
GRANT ALL PRIVILEGES ON `test`.`t1` TO 'temptest'@'localhost' 
*/

USE test;

CREATE TEMPORARY TABLE temptab (id INT, txt VARCHAR(20));

INSERT INTO temptab VALUES (1,'a');
/*
Error Code : 1142
INSERT command denied to user 'temptest'@'localhost' for table 'temptab'
*/

Suggested fix:
I think the proposal in the old report that a user with CREATE TEMP TABLES privileges should have full access to temp tables for the session is worth considering.

I can imagine several scenarios where security concerns would prohibit use of full database level privileges for a user only.

What is the idea of CREATE TEMP TABLES privilege if the user is not able to use the tables he is able to create? 

I realize the root may "GRANT ALL ON test.t99 TO 'temptest'@'localhost';" and then user 'temptest' will be able to "CREATE TEMPORARY TABLE t99 .." and INSERT and SELECT etc. as well. 

However I believe that CREATE TEMP TABLES privileges is rather useless with current implementation in the situations where full database-level privileges are not feasible. The idea was that a user should have access to use temp tables I believe when granted *once and for all*
[22 Jun 2009 14:33] Valeriy Kravchuk
I think this is a duplicate of Bug #27480, verified bug. Please, check.
[22 Jun 2009 15:07] Peter Laursen
yes .. sorry for dupping!
[22 Jun 2009 15:11] Valeriy Kravchuk
Duplicate of Bug #27480.