Ben Holness Ben Holness - 6 months ago 26
SQL Question

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

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'

Answer

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.