You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
Presense of 'WITH CHECK OPTION' in a view DDL disallows UPDATE statement if user has been granted to update some (NOT ALL) columns of this view [CORE6247]
#6491
Consider scenario:
1) create table with three columns (PK and two for 'user-values');
2) create two views on this table:
2.1) first view is trivial, one-to one;
2.2) second view is almost the same but we add 'WITH CHECK OPTION' to its DDL
3) create non-privileged user 'foo' who is given access to:
3.1) select all columns from both views;
3.2) update only columns which store 'user-values', i.e. except PK (also in both views).
UPDATE statement against view which was declared using WITH CHECK OPTION will fail with:
Statement failed, SQLSTATE = 28000
no permission for update/write access to COLUMN V_TEST2.ID
This is script to reproduce:
connect 'localhost:employee' user sysdba password 'masterkey';
set term ^;
execute block as
begin
begin
execute statement 'drop user foo' with autonomous transaction;
when any do begin end
end
end^
set term ;^
commit;
create user foo password 'bar';
commit;
recreate view v_test1 as select 1 d from rdb$database;
recreate view v_test2 as select 1 d from rdb$database;
commit;
recreate table test(id int, x int, y int);
commit;
recreate view v_test1 as select * from test ;
recreate view v_test2 as select * from test where 1=1 with check option;
commit;
grant select on v_test1 to user foo;
grant select on v_test2 to user foo;
grant update (x, y) on v_test1 to user foo;
grant update (x, y) on v_test2 to user foo;
-- grant update on v_test2 to user foo; --------------------------------- [ 1 ]
commit;
insert into test(id) values(1);
commit;
connect 'localhost:employee' user foo password 'bar';
set echo on;
update v_test1 set x = 1, y = 2 where id = 1;
select * from v_test1;
rollback;
update v_test2 set x = 1, y = 2 where id = 1;
select * from v_test2;
PS.
Note on line which is marked as "[ 1 ]".
If we replace "grant update (x, y) on v_test2 to user foo" with this all work fine.
So, it seems that presense COLUMN NAMES in the GRANT statement can affect on result.
The text was updated successfully, but these errors were encountered:
Submitted by: @pavel-zotov
Consider scenario:
1) create table with three columns (PK and two for 'user-values');
2) create two views on this table:
2.1) first view is trivial, one-to one;
2.2) second view is almost the same but we add 'WITH CHECK OPTION' to its DDL
3) create non-privileged user 'foo' who is given access to:
3.1) select all columns from both views;
3.2) update only columns which store 'user-values', i.e. except PK (also in both views).
UPDATE statement against view which was declared using WITH CHECK OPTION will fail with:
Statement failed, SQLSTATE = 28000
no permission for update/write access to COLUMN V_TEST2.ID
This is script to reproduce:
connect 'localhost:employee' user sysdba password 'masterkey';
set term ^;
execute block as
begin
begin
execute statement 'drop user foo' with autonomous transaction;
when any do begin end
end
end^
set term ;^
commit;
create user foo password 'bar';
commit;
recreate view v_test1 as select 1 d from rdb$database;
recreate view v_test2 as select 1 d from rdb$database;
commit;
recreate table test(id int, x int, y int);
commit;
recreate view v_test1 as select * from test ;
recreate view v_test2 as select * from test where 1=1 with check option;
commit;
grant select on v_test1 to user foo;
grant select on v_test2 to user foo;
grant update (x, y) on v_test1 to user foo;
grant update (x, y) on v_test2 to user foo;
-- grant update on v_test2 to user foo; --------------------------------- [ 1 ]
commit;
insert into test(id) values(1);
commit;
connect 'localhost:employee' user foo password 'bar';
set echo on;
update v_test1 set x = 1, y = 2 where id = 1;
select * from v_test1;
rollback;
update v_test2 set x = 1, y = 2 where id = 1;
select * from v_test2;
PS.
Note on line which is marked as "[ 1 ]".
If we replace "grant update (x, y) on v_test2 to user foo" with this all work fine.
So, it seems that presense COLUMN NAMES in the GRANT statement can affect on result.
The text was updated successfully, but these errors were encountered: