Here is the sample solution I provided (where the table owner FGATEST is the user that won't get audited):
conn / as sysdba create user fgatest identified by fgatest; grant connect, resource, dba to fgatest; connect fgatest/fgatest create table foobar (fooid number, bar varchar2(30)); grant select, insert, update, delete on foobar to PUBLIC; BEGIN dbms_fga.add_policy( object_schema => 'FGATEST', object_name => 'FOOBAR', policy_name => 'FOOBAR_AUD', audit_condition => 'sys_context(''userenv'',''session_user'') != ''FGATEST''' , statement_types => 'SELECT,INSERT,UPDATE,DELETE'); end; / insert into foobar select 1, sys_context('userenv','current_user') from dual; insert into foobar select 2, sys_context('userenv','session_user') from dual; select * from foobar; select username from dba_users where account_status='OPEN'; conn tst/tst insert into foobar select 3, sys_context('userenv','current_user') from dual; insert into foobar select 4, sys_context('userenv','current_user') from dual;The script creates the FGATEST user, table, and FGA policy, then adds rows as both FGATEST and TST. Our expectation is that the inserts and selects performed by FGATEST won't be audited, but the ones performed by TST will. Let's check it out:
conn / as sysdba select db_user, os_user, object_schema, object_name, sql_text from dba_fga_audit_trail; DB_USER OS_USER OBJECT_SCHEMA OBJECT_NAME SQL_TEXT -------------------- -------------------- -------------------- -------------------- ---------------------------------------- TST BARCLAY\kengel FGATEST FOOBAR insert into foobar select 3, sys_context('userenv','current_user') from dual TST BARCLAY\kengel FGATEST FOOBAR insert into foobar select 4, sys_context('userenv','current_user') from dual TST BARCLAY\kengel FGATEST FOOBAR select * from foobar
No comments:
Post a Comment