[MDEV-7042] SELECT privilege is not checked for views on UPDATE which references to columns Created: 2014-11-07  Updated: 2023-04-27

Status: Confirmed
Project: MariaDB Server
Component/s: OTHER, Views
Affects Version/s: 5.5.40, 10.0.14, 10.2, 10.3, 10.4, 10.5, 10.6
Fix Version/s: 10.4, 10.5, 10.6

Type: Bug Priority: Major
Reporter: Elena Stepanova Assignee: Oleksandr Byelkin
Resolution: Unresolved Votes: 0
Labels: upstream-fixed


 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;



 Comments   
Comment by Elena Stepanova [ 2021-09-23 ]

Still reproducible on current 10.2-10.7.
Not reproducible on MySQL 5.7. I don't have 5.6 handy to check.

Comment by Elena Stepanova [ 2021-09-23 ]

Please note that the test case has --disable_abort_on_error, so it never actually fails, it just demonstrates the outcome of the queries. Please remember to change it after fixing, to set proper expected errors instead of the blank suppression.

Generated at Thu Feb 08 07:16:31 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.