3

I activated a ORA_LOGON_FAILURES policy and I've tried to execute

select * from unified_audit_trail where unified_audit_policies = 'ORA_LOGON_FAILURES';

But it doesn't work. All my failed attempts to log in weren't audited. So what do I do wrong?

  • which oracle version you are using? – Md Haidar Ali Khan Mar 5 '17 at 13:48
  • @Md Haidar Ali Khan 12.1.0.2.0 – Denis Lolik Mar 5 '17 at 13:50
4

You have enabled ORA_LOGON_FAILURES, but have you enabled Unified Auditing properly?

With the default, mixed-mode auditing, setting audit_trail to none, prevents logon failures to be audited with ORA_LOGON_FAILURES enabled.

This is how it works with everything set to default (audit_trail set to DB by DBCA):

SQL> audit policy ora_logon_failures;

Audit succeeded.

SQL> select count(*) from unified_audit_trail 
     where unified_audit_policies = 'ORA_LOGON_FAILURES' and return_code = 1017;

  COUNT(*)
----------
         0

SQL> connect 1/1
ERROR:
ORA-01017: invalid username/password; logon denied


Warning: You are no longer connected to ORACLE.
SQL> connect / as sysdba
Connected.
SQL> select count(*) from unified_audit_trail
     where unified_audit_policies = 'ORA_LOGON_FAILURES' and return_code = 1017;

  COUNT(*)
----------
         1

Also, Unified Audit entires can be buffered in the SGA and be written to disk later and not immediately, in that case, flush manually before querying:

EXEC DBMS_AUDIT_MGMT.flush_unified_audit_trail;

Still, if you configured everything properly, you may have hit:

BUG 19383839 - UNIFIED AUDIT - NO LOGON OR FAILED LOGON ACTION CAPTURED

Edit:

Ok, so I had to test it myself, else I would not have believed it. Opposing to what the documentation states (AUDIT_TRAIL), setting the audit_trail to none does have an effect even when using pure Unified Auditing. But this is not intended, it is a bug. The fix is not included in the latest PSU (12.1.0.2.170117), but installing the above one-off patch (19383839) indeed resolved the issue. The problem is, this patch is not available to Windows platform. (I have tested this both on Linux and Windows, because Windows is always a factor you need to consider when working with Oracle.)

Setting audit_trail to DB does not reenable mixed-mode, relinking (renaming the DLL) enables pure Unified Auditing and that overrides this, you can confirm this by selecting from AUD$ or DBA_AUDIT_SESSION. So I suggest that you set audit_trail to DB.

  • You have enabled ORA_LOGON_FAILURES . +1 – Md Haidar Ali Khan Mar 5 '17 at 13:57
  • If I got you right, I have to enable mix-mode auditing with AUDIT_TRAIL='db,extended'. Right? – Denis Lolik Mar 5 '17 at 13:57
  • @Denis Lolik Not necessarily, audit_trail=db works as well. – Balazs Papp Mar 5 '17 at 14:09
  • @Balazs Papp I mean what if I do not activate audit_trail? Mix-mode decreases performance. – Denis Lolik Mar 5 '17 at 14:14
  • @Denis Lolik Mixed-mode is the default. You can enable pure Unified Auditing by relinking the binaries with the Unified Auditing option. In a pure Unified Auditing environment, audit_trail has no effect. Still, you have not told us what you did besides activating the ORA_LOGON_FAILURES policy, so I do not know what environment you use. – Balazs Papp Mar 5 '17 at 14:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.