Friday, September 11, 2009

FINE GAINED AUDITING

select * from SYS.V_$PWFILE_USERS

execute dbms_fga.add_policy('STLBAS', 'STFETRAN', 'policy1', 'VALDAT > TO_DATE('10022009','DDMMRRRR')');


analyze table STFETRAN compute statistics;


select sqltext from sys.fga_log$;

delete from sys.fga_log$;


-- Add policy on table with autiting condition...
execute dbms_fga.add_policy('HR', 'EMP', 'policy1', 'deptno > 10');
-- Must ANALYZE, this feature works with CBO (Cost Based Optimizer)
analyze table EMP compute statistics;

select * from EMP where c1 = 11; -- Will trigger auditing
select * from EMP where c1 = 09; -- No auditing

-- Now we can see the statments that triggered the auditing condition...
select sqltext from sys.fga_log$;
delete from sys.fga_log$;

No comments: