Bug #74738 Multi-table DELETE rejected due to lack of SELECT privilege for no good reason

• Submitted: 7 Nov 2014 15:10
• Modified: 7 Nov 2014 16:51
• Reporter: Elena Stepanova
• Status: Verified
• Category: MySQL Server: Security: Privileges
• Severity: S3 (Non-critical)
• Version: 5.5, 5.6, 5.7
• OS: Any
• CPU Architecture: Any

[7 Nov 2014 15:10] Elena Stepanova

Description:
In the test case provided in 'How to repeat' section, we have full access to the table we join with (to make things more obvious), and DELETE privilege on the table we delete from. The tables are joined unconditionally. 

The DELETE fails due to the lack of SELECT privilege on t1:

SELECT command denied to user 'privtest'@'localhost' for table 't1'

The theory that SELECT is needed for the join does not survive the next test: we add a SELECT privilege on one of two columns in t1 and run the same DELETE again. Now it succeeds. 

Since the JOIN is unconditional, it should either require no SELECT privilege on the table we delete from, or full SELECT on all columns. One column-SELECT should not make any difference. 

How to repeat:
--disable_abort_on_error
--enable_connect_log

CREATE DATABASE privtest_db;
USE privtest_db;

CREATE TABLE privtest_db.t1 (a INT, b INT);
CREATE TABLE privtest_db.t2 (a INT);
INSERT INTO privtest_db.t1 VALUES (1,1), (2,2), (3,3);

GRANT ALL ON privtest_db.t2 TO 'privtest'@'localhost';
GRANT DELETE ON privtest_db.t1 TO 'privtest'@'localhost';

connect(con1,localhost,privtest,,);
USE privtest_db;

# This DELETE fails
DELETE t1 FROM t1, t2;

--connection default
GRANT SELECT(b) ON privtest_db.t1 TO 'privtest'@'localhost';
--connection con1

# Now it succeeds
DELETE t1 FROM t1, t2;

[7 Nov 2014 16:51] MySQL Verification Team

Thank you for the bug report.

Logging: mysql-test-run.pl  bug74738
MySQL Version 5.7.6
Checking supported features...
 - SSL connections supported
Collecting tests...
Removing old var directory...
Creating var directory 'C:/2014.11.02/mysql-trunk/mysql-test/var'...
Installing system database...

==============================================================================

TEST                                      RESULT   TIME (ms) or COMMENT
--------------------------------------------------------------------------

worker[1] Using MTR_BUILD_THREAD 301, with reserved ports 13010..13019
CREATE DATABASE privtest_db;
USE privtest_db;
CREATE TABLE privtest_db.t1 (a INT, b INT);
CREATE TABLE privtest_db.t2 (a INT);
INSERT INTO privtest_db.t1 VALUES (1,1), (2,2), (3,3);
GRANT ALL ON privtest_db.t2 TO 'privtest'@'localhost';
GRANT DELETE ON privtest_db.t1 TO 'privtest'@'localhost';
connect con1,localhost,privtest,,;
USE privtest_db;
DELETE t1 FROM t1, t2;
ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 't1'
connection default;
GRANT SELECT(b) ON privtest_db.t1 TO 'privtest'@'localhost';
connection con1;
DELETE t1 FROM t1, t2;