Audit Unsuccessful Oracle Login Attempts

Recently, the corporate parent of a client began dictating regular password changes for various Oracle service accounts, ie. accounts that the developers with the client as well as myself had been using to run automated tasks, automated reports, etc. After the password was changed, most of those jobs were documented and thus easy to find and update, but almost immediately we noticed that one of the service accounts began locking up every few minutes, so we obviously missed at least one, and it was too early in the morning for us to have consumed enough cups of coffee to remember what we may have missed.

The built-in DBA_AUDIT_TRAIL view came to our rescue.

The following command enables the logging of failed login attempts.

audit session whenever not successful;

Once that was enabled, we simply waited a few minutes, and then ran this SQL below.

select os_username, username, terminal, returncode, to_char(timestamp,'mm/dd/yyyy hh24:mi:ss') as fail_time, comment_text
from dba_audit_trail
where username='ACCOUNT_USERNAME' 
and returncode in(1017,28000)
order by timestamp desc;

VoilĂ ! We found our culprit. The field “terminal” showed us the machine’s hostname, and its IP address was found in the “comment_text” field. Return code 1017 signifies a login failure due to bad password, and 28000 is a login failure due to locked account.