Tuesday, April 26, 2011

Fine-Grained Auditing

Someone recently asked about Oracle fine-grained auditing (FGA) and how to apply it to a situation in which "everyone except Joe in HR gets audited".
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: