User Can't Select His Tmp_table

[tobiha]

Hello,

I have granted a user the rights to create_tmp_table, but when he tries to select it afterworths, he doesn't have the right priviliges. How do I grant him permission to the the tmp_table, without giving him select priv. to the whole db?

[peterlaursen]

If it is a temporary table created like “CREATE TEMPORARY TABLE … ” then only the user and session who created it we be able to access ti. Temporary tables in MySQL are session-based. A session does not know about temporary tables created in other sessions.

[tobiha]

Yes, the tmp table is created by the user himself, but he can't “select” the table, unless I give him select priv. for the whole database.

[peterlaursen]

ok … this is a server issue then.

I find this old and somewhat realted bug report: http://bugs.mysql.com/bug.php?id=179 . It is marked as 'closed' but I do not find a conclusion!

Let me try to research a little!

[peterlaursen]

For me it is like you tell:

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'

*/

BUT: If I as root “GRANT ALL ON test.t99 TO 'temptest'@'localhost';” then user 'temptest' will be able to “CREATE TEMPORARY TABLE t99 ..” and INSERT and SELECT etc. as well. So this is the workaround: GRANT ALL on specific table names and tell user only to use those names for temporary tables.

I have created a new bug report with MySQL: http://bugs.mysql.com/bug.php?id=45653

(good chances that they say *not a bug*! 🙂 )

[tobiha]

thanks, by researching a little I found a lot of other posts in different forums about the subject. No solutions though. Thanks again, Peter! *rart at se der er andre danskere herinde*.

[Author]

Posts