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

MySQL grants are independent of the object actually existing - you can grant on tables which don't (yet) exist and those permissions would be assigned to a table if it were to be created. This means that you can grant a user permission to create a specific table.

I'd be tempted to create a database just for temp tables, (called, say, temp) and grant the users access to that database. Database-based permissions are much easier to manage than per-object ones.

Answer 2

One way around this will be to create a special database and grant the user write access to it, and then have it create those temporary tables in that special database.

Answer 3

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.