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
-----------------------------------------------------------------------------------------------------------------------------------------------
Key: CORE-6247
URL: http://tracker.firebirdsql.org/browse/CORE-6247
Project: Firebird Core
Issue Type: Bug
Components: Engine, Security
Affects Versions: 3.0.5, 2.5.9, 4.0 Beta 1
Reporter: 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.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
