MySQL Bugs: #16664: I can't give grant to temp table directly

Bug #16664	I can't give grant to temp table directly
Submitted:	20 Jan 2006 8:18	Modified:	18 Sep 2009 16:08
Reporter:	Marat Latypov	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Security: Privileges	Severity:	S4 (Feature request)
Version:	any, 5.0.13	OS:	Any (any, Linux)
Assigned to:		CPU Architecture:	Any

Description:
There  are strange thing when I want use select/update/delete grants on my temporary table.
1. I can't give grant to temp table directly.
2. Drop table doesn't fix 'mysql.tables'

How to repeat:
1. Creating user with basic access
----------------------------------------------------------------
# root session
$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10429 to server version: 5.0.13-rc-standard-log

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

mysql> insert into mysql.user (user,password)values('fooo',password('111'));
Query OK, 1 row affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.11 sec)

mysql> grant create temporary tables on test.* to fooo;
Query OK, 0 rows affected (0.01 sec)

# fooo user's session 
$ mysql -u fooo -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10435 to server version: 5.0.13-rc-standard-log

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

mysql> use test #Before grant
ERROR 1044 (42000): Access denied for user 'fooo'@'' to database 'test'
mysql> use test #After grant
Database changed
mysql> create temporary table a (b int);
Query OK, 0 rows affected (0.00 sec)

mysql> select * from a;
ERROR 1142 (42000): SELECT command denied to user 'fooo'@'localhost' for table 'a'

2. Attempt to give select/update/insert/delete grants
-----------------------------------------------------
# root session continues

mysql> grant select,update,insert,delete,drop on test.a to fooo;
ERROR 1146 (42S02): Table 'test.a' doesn't exist

mysql> create temporary table test.a (b int);
Query OK, 0 rows affected (0.00 sec)

mysql> grant select,update,insert,delete,drop on test.a to fooo;
ERROR 1146 (42S02): Table 'test.a' doesn't exist

3. Workaround of problem
-----------------------------------------------------
# root session continues
  
mysql> drop table test.a;
Query OK, 0 rows affected (0.00 sec)

mysql> create table test.a (b int);
Query OK, 0 rows affected (0.02 sec)

mysql> grant select,update,insert,delete,drop on test.a to fooo;
Query OK, 0 rows affected (0.02 sec)

mysql> drop table test.a;
Query OK, 0 rows affected (0.01 sec)  <--!!!!!

# fooo user's session. All is right

mysql> select * from a;
Empty set (0.00 sec)

mysql> drop table a;
Query OK, 0 rows affected (0.00 sec)

Suggested fix:
I see 3 ways:

1. If user has access for creating Temporary tables then give all grants to user on his created temps automatically without special grants for reading/writing/etc
(It's best for me)

2. Enable give grant on non-existing tables

3. Leave it as is without fixing.

Thank you for a problem report. Sorry, but it is not a bug. Please, read the manual carefully (http://dev.mysql.com/doc/refman/5.0/en/create-table.html):

"You can use the TEMPORARY keyword when creating a table. A TEMPORARY table is visible only to the current connection, and is dropped automatically when the connection is closed. This means that two different connections can use the same temporary table name without conflicting with each other or with an existing non-TEMPORARY table of the same name. (The existing table is hidden until the temporary table is dropped.)"

I think, it explains the results you got. And, by the way, it is a common way to implement temporary tables. IBM Informix, for example, deals with them in a similar way.

To make it obvious how it works in current 5.0.x versions:

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

mysql> insert into tttt values(1);
Query OK, 1 row affected (0.05 sec)

mysql> select * from tttt;
+------+
| c1   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql> exit
Bye
[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 2 to server version: 5.0.19

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

mysql> select * from tttt;
ERROR 1146 (42S02): Table 'test.tttt' doesn't exist

Sorry but I said about GRANTS(!!!)  with temporary tables.
Your answer doesn't explain nothing about how I must give access to ones.

In Russian:
Valeriy. Ja imel v vidu sovsem drugoe.  Kak dat' useru dostup k svojej tablice?

HOW TO DO GRANTS & TEMPORARY TABLES?

As I shown in my reply on 20 Jan 11:49, the user does not need any special grant after he created temporary table. He can do anything with it. 

BUT! After he closed the session, table disappears. And it is also not visible from other sessions of the same user. That is what explained in that manual quote.

So, there is NO NEED for GRANTS, anyway.

In Russian: vremennaja tablica v kazhdom seanse svoja, ee ne vidno ni v odnom drugom seanse.

Hmm...Really??? ;)))   So what is it ? -

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

mysql> select * from a;
ERROR 1142 (42000): SELECT command denied to user 'fooo'@'localhost' for table
'a'
( quoted from How to repeat )

These are two queries within one session without closing connection. 
So why user can't select his own temp table? 
You said "NO NEED for GRANTS, anyway."???
Please read carefully "How to repeat" queries. 
What is wrong?
PS: I don't want trolling. I just want to get full answer.

Sorry, I was my fault to check with 'root' user (having much more privileges anyway), and not with simple user like in your "How to repeat".

So, the problem still exists in current 5.0.19-BK (see below), and looks like it is well known for ages. Read the comment posted by Dietrich Feist on November 25 2003 3:02am at http://dev.mysql.com/doc/refman/5.0/en/privileges-provided.html for possible workaround...

So, what we really have is:

[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 3 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 fooo@localhost identified by '111';
Query OK, 0 rows affected (0.01 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

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

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

mysql> use test
Database changed
mysql> create temporary table a (b int);
Query OK, 0 rows affected (0.00 sec)

mysql> select * from a;
ERROR 1142 (42000): SELECT command denied to user 'fooo'@'localhost' for table 'a'

So, to give a user grants on his temporary table, now you have to grant him appropriate privileges on ALL tables in the database he usues. This is really bad and insecure. Other databases with temporary tables (Oracle, Informix) do not have such a strange requirement.

So, I mark it as a verified feature request - make temporary table available for all operations to users that created them! I am almost sure it is a known problem, but was not yet able to find duplicates in bugs database.

Thank you for your patience.

Looks like duplicate of bug #2317

Consolidating this and the related bugs as duplicates of bug #27480