Script for AUDIT and NOAUDIT statements for Unified Auditing in Oracle

This script help in disable and enabled the current policy already configured in the unified auditing. Sometime we face problem of space and performance due to extra enabled policies in Audit trail. With help of script we can make changes with current settings.

Check Unified auditing is enabled or Disabled.

select parameter,value from v$option where parameter = 'Unified Auditing';


PARAMETER VALUE
-------------------- ----------
Unified Auditing TRUE


Check the Policy and left the default policy which already from Oracle

select unique policy_name from AUDIT_UNIFIED_POLICIES
where policy_name not in (
'ORA_ACCOUNT_MGMT','ORA_DATABASE_PARAMETER',
'ORA_SECURECONFIG','ORA_DV_AUDPOL',
'ORA_DV_AUDPOL2','ORA_RAS_POLICY_MGMT',
'ORA_RAS_SESSION_MGMT','ORA_LOGON_FAILURES',
'ORA_STIG_RECOMMENDATIONS','ORA_LOGON_LOGOFF',
'ORA_ALL_TOPLEVEL_ACTIONS','ORA_CIS_RECOMMENDATIONS')
order by policy_name;


Get more detail about policies

select POLICY_NAME,AUDIT_OPTION,AUDIT_OPTION_TYPE,OBJECT_SCHEMA,OBJECT_NAME,OBJECT_TYPE
from audit_unified_policies
where policy_name in ('ORA_SECURECONFIG','ORA_LOGON_FAILURES')
order by 1,2,3,4,5;


Enable and disable policy statements

---For Disable Unified policy
select unique 'NOAUDIT POLICY '||POLICY_NAME||';' from audit_unified_enabled_policies;

— For Enable unified policy
select unique ‘AUDIT POLICY ‘||POLICY_NAME||’;’ from audit_unified_enabled_policies;

Check more details of enabled current policy of Unified audit in Oracle

--For version 19c
select entity_name, policy_name, enabled_option, success||failure,
'AUDIT POLICY '||POLICY_NAME||decode(ENTITY_NAME,'ALL USERS',' ',' ',decode(ENABLED_OPTION,'BY GRANTED ROLE','BY USERS WITH GRANTED ROLES','BY USER','BY','EXCEPT USER',
'EXCEPT',ENABLED_OPTION)||' "'||ENTITY_NAME||'"')|| decode(SUCCESS||FAILURE, 'YESYES', ' ', 'YESNO' , ' WHENEVER SUCCESSFUL', 'NOYES' , ' WHENEVER NOT SUCCESSFUL', 'NONO'
, ' /* metadata issue */ ')||' ;' statement from audit_unified_enabled_policies order by policy_name, enabled_option, success||failure, entity_name;

— Checked Enabled policy for 12.2 or 18
select user_name, policy_name, enabled_opt, entity_name, success||failure,
‘AUDIT POLICY ‘||POLICY_NAME||
decode(USER_NAME,’ALL USERS’,’ ‘, decode(ENABLED_OPT,’INVALID’,’BY USERS WITH GRANTED ROLES’,ENABLED_OPT),’ “‘||nvl(USER_NAME,ENTITY_NAME)||'”‘)
||decode(SUCCESS||FAILURE, ‘YESYES’, ‘ ‘, ‘YESNO’ , ‘ WHENEVER SUCCESSFUL’, ‘NOYES’ ,’ WHENEVER NOT SUCCESSFUL’, ‘NONO’ , ‘ /* metadata issue */ ‘)||’ ;’ “statement”
from audit_unified_enabled_policies order by policy_name, enabled_opt, success||failure, user_name;

–For 12.1 version
select user_name, policy_name, enabled_opt, success||failure, ‘AUDIT POLICY ‘||POLICY_NAME||”||
decode(USER_NAME,’ALL USERS’,’ ‘,’ ‘||ENABLED_OPT||’ “‘||USER_NAME||'”‘)||
decode(SUCCESS||FAILURE, ‘YESYES’, ‘ ‘, ‘YESNO’ , ‘ WHENEVER SUCCESSFUL’,’NOYES’ , ‘ WHENEVER NOT SUCCESSFUL’, ‘NONO’ , ‘ /* metadata issue */ ‘)||’ ;’ “statement”
from audit_unified_enabled_policies order by policy_name, enabled_opt, success||failure, user_name;

— View Audit context
select ‘AUDIT CONTEXT NAMESPACE ‘||namespace||’ ATTRIBUTES ‘||attribute||
decode(USER_NAME,’ALL USERS’,’ ‘,’ BY “‘||user_name||'”‘)||’;’
from AUDIT_UNIFIED_CONTEXTS order by namespace,user_name, attribute;

Generate No Audit policy for Current environment details

-- No Audit Statement for 12.2 or 18 version
select --user_name, policy_name, enabled_opt, entity_name, success||failure,
'NOAUDIT POLICY '||POLICY_NAME||decode(USER_NAME,'ALL USERS',' ',' '||decode(ENABLED_OPT,'INVALID','BY USERS WITH GRANTED ROLES',ENABLED_OPT)||' "'||
nvl(USER_NAME,ENTITY_NAME)||'"')||
decode(SUCCESS||FAILURE, 'YESYES', ' ', 'YESNO' , ' WHENEVER SUCCESSFUL', 'NOYES' ,' WHENEVER NOT SUCCESSFUL', 'NONO' , ' /* metadata issue */ ')||' ;' statement
from audit_unified_enabled_policies where enabled_opt 'EXCEPT' order by policy_name, enabled_opt, success||failure, user_name;

— For 19c
select –entity_name, policy_name, enabled_option, success||failure,
‘NOAUDIT POLICY ‘||POLICY_NAME||decode(ENTITY_NAME,’ALL USERS’,’ ‘,’ ‘||
decode(ENABLED_OPTION,’BY GRANTED ROLE’,’BY USERS WITH GRANTED ROLES’,’BY USER’,’BY’,’EXCEPT USER’,’EXCEPT’,ENABLED_OPTION)||’ “‘||ENTITY_NAME||'”‘)||
decode(SUCCESS||FAILURE, ‘YESYES’, ‘ ‘, ‘YESNO’ , ‘ WHENEVER SUCCESSFUL’, ‘NOYES’ , ‘ WHENEVER NOT SUCCESSFUL’, ‘NONO’ , ‘ /* metadata issue */ ‘)||’ ;’ statement
from audit_unified_enabled_policies where enabled_option ‘EXCEPT USER’ order by policy_name, enabled_option, success||failure, entity_name;

–For 12.1
select — user_name, policy_name, enabled_opt, success||failure,
‘NOAUDIT POLICY ‘||POLICY_NAME||decode(USER_NAME,’ALL USERS’,’ ‘,’ ‘||ENABLED_OPT||’ “‘||USER_NAME||'”‘)||
decode(SUCCESS||FAILURE, ‘YESYES’, ‘ ‘, ‘YESNO’ , ‘ WHENEVER SUCCESSFUL’,
‘NOYES’ , ‘ WHENEVER NOT SUCCESSFUL’, ‘NONO’ , ‘ /* metadata issue */ ‘)||’ ;’ statement
from audit_unified_enabled_policies where enabled_opt ‘EXCEPT’ order by policy_name, enabled_opt, success||failure, user_name;

–Noaudit CONTEXT
select ‘NOAUDIT CONTEXT NAMESPACE ‘||namespace||’ ATTRIBUTES ‘||attribute||decode(USER_NAME,’ALL USERS’,’ ‘,’ BY “‘||user_name||'”‘)||’;’
from AUDIT_UNIFIED_CONTEXTS order by namespace,user_name, attribute;