2

I have some users who are logging in with a wrong password and their accounts get locked.

Now I need to find the source of the failed login attempt. The machine, username etc.

Is it possible to find this?

I did try

select ntimestamp#, userid, userhost, spare1, comment$text from sys.aud$ where returncode=1017 order by 1;

and also,

select OS_USERNAME,USERNAME,USERHOST,to_char(timestamp,'MM-DD-YYYY HH24:MI:SS'), returncode
from dba_audit_trail 
where returncode > 0

Both the above queries show up results but I'm not sure if the results are the ones which actually gets locked.

Server - RHEL DB - Oracle 12c

  • Perhaps you might get lucky looking in listener.log or enable db audit for access. – Raj Dec 18 '15 at 13:59
  • listener would show high volume of hits. – tesla747 Dec 19 '15 at 7:14
2

You are on the right way. Column RETURNCODE in DBA_AUDIT_TRAIL or RETURN_CODE in UNIFIED_AUDIT_TRAIL view.

http://docs.oracle.com/database/121/REFRN/GUID-A9993FAC-12D3-4725-A37D-938CC32D74CC.htm#REFRN23023

This view is populated only in an Oracle Database where unified auditing is not enabled. When unified auditing is enabled in Oracle Database, the audit records are populated in the new audit trail and can be viewed from UNIFIED_AUDIT_TRAIL.

Oracle documentation is always a very good source of information.

|improve this answer|||||

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.