Database Software

According to the Oracle 12c documentation when in Mixed mode audit messages resulting from both Fine Grained Auditing and Standard Auditing (via the CREATE AUDIT POLICY / AUDIT commands) should go to both the traditional audit trails AND the new unified audit trail.  FGA audit messages however appear on the traditional trail table but not on the unified table. Standard Audit messages do not appear on either trail.  I'm running Oracle 12.1.0.2.0

select banner from v$version;

-- Mixed Mode

select * from v$option where parameter = 'Unified Auditing';

-- Audit messages to DB

select name,value from v$parameter where name = 'audit_trail';

create table test_audit_table (id number(11), audit_col_1 varchar2(8), audit_col_2 number(11), condition_col varchar2(3));

Table created.

insert into test_audit_table values (1, 'aaaaa', 11111, 'xxx');

1 row inserted

insert into test_audit_table values (2, 'bbbbb', 22222, 'yyy');

1 row inserted

grant select, update on test_audit_table to public;

Grant complete

Fine Grained Auditing:

BEGIN

DBMS_FGA.ADD_POLICY(

   object_schema         => 'sax'

  ,object_name             => 'test_audit_table'

  ,policy_name             => 'test_audit_1'

  ,audit_condition        => 'condition_col = ''xxx'''

  ,audit_column            => NULL

  ,handler_schema       => NULL

  ,handler_module       => NULL

  ,enable                        => TRUE

  ,statement_types      => 'SELECT'

  ,audit_trail                  => DBMS_FGA.DB_EXTENDED

  ,audit_column_opts  => DBMS_FGA.ANY_COLUMNS

  ,policy_owner             => NULL

   );

EXCEPTION

WHEN OTHERS THEN

    DBMS_OUTPUT.PUT_LINE(SUBSTR(SQLERRM,1,200));

END;

/

PL/SQL procedure successfully completed.

select object_schema, object_name, policy_name, policy_text, policy_column, pf_function, enabled, sel, ins, upd, del, audit_trail, policy_column_options from all_audit_policies;

select * from sax.test_audit_table where condition_col = 'xxx';

select obj$schema, obj$name, policyname, lsqltext from sys.fga_log$ where policyname = 'TEST_AUDIT_1';

Audit messages appear successfully in sys.fga_log$ table

EXEC SYS.DBMS_AUDIT_MGMT.FLUSH_UNIFIED_AUDIT_TRAIL;

PL/SQL procedure successfully completed

select object_schema, object_name, action_name, sql_text, fga_policy_name from unified_audit_trail where lower(object_name) = 'test_audit_table';

No rows returned.

No audit messages appear in unifed_audit_trail table !!!

A similar thing happens with standard audit (via CREATE AUDIT POLICY / AUDIT commands) however in this case the audit messages do not appear in EITHER audit trails

CREATE AUDIT POLICY TEST_AUDIT_2 ACTIONS UPDATE ON SAX.TEST_AUDIT_TABLE;

Unknown successfully completed.

AUDIT POLICY TEST_AUDIT_2;

Audit complete.

select * from audit_unified_enabled_policies where policy_name = 'TEST_AUDIT_2';

select policy_name, object_schema, object_name, audit_option from audit_unified_policies where policy_name like 'TEST_AUDIT_2';

update sax.test_audit_table set audit_col_2 = 33333 where id = 2;

1 row updated

commit;

Commit complete

select * from sys.aud$ where obj$name = 'TEST_AUDIT_TABLE';

No rows returned

select * from dba_audit_trail where obj_name = 'TEST_AUDIT_TABLE';

No rows returned

EXEC SYS.DBMS_AUDIT_MGMT.FLUSH_UNIFIED_AUDIT_TRAIL;

PL/SQL procedure successfully completed

select object_schema, object_name, action_name, sql_text, fga_policy_name from unified_audit_trail where object_name = 'TEST_AUDIT_TABLE';

No rows returned

No audit messages appear on either audit trail !!!