Is it possible to grant insert, update and delete privileges only on temporary tables with MySQL?

Question

I have a user that has only SELECT and CREATE TEMPORARY TABLES privileges on a slave database, to ensure that the slave doesn't get tables changed outside of replication, however without the ability to INSERT (and UPDATE and DELETE) on any temporary tables that get created, the CREATE TEMPORARY TABLES privilege is fairly useless.

Is it possible to grant these privileges but only for temporary tables?

I am running MySQL 5.5

EDIT: I am using PHP and PDO to run the commands. Perhaps there is an issue with the session, based on @phreakv6's answer

EDIT 2: My full test code:

<?php
$dbh=new PDO("mysql:host=localhost;dbname=scratch;charset=utf8", 'readonly', 'readonlytestingonlypassword',array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));

$prepped=$dbh->prepare("CREATE TEMPORARY TABLE `TempPermissionsTest` (`ID` INT( 11 ) NOT NULL ,`TestVal` INT( 11 ) NOT NULL )");
$prepped->execute();
$err=$prepped->errorInfo();
if ($err[0]>0) print $err[1]." : ".$err[2];

$prepped=$dbh->prepare("INSERT INTO `TempPermissionsTest` (`ID` ,`TestVal`) VALUES (1,3)");
$prepped->execute();
$err=$prepped->errorInfo();
if ($err[0]>0) print $err[1]." : ".$err[2];

$prepped=$dbh->prepare("SELECT * FROM `TempPermissionsTest`");
$prepped->execute();
$err=$prepped->errorInfo();
if ($err[0]>0) print $err[1]." : ".$err[2];

while ($row=$prepped->fetch(PDO::FETCH_ASSOC)) {
    print_r($row);
}
?>

Running this on a user who has only SELECT and CREATE TEMPORARY TABLES privileges results in the following output:

1142 : INSERT command denied to user 'readonly'@'localhost' for table 'TempPermissionsTest'

phreakv6 · Accepted Answer · 2016-06-07T07:19:19.280

4

After a session has created a temporary table, the server performs no further privilege checks on the table. The creating session can perform any operation on the table, such as DROP TABLE, INSERT, UPDATE, or SELECT.

It looks your CREATE TEMPORARY TABLES privilege should give you INSERT, UPDATE and DELETE privileges on your temporary table for the session that created it. Isn't this what you are looking for?

I just noticed your mysql version. For mysql 5.5 I think you are out of luck. Please take a look at this bug. CREATE TEMPORARY TABLES privilege was extended to include INSERT, UPDATE and DELETE only post 5.6.

edited Jun 07 '16 at 07:19

answered Jun 07 '16 at 06:03

phreakv6

2,135
1
9
11

Hmmm, I get a `1142 UPDATE command denied to user` error when I try to update the table, same for `INSERT` and `DELETE` – Ben Holness Jun 07 '16 at 06:47
I think the idea of "Temporary tables" is a non-persistent existence. So the privileges are valid only for the session and not beyond. Are you trying to access the temporary table beyond the session from a different connection perhaps but for the same user? – phreakv6 Jun 07 '16 at 07:04
1

I don't think so. I created a test file with am `INSERT` query on the line after the `CREATE TEMPORARY TABLE` in the code and it fails with the same permission denied error. I am able to `SELECT` without an error, which I think means I am still within the session (otherwise it would say table not found, right?) – Ben Holness Jun 07 '16 at 07:08
Please check my updated answer. I couldn't post link in comment. – phreakv6 Jun 07 '16 at 07:19
Ah, that would do it. I guess I need to look into upgrading to 5.6 – Ben Holness Jun 07 '16 at 07:22
Yes. Please upvote and accept my answer if it was useful. – phreakv6 Jun 07 '16 at 07:23

score 0 · Answer 2 · answered Jun 14 '16 at 06:21

Supplementing the accepted answer with more general info in case people find this question from other dbs.

In every database manager I have worked with, temporary tables have been intended to be session-private and implemented in such a way that the creator has permission on these tables. In MySQL as noted, there is no permission checks on a temp tables.

In PostgreSQL, temporary tables are initially owed by the creator who has full permissions to them. The owner can revoke permissions and even in theory revoke ownership though I am not aware of any applications that actually do this.

I would expect that every other (or even if there are exceptions, almost every other) RDBMS on the planet effectively defaults the temp table permissions to full permissions for the user that creates them. Failure to do this would severely limit the utility of this feature.

Is it possible to grant insert, update and delete privileges only on temporary tables with MySQL?

2 Answers2