This articles contains useful command for both traditional and unified auditing .

1. How to enable auditing:(traditional)

-- Auditing is disabled, when audit_trail is set to NONE

SQL> show parameter audit_trail
NAME               TYPE               VALUE
------------------------------------ ----------- --------------------------
audit_trail       string                NONE

- Either set audit_trail to DB or DB,EXTENDED.

alter system set audit_trail=db scope=spfile;
(or)
alter system set audit_trail=db, extended scope=spfile;

-- Restart the database.

shutdown immediate;
startup;

SQL> show parameter audit_trail
NAME                 TYPE                   VALUE
------------------------------------ ----------- --------------------------
audit_trail           string               DB

2. statement level auditing:

-- Shows the list of statements that can be audited

select * from STMT_AUDIT_OPTION_MAP;

-- Enable  statement level auditing:

audit table by DBACLASS. 

audit table by DBACLASS whenever successful;

audit role by DBACLASS;

-- To disable auditing:

noaudit table by DBACLASS;

-- find statements audited in the database:
col user_name for a12 heading "User name"
col audit_option format a30 heading "Audit Option"
set pages 1000
prompt
prompt System auditing options across the system and by user
select user_name,audit_option,success,failure from sys.dba_stmt_audit_opts
order by user_name, proxy_name, audit_option
/

3. object level auditing:

audit insert,update on DBACLASS.EMP by MANAGER;
AUDIT delete on DBACLASS.EMP;



-- disable auditing:
noaudit insert,update on DBACLASS.EMP by MANAGER;
noAUDIT delete on DBACLASS.EMP by MANAGER;


-- Audit SELECT/DML activites done by an user(DBACLASS):

audit select table,insert table,update table,delete table by DBACLASS by access;
audit execute procedure by dbaclass by access;
audit all by dbaclass by access;

4. Privilege level auditing:

-- Enable privilege auditing:

audit drop any table ;
audit create table;
audit drop user;

-- Find privileges audited in the database:
col user_name for a12 heading "User name"
col privilege for a30 heading "Privilege"
set pages 1000
prompt
prompt System Privileges audited across system
select user_name,privilege,success,failure from dba_priv_audit_opts
order by user_name, proxy_name, privilege
/

5. Find audit records of a user:

col user_name for a12 heading "User name"
col timest format a13
col userid format a8 trunc
col obn format a10 trunc
col name format a13 trunc
col object_name format a10
col object_type format a6
col priv_used format a15 trunc
set verify off
set pages 1000
SET PAGESIZE 200
SET LINES 299
select username userid, to_char(timestamp,'dd-mon hh24:mi') timest ,
action_name acname, priv_used, obj_name obn, ses_actions
from sys.dba_audit_trail
where timestamp>sysdate-&HOURS*(1/24) and username='&USER_NAME'
order by timestamp
/

6. Enable auditing for sys user:

SQL>ALTER SYSTEM SET audit_sys_operations=true SCOPE=spfile;

SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP
SQL> show parameter audit_sys_operations

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_sys_operations boolean TRUE

7. Enable pure unified auditing:

 

For more info on unified auditing refer – > Unified auditing in oracle 12c 

-- False means mixed auditing;
SELECT value FROM v$option WHERE parameter = 'Unified Auditing';
VALUE
-----------------
FALSE

-- relink the library as mentioned.
shutdown immediate;

cd $ORACLE_HOME/rdbms/lib
make -f ins_rdbms.mk unaiaud_on ioracle

startup

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

8. View unified audit policies present in db:

-- False means mixed auditing;
SELECT value FROM v$option WHERE parameter = 'Unified Auditing';
VALUE
-----------------
FALSE

-- relink the library as mentioned.
shutdown immediate;

cd $ORACLE_HOME/rdbms/lib
make -f ins_rdbms.mk unaiaud_on ioracle

startup

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

9. View unified audit records:

- Unified report for last 1 hour:
set lines 299
col SQL_TEXT for a23
col action_name for a18
col UNIFIED_AUDIT_POLICIES for a23
select action_name,SQL_TEXT,UNIFIED_AUDIT_POLICIES ,EVENT_TIMESTAMP from unified_AUDIT_trail
where EVENT_TIMESTAMP > sysdate -1/24

10. Create unified audit policy:

-- Create audit policy with audit options:

create audit policy test_case2
ACTIONS CREATE TABLE,
INSERT ON classdba.EMP_TAB,
TRUNCATE TABLE,
select on classdba.PROD_TAB;

select POLICY_NAME,audit_option,AUDIT_CONDITION,OBJECT_SCHEMA,OBJECT_NAME FROM
AUDIT_UNIFIED_POLICIES where POLICY_NAME='TEST_CASE2';

-- Enable policy:

audit policy TEST_CASE2;

select distinct policy_name from AUDIT_UNIFIED_ENABLED_POLICIES where policy_name='TEST_CASE2';

11 . Exclude particular user from audit policy:

SQL> noaudit policy TEST_CASE2;
 
Noaudit succeeded.
 
SQL> audit policy TEST_CASE2 except stcdba;
 
Audit succeeded.
 
SQL> select USER_NAME,POLICY_NAME,ENABLED_OPT from AUDIT_UNIFIED_ENABLED_POLICIES where POLICY_NAME='TEST_CASE2';
 
USER_NAME     POLICY_NAME  ENABLED_OPT
------------- ------------ -----------------------
CLASSDBA        TEST_CASE2   EXCEPT
 

12. Purge audit table using dbms package:

-- Move aud$ table to new tablespace if present under SYSTEM tablespace:

select owner,segment_name,segment_type,tablespace_name,bytes/1024/1024 from dba_segments where segment_name='AUD$';
 
OWNER        SEGMENT_NAME SEGMENT_TYPE       TABLESPACE_NAME                BYTES/1024/1024
------------ ------------ ------------------ ------------------------------ ---------------
SYS          AUD$         TABLE              SYSTEM                                   176
 
 
SQL>
BEGIN
DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
audit_trail_location_value => 'TS_AUDIT');
END;
/
 
SQL>   
 
PL/SQL procedure successfully completed.



-- Initialise cleanup:



SQL>
BEGIN
  DBMS_AUDIT_MGMT.init_cleanup(
    audit_trail_type         => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,
    default_cleanup_interval => 12 /* hours */);
END;
/  
 
PL/SQL procedure successfully completed.

-- set archive duration:

BEGIN
  DBMS_AUDIT_MGMT.set_last_archive_timestamp(
    audit_trail_type  => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
    last_archive_time => SYSTIMESTAMP-30);
END;
/ 



-- Run the purge job:

BEGIN
  DBMS_AUDIT_MGMT.clean_audit_trail(
   audit_trail_type        => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
   use_last_arch_timestamp => TRUE);
END;
/