Bug #62949 Multiple DELETE permission bug
Submitted: 26 Oct 2011 18:36 Modified: 27 Oct 2011 3:25
Reporter: Fabrício Godoy Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DML Severity:S2 (Serious)
Version:5.5.17 OS:Linux (CentOS 5.7)
Assigned to: CPU Architecture:Any
Tags: delete, permission
Triage: Needs Triage: D2 (Serious)

[26 Oct 2011 18:36] Fabrício Godoy
Description:
When I try a multiple delete of temporary tables I get following error:

Error Code: 1142. DELETE command denied to user 'testuser'@'localhost' for table 'temptest'

How to repeat:
CREATE DATABASE `testdb`;

CREATE DATABASE `temporary`;

CREATE USER 'testuser'@'localhost' IDENTIFIED BY '123teste';

GRANT CREATE, CREATE TEMPORARY TABLES, DELETE, DROP, INSERT, SELECT, UPDATE ON `temporary`.* TO 'testuser'@'localhost';

GRANT SELECT, EXECUTE ON `testdb`.* TO 'testuser'@'localhost';
/*
* If DELETE is granted on testdb when procedure is executed with no errors.
*
* GRANT SELECT, EXECUTE, DELETE ON `testdb`.* TO 'testuser'@'localhost';
*/

/* ============================== */

DELIMITER $$

CREATE

DEFINER = 'testuser'@'localhost'

PROCEDURE `testdb`.`proc_test` ()

BEGIN

	CREATE TEMPORARY TABLE `temporary`.`temptest` (

		`id` INT DEFAULT '0' NOT NULL

	);

	CREATE TEMPORARY TABLE `temporary`.`temptest2`

		LIKE `temporary`.`temptest`;

	INSERT INTO `temporary`.`temptest`

		VALUES (1), (3), (5), (7);

	INSERT INTO `temporary`.`temptest2`

		VALUES (2), (4), (6), (8);

	DELETE tb1, tb2

		FROM `temporary`.`temptest` AS tb1,

		`temporary`.`temptest2` AS tb2

		WHERE tb1.`id` < 5

			OR tb2.`id` < 5;

	DROP TEMPORARY TABLE `temporary`.`temptest`;

	DROP TEMPORARY TABLE `temporary`.`temptest2`;

END$$

DELIMITER ;

/* Just try to execute `proc_test` */
[26 Oct 2011 18:42] Fabrício Godoy
If I replace multiple delete to:

=============================================
DELETE

	FROM `temporary`.`temptest`

	WHERE `id` < 5;

DELETE

	FROM `temporary`.`temptest2`

	WHERE `id` < 5;
=============================================

Executes fine.
[26 Oct 2011 18:48] Fabrício Godoy
If I replace DELETE to the following:

====================================================
	DELETE `temporary`.`temptest`, `temporary`.`temptest2`

		FROM `temporary`.`temptest` AS tb1,

		`temporary`.`temptest2` AS tb2

		WHERE tb1.`id` < 5

			OR tb2.`id` < 5;
====================================================

I get (regardless 'real' temptest exists or not):
Error Code: 1109. Unknown table 'temptest' in MULTI DELETE
[26 Oct 2011 18:56] Fabrício Godoy
The following works fine:

============================================================
DELETE `temporary`.`temptest`, `temporary`.`temptest2`

	FROM `temporary`.`temptest`,

	`temporary`.`temptest2`

	WHERE `temporary`.`temptest`.`id` < 5

		OR `temporary`.`temptest2`.`id` < 5;
============================================================
[27 Oct 2011 3:25] Valeriy Kravchuk
Thank you for the bug report. Verified on Mac OS X:

macbook-pro:5.5 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.5.17-debug Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE DATABASE `testdb`;
Query OK, 1 row affected (0.03 sec)

mysql> CREATE DATABASE `temporary`;
Query OK, 1 row affected (0.00 sec)

mysql> CREATE USER 'testuser'@'localhost' IDENTIFIED BY '123teste';
Query OK, 0 rows affected (0.10 sec)

mysql> GRANT CREATE, CREATE TEMPORARY TABLES, DELETE, DROP, INSERT, SELECT, UPDATE ON
    -> `temporary`.* TO 'testuser'@'localhost';
Query OK, 0 rows affected (0.03 sec)

mysql> GRANT SELECT, EXECUTE ON `testdb`.* TO 'testuser'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER $$
mysql> CREATE
    -> DEFINER = 'testuser'@'localhost'
    -> PROCEDURE `testdb`.`proc_test` ()
    -> BEGIN
    ->  CREATE TEMPORARY TABLE `temporary`.`temptest` (
    ->   `id` INT DEFAULT '0' NOT NULL
    ->  );
    ->  CREATE TEMPORARY TABLE `temporary`.`temptest2`
    ->   LIKE `temporary`.`temptest`;
    ->  INSERT INTO `temporary`.`temptest`
    ->   VALUES (1), (3), (5), (7);
    ->  INSERT INTO `temporary`.`temptest2`
    ->   VALUES (2), (4), (6), (8);
    ->  DELETE tb1, tb2
    ->   FROM `temporary`.`temptest` AS tb1,
    ->   `temporary`.`temptest2` AS tb2
    ->   WHERE tb1.`id` < 5
    ->    OR tb2.`id` < 5;
    ->  DROP TEMPORARY TABLE `temporary`.`temptest`;
    ->  DROP TEMPORARY TABLE `temporary`.`temptest2`;
    -> END$$
Query OK, 0 rows affected (0.16 sec)

mysql> DELIMITER ;
mysql> call testdb.proc_test();
ERROR 1142 (42000): DELETE command denied to user 'testuser'@'localhost' for table 'temptest'