Search code examples
phpmysqltemp-tablessql-grant

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'
Solution

  • 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.