Bug #62949 Multiple DELETE permission bug

• Submitted: 26 Oct 2011 18:36
• Modified: 27 Oct 2011 3:25
• Reporter: Fabrício Godoy
• Status: Verified
• Category: MySQL Server: DML
• Severity: S2 (Serious)
• Version: 5.5.17
• OS: Linux (CentOS 5.7)
• CPU Architecture: Any
• Tags: delete, permission

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