
If you were logged in you would be able to see more operations.
|
|
|
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.
|
Description
|
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.
|
Show » |
There are no comments yet on this issue.
|
|