MySQL Bugs: #16254: temporary tables use db specific privileges, but should not

Bug #16254	temporary tables use db specific privileges, but should not
Submitted:	6 Jan 2006 15:04	Modified:	28 Jan 2006 12:40
Reporter:	Stephen Surles	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server	Severity:	S4 (Feature request)
Version:	5.0.x, 4.1.14	OS:	Linux (Linux)
Assigned to:		CPU Architecture:	Any

Description:
If a user has table specific privileges and create temp table privileges on the db, the temp tables are created, but the user is not allowed to do any selects inserts updates or deletes(or truncates). The only way to give the user temp table privileges is to give the user those privileges to the entire db. These privileges then negate/trump the table specific privileges. 

How to repeat:
Create a DB
Give User create temporary table privileges on that DB
Try to issue a select, insert, update or delete from that temporary table

Suggested fix:
The resolution it seems to me would be that the ability to create temp tables automatically grants all privileges to that temp table, or there be seperate permissions for temporary tables.

Thank you for a problem report. Yes, everything works just as you described, even in the newest 5.0.x:

[openxs@Fedora 5.0]$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4 to server version: 5.0.19

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> grant create temporary tables on test.* to user_t@localhost identified by 'user_t';
Query OK, 0 rows affected (0.01 sec)

mysql> exit
Bye
[openxs@Fedora 5.0]$ bin/mysql -uuser_t -puser_t test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5 to server version: 5.0.19

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create temporary table ttt1 (c1 int);
Query OK, 0 rows affected (0.00 sec)

mysql> insert into ttt1 values(1);
ERROR 1142 (42000): INSERT command denied to user 'user_t'@'localhost' for table 'ttt1'
mysql> select * from ttt1;
ERROR 1142 (42000): SELECT command denied to user 'user_t'@'localhost' for table 'ttt1'

Formally, according to the manual (http://dev.mysql.com/doc/refman/5.0/en/privileges-provided.html):

"The CREATE TEMPORARY TABLES privilege allows the use of the keyword TEMPORARY in CREATE TABLE statements."

CREATE TEMPORARY TABLES gives you no privileges for temporary tables created. So, it is not a bug... 

But I see no reason for the current behaviour. In all other RDBMSes that support temporary table, the user who created temporary table has all the table-level privileges for it. So, I'll mark this report as a verified and reasonable feature request.

Consolidating this bug as a duplicate of #27480