Script for AUDIT and NOAUDIT statements for Unified Auditing in Oracle

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;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.