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*
