Allow write access only to temporary tables?
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