MySQL Forums
Forum List  »  Security

Allow write access only to temporary tables?
Posted by: John Martinez
Date: March 30, 2006 06:31PM

I have a reporting application that, for performance reasons, needs to build some [expensive to create] temporary tables. I would like to structure permissions in the database such that the 'reporting' user can create these tables, write to them, and later drop them, while allow effectively read-only access to the permanent tables.

Any suggestions on how to accomplish this? Conceptually, what I would like to do is something like this:

grant create temporary table, select on myapp.* to 'foo'@'%'
grant insert,update on myapp.temp_* to 'foo'@'%'

The second line is, of course, bogus syntax - I was hoping I could use a naming convention to carve out some tables the reporting user could use without having to knoew the names of those tables...


My next plan was something like this:
grant create temporary table,select,insert,update on myapp.* to 'foo'@'%'
revoke insert,update on myapp.table1 from 'foo'@'%'
revoke insert,update on myapp.table2 from 'foo'@'%'
[...]

but even if the security model worked that way (and I don't think it does,) that scheme has other flaws, too, like if I add a new permanent table I have to reme-mber to revoke priviliges for that table, too.

Ideally, of course, MySQL would have an extra security level:
-- global
-- database-level
--database-wide, but only for temporary tables (WISHING HERE)
-- table-level
-- column-level

and I could do something like:
grant create temporary table on myapp.* to reporter;
grant all on myapp.$ to reporter

(where $ was the magic temporary-table wildcard.)

But enough idle wishing - any suggestions how to get this done? Using 5.0.latest

Options: ReplyQuote


Subject
Views
Written By
Posted
Allow write access only to temporary tables?
6495
March 30, 2006 06:31PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.