Bug #21100 Permission model does not allow for a read only user with temporary tables
Submitted: 17 Jul 2006 18:37 Modified: 18 Sep 2009 16:07
Reporter: Cristian Gafton Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Security: Privileges Severity:S4 (Feature request)
Version:5.0.22 OS:Any (all)
Assigned to: CPU Architecture:Any

[17 Jul 2006 18:37] Cristian Gafton 
Description:
Assume you have a database with several tables, and an application that needs to connect to it with read-only permissions. IF this application needs to use temporary tables for whatever reasons, one would have to give up the read-only access and grant write access for that application/user.

How to repeat:
GRANT SELECT,CREATE TEMPORARY TABLES on db.* to 'foo'@'localhost';

'foo' can now create temporary tables, but it can not insert/delete from those. Since the  temporary tables are not defined at the time of the grant, we need to grant additional privileges to get the temporary tables working:

GRANT INSERT,DELETE,UPDATE on db.* to 'foo'@'localhost';

(since granting the same on db.tmpTbl will result in an invalid Table error)

Suggested fix:
MySQL should have a more generic 'TEMPORARY TABLES' privilege chich should allow one not only to create, but also to work with (insert/delete/update) and drop temporary tables, while still allowing only for 'SELECT' on the real tables.
[18 Jul 2006 16:42] Valeriy Kravchuk 
Thank you for a reasonable feature request.
[21 Nov 2008 9:23] Sveta Smirnova 
Looks like duplicate of bug #2317
[18 Sep 2009 16:07] MySQL Verification Team 
Consolidating this and the related bugs as duplicates of bug #27480