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>
