Who is locking your accounts (ORA-01017 and ORA-28000 errors) ?
I have decided to write this blog post after second time I received question on how to know from where are coming connections that are locking an account in an Oracle database…
Do not smile, I have seen at least two situations where, after a password change, a batch job was initiating plenty of connection (with previous wrong password) and no one was able to know from where this batch job was running (!!).
As a reminder, with default profile in Oracle 11g, accounts are automatically locked 1 day (PASSWORD_LOCK_TIME) after 10 failed login attempt (FAILED_LOGIN_ATTEMPTS):
Oracle client session will received 10 times ORA-01017: invalid username/password; logon denied error message and then ORA-28000: the account is locked error message (for one day and then back to ORA-01017 error message).
The final question is how to identify from where (client IP address/name) are coming those tentative connections… I have done my testing using Oracle 126.96.36.199 running on Oracle Linux Server release 6.3.
ORA-01017/ORA-28000 with AUDIT_TRAIL
The first and preferred solution is with Oracle standard auditing feature. Start by setting initialization parameter AUDIT_TRAIL to db and restart your Oracle database as it is static parameter.
So you must put in place a purging policy for this table.
ORA-01017/ORA-28000 without AUDIT_TRAIL
The only drawback of the previous solution is that you have to restart the database. And maybe two times because after problem solved you would like to deactivate auditing. This is most probably not reliable solution on a production database so I have been looking for a better solution with no database reboot.
I initially thought of the AFTER LOGON trigger but you need to be logged-in and the BEFORE LOGON does not exits. Then at same documentation place I found the AFTER SERVERERROR trigger and decided to give it a try.
First I created a dummy table to log server error (columns inherited from dba_audit_session dictionary table):