Finding the source of failed login attempts. (Doc ID 352389.1)

Finding which Application server has wrong password.   

1. Check if audit is enable, if not turn on. -- AUDIT SESSION WHENEVER NOT SUCCESSFUL;

2. Unlock user 

3. Run the below SQL 

select username,

os_username,

userhost,

client_id,

trunc(timestamp),

count(*) failed_logins

from dba_audit_trail

where returncode=1017 and --1017 is invalid username/password

timestamp > sysdate -7

group by username,os_username,userhost, client_id,trunc(timestamp); 

4. Inform Application user 

5. Turn off the auditing if not needed --   NOAUDIT SESSION WHENEVER NOT SUCCESSFUL;

Oracle Note:- 

TROUBLESHOOTING STEPS

Check DBA_USERS

If the ACCOUNT_STATUS for the user is LOCKED, this means the account was locked by a DBA doing ALTER USER &username ACCOUNT LOCK; if the ACCOUNT_STATUS is LOCKED(TIMED) this means it was locked because of exceeding the number of allowed FAILED_LOGIN_ATTEMPTS, this is true even if the PASSWORD_LOCK_TIME is set to unlimited. An account will never get locked due to password expiration. Before the account is actually locked you can inspect the number of failed login attempts so far by checking the USER$.LCOUNT column:

select name,lcount from user$ where name='&USERNAME';

Audit unsuccessful logins

At first establish standard auditing by setting audit_trail = db and issue:

AUDIT SESSION WHENEVER NOT SUCCESSFUL;

The audit records for unsuccessful logon attempts from 'last week' can be found as follows:

select username,

os_username,

userhost,

client_id,

trunc(timestamp),

count(*) failed_logins

from dba_audit_trail

where returncode=1017 and --1017 is invalid username/password

timestamp > sysdate -7

group by username,os_username,userhost, client_id,trunc(timestamp); 

Set an event in the "init.ora" parameter file

Set the following event in your parameter file to dump a trace file whenever an ORA-1017 is generated:

event = "1017 trace name errorstack level 10"

Alternatively you can issue the following command as a privileged user, this will only affect new processes so may not work in a Shared Server environment:

alter system set events '1017 trace name errorstack level 10';

This will produce a trace file in user_dump_dest whenever someone attempts an invalid username / password , since the trace is requested at level 10, it will include a section labeled PROCESS STATE that includes trace information like:

O/S info: user: userx, term: pts/1, ospid: ***** , machine: *****
program: sqlplus@***** (TNS V1-V3)
application name: sqlplus@**** (TNS V1-V3), hash value=0
last wait for 'SQL*Net message from client' blocking sess=0x0 seq=2 wait_tim
e=5570 seconds since wait started=0

In this case it was an sqlplus client started by OS user 'userx' that started the client session. The section Call Stack Trace may aid support in further diagnosing the issue. Tip: If the OS user or program is 'oracle' the connection may originate from a Database Link.

Use a trigger to capture additional information

The following trigger code can be used to gather additional information about unsuccessful login attempts, it is recommended to integrate this code into an existing trigger if you already have a trigger for this triggering event instead of having more triggers on the same event, note this is just an example providing some essential information, it can be changed or modified at will, for example you may prefer to log the entries in a table instead of the alert.log file, it appears this trigger fires even if the session is not authenticated, please keep the trigger code as simply as possible as to minimize the performance impact.

Some sample output from the alert.log looks like:

Fri May 20 10:00:50 2011 Fri May 20 10:00:50 2011 logon denied IP = localhost pid = **** os user = oracle client id = with program= sqlplus@<host name> (TNS V1-V3) module=sqlplus@<host name> (TNS V1-V3) action= 

More attributes may be found in note 120797.1  , however please consider when this trigger fires on an unsuccessful logon attempt, not all session specific information may be available as it would be for an authenticated session.

Use SQLNET Tracing to gather detailed information

An sqlnet trace can provide you with even more details about the connection attempt, use this only if the above does not provide you with enough information, since it will be hard to find what you are looking for if you enable sqlnet tracing, to enable it create or edit the server side sqlnet.ora file and put in the following parameters:

# server side sqlnet trace parameters

trace_level_server = 16

trace_file_server=server

trace_directory_server = <any directory on a volume with enough freespace>

As a last resort you may want to set your hopes on a packet sniffer on the network or operating system level, the use of such tools however is beyond the scope of this article, you may want to consult your local network administrator about this possibility.

 

Note: If you are seeing your RDBMS account getting locked out(with an ORA-28000 "The account is locked." error) even though user never provided the wrong password
FAILED_LOGIN_ATTEMPTS times consecutively, and there were no ORA-1017 errors in the audit trail for user account, then a known bug can be cause for this and will have to apply patch to fix the issue Bug 30210753.