Unified Audit

Check Unified Audit records and enable policies in Oracle

check UA is enabled

SQL> SELECT value FROM v$option WHERE parameter = 'Unified Auditing';
VALUE
------
FALSE

bring down db and listener

Go to the $ORACLE_HOME/rdbms/lib directory.

Enable the unified auditing executable.

    UNIX: Run the following command:

    make -f ins_rdbms.mk uniaud_on ioracle ORACLE_HOME=$ORACLE_HOME

bring up now listener and DB.

1. Check unified audit is enabled.

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

create AUDIT user

SQL> create user SEC_ADMIN identified by oracle123;

User created.

SQL> grant AUDIT_ADMIN to SEC_ADMIN;

Grant succeeded.

SQL> grant execute on SYS.DBMS_AUDIT_MGMT to SEC_ADMIN;

Grant succeeded.

SQL>  grant CREATE PROCEDURE,CREATE ROLE,CREATE SESSION,INHERIT ANY PRIVILEGES,SELECT ANY DICTIONARY to SEC_ADMIN;


2. Check the default / enabled polices of unified audit.

SQL> select distinct policy_name from AUDIT_UNIFIED_ENABLED_POLICIES;

POLICY_NAME
--------------------
ORA_LOGON_FAILURES
ORA_SECURECONFIG

connect using SEC_ADMIN user

3. Check all policy details

SQL> SELECT policy_name, enabled_option, entity_name, success, failure
FROM audit_unified_enabled_policies  2  ;

POLICY_NAME          ENABLED_OPTION  ENTITY_NAME          SUC FAI
-------------------- --------------- -------------------- --- ---
ORA_SECURECONFIG     BY USER         ALL USERS            YES YES
ORA_LOGON_FAILURES   BY USER         ALL USERS            NO  YES


Note: SUCCESS or FAILURE columns value tell WHENEVER SUCCESSFUL or WHENEVER NOT SUCCESSFUL clause is used during setting AUDITING.
like “AUDIT POLICY TESTPOLICY1 BY HR WHENEVER NOT SUCCESSFUL;”

create AUDIT Policy for user TEST1

SQL> create audit policy test1_pol
  actions all
  when q'~ sys_context('userenv', 'session_user') = 'TEST1' ~'
  evaluate per session;

Audit policy created.

-- enable audit policy

SQL> audit policy test1_pol;

Audit succeeded.

-- connect with other TEST1 user and perform some action

SQL> connect test1/test1;

SQL> INSERT INTO simulate_deadlock VALUES (1,'Manish','Sureka');

1 row created.

SQL> commit;

-- connect with SEC_ADMIN user to view AUDIT data

SQL> conn sec_admin/oracle123

-- Check the report for the enabled polices.
--Check today audit records

set lines 200
col SQL_TEXT for a30
col action_name for a20
col UNIFIED_AUDIT_POLICIES for a30
select action_name,SQL_TEXT,UNIFIED_AUDIT_POLICIES ,EVENT_TIMESTAMP from unified_AUDIT_trail
where EVENT_TIMESTAMP > sysdate -1;

empty AUDIT TRAIL

SQL> exec dbms_audit_mgmt.clean_audit_trail(dbms_audit_mgmt.audit_trail_unified,false);

PL/SQL procedure successfully completed.

SQL>