Subject | Re: access rights error when updating on a view with triggers |
---|---|
Author | Doru Ilasi |
Post date | 2008-05-16T06:12:29Z |
Ok, the code was refactored : the problem is the same.
A table TEST(F1,F2) has an TEST_VW wiev. If an grantee (for updating)
user is trying to update the field F1 whom will trigger (by
TEST_VW_BU) the update of field F2 his action will raise an access
rights error. See code below.
Does someone know why this happen?
TIA,
Doru
-- run this as SYSDBA in a new database
set autoddl on;
create table test(f1 varchar(1),f2 varchar(1), constraint test_pk
primary key (f1)) ;
create view test_vw(f1,f2) as select f1,f2 from test;
set term ^; create trigger test_vw_bu for test_vw active before update
as begin
if (new.f1 = 'Y') then begin
update test_vw set f2 = 'N' where f1 = new.f1;
end else begin
update test_vw set f2 = 'Y' where f1 = new.f1;
end
end^ set term ;^
revoke all on test_vw from USERNAME;
grant select on test_vw to USERNAME;
grant update (f1) on test_vw to USERNAME;
grant all on test to test_vw_bu;
set autoddl off;
-- login as USERNAME
update test_vw set f1='Y';
will raise : Stetement failed, SQLCODE = -551
No permission for update/write access to COLUMN F2
A table TEST(F1,F2) has an TEST_VW wiev. If an grantee (for updating)
user is trying to update the field F1 whom will trigger (by
TEST_VW_BU) the update of field F2 his action will raise an access
rights error. See code below.
Does someone know why this happen?
TIA,
Doru
-- run this as SYSDBA in a new database
set autoddl on;
create table test(f1 varchar(1),f2 varchar(1), constraint test_pk
primary key (f1)) ;
create view test_vw(f1,f2) as select f1,f2 from test;
set term ^; create trigger test_vw_bu for test_vw active before update
as begin
if (new.f1 = 'Y') then begin
update test_vw set f2 = 'N' where f1 = new.f1;
end else begin
update test_vw set f2 = 'Y' where f1 = new.f1;
end
end^ set term ;^
revoke all on test_vw from USERNAME;
grant select on test_vw to USERNAME;
grant update (f1) on test_vw to USERNAME;
grant all on test to test_vw_bu;
set autoddl off;
-- login as USERNAME
update test_vw set f1='Y';
will raise : Stetement failed, SQLCODE = -551
No permission for update/write access to COLUMN F2