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