Our users log into the production database as a fairly low-level user, with SELECT granted at the database level, and INSERT/UPDATE/DELETE granted on the specific tables they need access to.
They also have permissions to create temporary tables (we need them for some of the more complicated queries). The problem is that whilst they can create the temporary tables, they don't have access to INSERT into them!
A workaround we have found is to create a "real" (persistent?) table of the same name (but with only one field) and grant access for them to insert into that. Then, when the temporary table is created with the same name, the system will use that, not the persistent table.
mysql> CREATE TEMPORARY TABLE `testing` (`id` INTEGER AUTO_INCREMENT PRIMARY KEY, `name` VARCHAR(30));
Query OK, 0 rows affected (0.04 sec)
mysql> INSERT INTO `testing` (`name`) VALUES ('testing');
ERROR 1142 (42000): INSERT command denied to user 'stduser'@'office.companyname.co.uk' for table 'testing'
If you try to grant access to the table (in another session, as root), you can't:
mysql> GRANT INSERT ON testdb.testing TO 'stduser'@'%';
ERROR 1146 (42S02): Table 'testdb.testing' doesn't exist
So my question is, basically, can we grant INSERT/UPDATE/DELETE on temporary tables without having a "persistent" table of the same name hanging around?