Issue Details (XML | Word | Printable)

Key: CORE-6247
Type: Bug Bug
Status: Open Open
Priority: Major Major
Assignee: Alexander Peshkov
Reporter: Pavel Zotov
Votes: 0
Watchers: 4
Operations

If you were logged in you would be able to see more operations.
Firebird Core

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

Created: 15/Feb/20 07:28 AM   Updated: 17/Feb/20 07:47 AM
Component/s: Engine, Security
Affects Version/s: 4.0 Beta 1, 2.5.9, 3.0.5
Fix Version/s: None

QA Status: No test


 Description  « Hide
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.


 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
There are no comments yet on this issue.