Friday, July 28, 2017

In 12c- unified auditing not capturing l succesful login & failures

In oracle 12.1, oracle has come up with unified audit trail.  Ideally for this, audit_trail parameter should be set to none. However we observed that even though, ORA_LOGON_FAILURES   was  available in AUDIT_UNIFIED_ENABLED_POLICIES , but unified audit trail was not capturing successful  logins and failures.


 select /*+ parallel(a,3) */ a.os_username,a.dbusername,a.userhost,a.target_user,a.action_name,
to_char(a.EVENT_TIMESTAMP,'mm/dd/yyyy hh24:mi:ss') EVENT_TIMESTAMP,
a.RETURN_CODE from UNIFIED_AUDIT_TRAIL a where a.unified_audit_policies = 'ORA_LOGON_FAILURES'  and a.RETURN_CODE > 0 order by a.EVENT_TIMESTAMP desc ;
--no rows selected

This came out to be a bug in oracle 12.1 and patch "19383839" was to be applied for this. This bug was fixed in 12.2