Is there a better way to assign permissions to temporary tables in MySQL?

Question

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?

Answer 1

According to the MySQL reference :

MySQL enables you to grant privileges on databases or tables that do not exist. For tables, the privileges to be granted must include the CREATE privilege.

So, try including CREATE permission on the grant statement above.