Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Major
-
Resolution: Unresolved
-
5.5.40, 10.0.14, 10.2(EOL), 10.3(EOL), 10.4(EOL), 10.5(EOL), 10.6
Description
According to the MySQL manual,
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.
Neither is true for updates on a view.
--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; |
|
Results |
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; |