Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question

1 Answer 1

up vote 1 down vote accepted

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';
share|improve this answer
    
Will that work for temporary tables, or only for normal tables? –  DrStalker Dec 17 '09 at 6:54
    
Only for normal tables, which is why I started my answer the way that I did. However you can create tables with the ENGINE=MEMORY and you basically have temp tables. –  randy melder Dec 22 '09 at 23:09
    
It's close enough for this case, since I know the temporary table will only be used by one DB connection at a time. –  DrStalker Jan 5 '10 at 7:55

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.