MySQL Bugs: #74713: Inconsistent error codes returned on lack of access for EXPLAIN UPDATE with view

Bug #74713	Inconsistent error codes returned on lack of access for EXPLAIN UPDATE with view
Submitted:	6 Nov 2014 12:44	Modified:	7 Nov 2014 8:18
Reporter:	Elena Stepanova	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Security: Privileges	Severity:	S3 (Non-critical)
Version:	5.6, 5.7, 5.6.22	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
The test case provided in 'How to repeat' section attempts to run 3 statements, 2 UPDATEs and 1 DELETE, and EXPLAINs for them. 
The user has SELECT privilege for the view which is being updated/deleted from, and ALL privileges for all involved base tables. 

As expected, all three statements cause identical ER_TABLEACCESS_DENIED_ERROR. However, for EXPLAINs it's different:
- single-table UPDATE causes ER_TABLEACCESS_DENIED_ERROR;
- multi-table DELETE causes ER_TABLEACCESS_DENIED_ERROR;
- multi-table UPDATE causes ER_VIEW_NO_EXPLAIN.

It's not harmful per se, but somewhat suspicious.

Result of the test when executed via MTR:

At line 36: query 'EXPLAIN UPDATE v1, t2 SET v1.a = v1.a + 1 WHERE v1.a = t2.a' failed with wrong errno 1345: 'EXPLAIN/SHOW can not be issued; lacking privileges for underlying table', instead of 1142...

How to repeat:
CREATE DATABASE privtest_db;

CREATE TABLE privtest_db.t1 (a INT);
CREATE TABLE privtest_db.t2 (a INT);
INSERT INTO privtest_db.t2 VALUES (1), (2), (3);
CREATE VIEW privtest_db.v1 (a) AS SELECT a FROM privtest_db.t1;

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

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

# Single-table UPDATE, multi-table DELETE, multi-table UPDATE all return
# the same ER_TABLEACCESS_DENIED_ERROR
# because there is no UPDATE/DELETE grants on v1

--error ER_TABLEACCESS_DENIED_ERROR
UPDATE v1 SET v1.a = v1.a + 1 WHERE v1.a = 2;
--error ER_TABLEACCESS_DENIED_ERROR
DELETE v1 FROM v1, t2 WHERE v1.a = t2.a;
--error ER_TABLEACCESS_DENIED_ERROR
UPDATE v1, t2 SET v1.a = v1.a + 1 WHERE v1.a = t2.a;

# Single-table UPDATE and multi-table DELETE both return ER_TABLEACCESS_DENIED_ERROR
# while the multi-table UPDATE returns ER_VIEW_NO_EXPLAIN

--error ER_TABLEACCESS_DENIED_ERROR
EXPLAIN UPDATE v1 SET v1.a = v1.a + 1 WHERE v1.a = 2;
--error ER_TABLEACCESS_DENIED_ERROR
EXPLAIN DELETE v1 FROM v1, t2 WHERE v1.a = t2.a;

--error ER_TABLEACCESS_DENIED_ERROR
EXPLAIN UPDATE v1, t2 SET v1.a = v1.a + 1 WHERE v1.a = t2.a;

Hello Elena,

Thank you for the report and test case.

Thanks,
Umesh

// 5.6.22

[ushastry@cluster-repo mysql-test]$ ./mysql-test-run.pl 74713
Logging: ./mysql-test-run.pl  74713
2014-11-09 14:56:37 23985 [Warning] Buffered warning: Changed limits: max_open_files: 1024 (requested 5000)

2014-11-09 14:56:37 23985 [Warning] Buffered warning: Changed limits: table_cache: 431 (requested 2000)

2014-11-09 14:56:37 23985 [Note] Plugin 'FEDERATED' is disabled.
2014-11-09 14:56:37 23985 [Note] Binlog end
2014-11-09 14:56:37 23985 [Note] Shutting down plugin 'CSV'
2014-11-09 14:56:37 23985 [Note] Shutting down plugin 'MyISAM'
MySQL Version 5.6.22
Checking supported features...
 - SSL connections supported
Collecting tests...
Removing old var directory...
Creating var directory '/data/ushastry/server/mysql-advanced-5.6.22/mysql-test/var'...
Installing system database...

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

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

worker[1] Using MTR_BUILD_THREAD 300, with reserved ports 13000..13009
CREATE DATABASE privtest_db;
CREATE TABLE privtest_db.t1 (a INT);
CREATE TABLE privtest_db.t2 (a INT);
INSERT INTO privtest_db.t2 VALUES (1), (2), (3);
CREATE VIEW privtest_db.v1 (a) AS SELECT a FROM privtest_db.t1;
GRANT ALL    ON privtest_db.t1 TO 'privtest'@'localhost';
GRANT ALL    ON privtest_db.t2 TO 'privtest'@'localhost';
GRANT SELECT ON privtest_db.v1 TO 'privtest'@'localhost';
USE privtest_db;
UPDATE v1 SET v1.a = v1.a + 1 WHERE v1.a = 2;
ERROR 42000: UPDATE command denied to user 'privtest'@'localhost' for table 'v1'
DELETE v1 FROM v1, t2 WHERE v1.a = t2.a;
ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 'v1'
UPDATE v1, t2 SET v1.a = v1.a + 1 WHERE v1.a = t2.a;
ERROR 42000: UPDATE command denied to user 'privtest'@'localhost' for table 'v1'
EXPLAIN UPDATE v1 SET v1.a = v1.a + 1 WHERE v1.a = 2;
ERROR 42000: UPDATE command denied to user 'privtest'@'localhost' for table 'v1'
EXPLAIN DELETE v1 FROM v1, t2 WHERE v1.a = t2.a;
ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 'v1'
main.74713                               [ fail ]
        Test ended at 2014-11-09 14:56:51

CURRENT_TEST: main.74713
mysqltest: At line 35: query 'EXPLAIN UPDATE v1, t2 SET v1.a = v1.a + 1 WHERE v1.a = t2.a' failed with wrong errno 1345: 'EXPLAIN/SHOW can not be issued; lacking privileges for underlying table', instead of 1142...