[#MDEV-15934] It is possible to replace a row that is not in a view

Comment by Alice Sherepa [ 2018-04-20 ]

view was created as "WHERE test.Account = 1", so :

MariaDB [test_views]> select * from view_test;

+---------+---------+-----------+

| PrimKey | Account | SomeValue |

+---------+---------+-----------+

|       1 |       1 | AAA       |

|       2 |       1 | BBB       |

+---------+---------+-----------+

2 rows in set (0.00 sec)

and after "DELETE FROM view_test WHERE PrimKey = 2;" row with (2,1,BBB) will be deleted
Why do you consider it as a bug?

Comment by Philippe Jean [ 2018-04-20 ]

I'm so sorry, it's for REPLACE statement. I edited the desscription of the case and the query in the description.

My problem is that a user can overwrite a row that he does not normally have access to.

Thank you,

Comment by Alice Sherepa [ 2018-04-26 ]

Thanks for the report! It looks like it is a bug, that row should not be deleted as it is not in a view (if replace=delete+insert)

CREATE TABLE IF NOT EXISTS t1 (id int NOT NULL PRIMARY KEY, a int, b varchar(100)) ENGINE=InnoDb;

INSERT INTO t1 VALUES (1, 1, 'AAA'),(2, 1, 'BBB'),(3, 2, 'CCC'),(4, 2, 'DDD');

CREATE VIEW v1 AS SELECT * FROM t1 WHERE t1.a = 1 WITH CASCADED CHECK OPTION;

SELECT * FROM v1;

REPLACE v1 value (3,1,'ZZZZZ');

SELECT * FROM v1;

MariaDB [test]> REPLACE v1 value (3,1,'ZZZZZ');

Query OK, 2 rows affected (0.133 sec)

MariaDB [test]> SELECT * FROM t1;

+----+------+-------+

| id | a    | b     |

+----+------+-------+

|  1 |    1 | AAA   |

|  2 |    1 | BBB   |

|  3 |    1 | ZZZZZ |

|  4 |    2 | DDD   |

+----+------+-------+

4 rows in set (0.001 sec)

[MDEV-15934] It is possible to replace a row that is not in a view Created: 2018-04-19 Updated: 2022-01-28
Status:	Open
Project:	MariaDB Server
Component/s:	Data Manipulation - Delete
Affects Version/s:	10.2.14
Fix Version/s:	10.2