Mysql: allow create/insert/delete for temporary tables only

Question

Using MySQL, how can I assign permissions to a user so they can create and manipulate temporary tables, but not alter any of the data in permanent tables?

Alternatively, is there a way for a user with CREATE TEMPORARY TABLES to create a temporary table such that they have full rights for that table?

Answer 1

I understand this doesn't exactly answer your request but...

You may want to handle it using an ACL (Access Control List) of permissions for this user. You want to specify by table what this user can SELECT. Next, create a database specifically for this user's purpose with SELECT,CREATE permissions.

E.g.

GRANT SELECT ON dbname.tablename1 TO 'username'@'ip-or-hostname' IDENIFIED BY 'password';
GRANT SELECT ON dbname.tablename2 TO 'username'@'ip-or-hostname' IDENIFIED BY 'password';
GRANT SELECT ON dbname.tablename3 TO 'username'@'ip-or-hostname' IDENIFIED BY 'password';

GRANT CREATE,SELECT,DROP ON tmpdb.tablename1 TO 'username'@'ip-or-hostname' IDENIFIED BY 'password';
GRANT CREATE,SELECT,DROP ON tmpdb.tablename2 TO 'username'@'ip-or-hostname' IDENIFIED BY 'password';
GRANT CREATE,SELECT,DROP ON tmpdb.tablename3 TO 'username'@'ip-or-hostname' IDENIFIED BY 'password';