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;
/
