MySQL Bugs: #74733: SELECT privilege is not checked for views on UPDATE which references to columns

Bug #74733	SELECT privilege is not checked for views on UPDATE which references to columns
Submitted:	7 Nov 2014 13:02	Modified:	7 Nov 2014 16:11
Reporter:	Elena Stepanova	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Security: Privileges	Severity:	S3 (Non-critical)
Version:	5.5, 5.6, 5.7	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
According to the manual, 
<quote>
SELECT is needed for columns referenced on the right hand side of col_name=expr assignment in UPDATE statements or for columns named in the WHERE clause of DELETE or UPDATE statements.
</quote>
(http://dev.mysql.com/doc/refman/5.6/en/privileges-provided.html#priv_select)

Neither is true for updates on a view.

The test case provided in 'How to repeat' section compares update on a view to update on a base table. 

In the first part of the test, we only grant UPDATE privilege on the base table. Expectedly, both 
UPDATE <table> SET <column> = <expression with column>
and
UPDATE <table> SET ... WHERE <condition with column>
fail due to the lack of SELECT privilege.

Then we revoke the UPDATE privilege on the base table, instead grant the UPDATE privilege on the view and run the same updates on the view. 
Both succeed, even though there is no SELECT privilege on the column which is used in the expression and WHERE condition. 

Partial output of the test case:

connection default;
GRANT UPDATE ON privtest_db.t1 TO 'privtest'@'localhost';
connection con1;
UPDATE privtest_db.t1 SET a = a + 1;
ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for column 'a' in table 't1'
UPDATE privtest_db.t1 SET a = 10 WHERE a > 3;
ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for column 'a' in table 't1'
connection default;
REVOKE ALL ON privtest_db.t1 FROM 'privtest'@'localhost';
GRANT UPDATE ON privtest_db.v1 TO 'privtest'@'localhost';
connection con1;
UPDATE privtest_db.v1 SET a = a + 1;
UPDATE privtest_db.v1 SET a = 10 WHERE a > 3;

Sorry if it's a duplicate of something, it's hard to find bugs like that. 
If it's the expected behavior, maybe the manual should mention it as an exception to the rule it describes. 

How to repeat:
--disable_abort_on_error
--enable_connect_log

CREATE DATABASE privtest_db;

CREATE TABLE privtest_db.t1 (a INT);
CREATE VIEW privtest_db.v1 AS SELECT * FROM privtest_db.t1;
CREATE USER 'privtest'@'localhost';

INSERT INTO privtest_db.t1 VALUES (1), (2), (3);

connect(con1,localhost,privtest,,);

connection default;
GRANT UPDATE ON privtest_db.t1 TO 'privtest'@'localhost';
connection con1;

UPDATE privtest_db.t1 SET a = a + 1;
UPDATE privtest_db.t1 SET a = 10 WHERE a > 3;

connection default;
REVOKE ALL ON privtest_db.t1 FROM 'privtest'@'localhost';
GRANT UPDATE ON privtest_db.v1 TO 'privtest'@'localhost';
connection con1;

UPDATE privtest_db.v1 SET a = a + 1;
UPDATE privtest_db.v1 SET a = 10 WHERE a > 3;

Thank you for the bug report.

s supported
Collecting tests...
Checking leftover processes...
Removing old var directory...
Creating var directory 'C:/2014.11.02/mysql-5.6/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;
CREATE TABLE privtest_db.t1 (a INT);
CREATE VIEW privtest_db.v1 AS SELECT * FROM privtest_db.t1;
CREATE USER 'privtest'@'localhost';
INSERT INTO privtest_db.t1 VALUES (1), (2), (3);
connect con1,localhost,privtest,,;
connection default;
GRANT UPDATE ON privtest_db.t1 TO 'privtest'@'localhost';
connection con1;
UPDATE privtest_db.t1 SET a = a + 1;
ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for column 'a' in table 't1'
UPDATE privtest_db.t1 SET a = 10 WHERE a > 3;
ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for column 'a' in table 't1'
connection default;
REVOKE ALL ON privtest_db.t1 FROM 'privtest'@'localhost';
GRANT UPDATE ON privtest_db.v1 TO 'privtest'@'localhost';
connection con1;
UPDATE privtest_db.v1 SET a = a + 1;
UPDATE privtest_db.v1 SET a = 10 WHERE a > 3;