Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

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

Open
firebird-automations opened this issue Feb 15, 2020 · 1 comment

Comments

@firebird-automations
Copy link
Collaborator

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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment