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

mysql mysql-error-1146 mysql-error-1142
Drarok picture Drarok · Dec 10, 2009 · Viewed 8.4k times · Source

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

David Gelhar picture David Gelhar · Mar 9, 2010

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.

Related questions

Mysql 1050 Error "Table already exists" when in fact, it does not

I'm adding this table: CREATE TABLE contenttype ( contenttypeid INT UNSIGNED NOT NULL AUTO_INCREMENT, class VARBINARY(50) NOT NULL, packageid INT UNSIGNED NOT NULL, canplace ENUM('0','1') NOT NULL DEFAULT '0', cansearch ENUM('0','1') …

Read More
Bug? #1146 - Table 'xxx.xxxxx' doesn't exist

I am using windows XP. I am creating a table in phpMyAdmin using its built-in create table feature, my database name is ddd. It generates the following code: CREATE TABLE `ddd`.`mwrevision` ( `asd` INT NOT NULL AUTO_INCREMENT PRIMARY KEY , `…

Read More
MySQL Table does not exist error, but it does exist

Does anyone know under what conditions you can receive an 1146: Table '<database>.<table>' doesn't exist error when your table does, in fact, exist? I use the same code on 5 servers, only one that I …

Read More