Unified Audit

References:
http://docs.oracle.com/database/121/SQLRF/statements_5001.htm#SQLRF56055
http://docs.oracle.com/database/121/DBSEG/auditing.htm#DBSEG630
https://docs.oracle.com/database/121/TDPSG/GUID-BF747771-01D1-4BFB-8489-08988E1181F6.htm#TDPSG94453

Tested on:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics, Real Application Testing
and Unified Auditing options

Please note that Unified Auditing options is enabled. Check your license
The link below describe how to enable.
https://docs.oracle.com/database/121/TDPSG/GUID-BF747771-01D1-4BFB-8489-08988E1181F6.htm#TDPSG50000

SELECT value FROM v$option WHERE parameter = 'Unified Auditing';

VALUE
----------------------------------------------------------------
TRUE

1.) Listing All privileges:

select name
FROM   system_privilege_map
ORDER BY name;

2.) Creating users:

create user T_AUD_1 identified by test;
create user T_AUD_2 identified by test;
create user T_AUD_3 identified by test;

grant connect, resource to     t_aud_1,t_aud_2,t_aud_3;
alter user t_aud_1 quota unlimited on users;
alter user t_aud_2 quota unlimited on users;
alter user t_aud_3 quota unlimited on users;
grant create view to t_aud_1;

3.) Policies:

CREATE AUDIT POLICY PLCY_T_AUD_01
  PRIVILEGES CREATE VIEW
  WHEN    'SYS_CONTEXT(''USERENV'', ''SESSION_USER'') = ''T_AUD_1'''
  EVALUATE PER SESSION;    

audit policy PLCY_T_AUD_01;

CREATE AUDIT POLICY PLCY_T_AUD_02
  PRIVILEGES CREATE TABLE
  WHEN    'SYS_CONTEXT(''USERENV'', ''SESSION_USER'') = ''T_AUD_2'''
  EVALUATE PER SESSION;    

audit policy PLCY_T_AUD_02;

CREATE AUDIT POLICY PLCY_T_AUD_03
  PRIVILEGES CREATE SEQUENCE
  WHEN    'SYS_CONTEXT(''USERENV'', ''SESSION_USER'') = ''T_AUD_3'''
  EVALUATE PER SESSION;    

audit policy PLCY_T_AUD_03;

4.) Listing Policies:

col policy_name format a15
col audit_option format a30
col audit_condition format a60

SELECT policy_name,
       audit_option,
       condition_eval_opt,
       audit_condition,OBJECT_SCHEMA,OBJECT_NAME
FROM   audit_unified_policies
where policy_name like '%PLCY_T_AUD_0%';

POLICY_NAME     AUDIT_OPTION      CONDITION AUDIT_CONDITION                                      OBJECT_SCH OBJECT_NAME
--------------- ----------------- --------- ------------------------------------------------------------ ---------- ------
PLCY_T_AUD_01   CREATE VIEW       SESSION   SYS_CONTEXT('USERENV', 'SESSION_USER') = 'T_AUD_1'           NONE       NONE
PLCY_T_AUD_02   CREATE TABLE      SESSION   SYS_CONTEXT('USERENV', 'SESSION_USER') = 'T_AUD_2'           NONE       NONE
PLCY_T_AUD_03   CREATE SEQUENCE   SESSION   SYS_CONTEXT('USERENV', 'SESSION_USER') = 'T_AUD_3'           NONE       NONE

select * from audit_unified_enabled_policies;

USER_NAME       POLICY_NAME             ENABLED_ SUC FAI
--------------- ----------------------- -------- --- ---
ALL USERS       ORA_SECURECONFIG        BY       YES YES
ALL USERS       ORA_LOGON_FAILURES      BY       NO  YES
ALL USERS       PLCY_T_AUD_03           BY       YES YES
ALL USERS       PLCY_T_AUD_01           BY       YES YES
ALL USERS       PLCY_T_AUD_02           BY       YES YES


5.) Testing:

conn t_aud_1/test;
drop table x;
create table x
(num number);

insert into x values (1);
insert into x values (2);
insert into x values (3);
commit;

create view v_aud1 as
select * from x;

conn t_aud_2/test
create table x2
(num number);

insert into x2
values
(1);

commit;

drop table x2;

conn t_aud_3/test
drop sequence seq_aud_3;
create sequence seq_aud_3;

6.) Checking audit trail

COLUMN event_timestamp FORMAT A30
COLUMN dbusername FORMAT A10
COLUMN action_name FORMAT A20
COLUMN object_schema FORMAT A10
COLUMN object_name FORMAT A20

SELECT event_timestamp,
       dbusername,
       action_name,
       object_schema,
       object_name
FROM   unified_audit_trail
WHERE  dbusername like 'T_AUD%'
ORDER BY event_timestamp;

EVENT_TIMESTAMP                DBUSERNAME ACTION_NAME          OBJECT_SCH OBJECT_NAME
------------------------------ ---------- -------------------- ---------- ------------
04-MAR-16 10.08.19.096000 AM   T_AUD_1    LOGON
04-MAR-16 12.24.35.390000 PM   T_AUD_2    CREATE TABLE         T_AUD_2    X2
04-MAR-16 12.39.04.908000 PM   T_AUD_2    CREATE TABLE         T_AUD_2    X2
04-MAR-16 12.39.05.068000 PM   T_AUD_3    CREATE SEQUENCE      T_AUD_3    SEQ_AUD_3
04-MAR-16 12.40.00.640000 PM   T_AUD_1    CREATE VIEW          T_AUD_1    V_AUD1
04-MAR-16 12.40.00.641000 PM   T_AUD_1    SELECT               T_AUD_1    X

7.) Performance:

-- Write immediate on the AUDIT TRAIL (SYNC)
BEGIN
  DBMS_AUDIT_MGMT.set_audit_trail_property(
    audit_trail_type           => DBMS_AUDIT_MGMT.audit_trail_unified,
    audit_trail_property       => DBMS_AUDIT_MGMT.audit_trail_write_mode, 
    audit_trail_property_value => DBMS_AUDIT_MGMT.audit_trail_immediate_write
  );
END;
/

-- Enqueue a message for AUDIT TRAIL (ASYNC)
BEGIN
  DBMS_AUDIT_MGMT.set_audit_trail_property(
    audit_trail_type           => DBMS_AUDIT_MGMT.audit_trail_unified,
    audit_trail_property       => DBMS_AUDIT_MGMT.audit_trail_write_mode, 
    audit_trail_property_value => DBMS_AUDIT_MGMT.audit_trail_queued_write
  );
END;
/

8.) Drop POLICY

NOAUDIT POLICY PLCY_T_AUD_01;
DROP AUDIT POLICY PLCY_T_AUD_01;

NOAUDIT POLICY PLCY_T_AUD_02;
DROP AUDIT POLICY PLCY_T_AUD_02;

NOAUDIT POLICY PLCY_T_AUD_03;
DROP AUDIT POLICY PLCY_T_AUD_03;
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s