Finding the origin of failed login attempts

This guide is intended to aid in establishing the origin of failed database login attempts. Most of the time these failed attempts are due to typos or outdated application configurations and are therefore not malicious, however due to default password management policies setting a limit on failed login attempts this often causes accounts to become locked out for legitimate users.

Note: An ACCOUNT_STATUS of "LOCKED" (in DBA_USERS) means the account was locked manually by the DBA, whereas "LOCKED(TIMED)" indicates the account was locked due to the number of failed login attempts being exceeded (as defined by FAILED_LOGIN_ATTEMPTS in the profile in effect for the user).

The following options are available for collecting information on the origin of failed connection attempts (in order of simplicity):

1. Using database auditing (if already enabled)

Caveat: This is the simplest method to determine the source of failed login attempts providing that auditing is already enabled on your database as the information has (probably) already been captured. However, if auditing is not enabled then doing so will require that the database be restarted, in which case this option is no longer the simplest!

Firstly, check to see whether auditing is enabled and set to "DB" (meaning the audit trail is written to a database table).

show parameter audit_trail

If not set, then you will need to enable auditing, restart the database and then enable auditing of unsucessful logins as follows:

audit session whenever not successful;

The audit records for unsuccessful logon attempts can then be found as follows:

col ntimestamp# for a30 heading "Timestamp"
col userid for a20 heading "Username"
col userhost for a15 heading "Machine"
col spare1 for a15 heading "OS User"
col comment$text for a80 heading "Details" wrap

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

Sample output:

Timestamp Username Machine OS User
------------------------------ -------------------- --------------- ---------------
Details
--------------------------------------------------------------------------------
08-DEC-14 12.39.42.945635 PM APPUSER unix_app_001 orafrms
Authenticated by: DATABASE; Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=10.218.
64.44)(PORT=42293))

08-DEC-14 12.42.10.170957 PM APPUSER unix_app_001 orafrms
Authenticated by: DATABASE; Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=10.218.
64.44)(PORT=48541))

Note: the USERHOST column is only populated with the Client Host machine name as of 10G, in earlier versions this was the Numeric instance ID for the Oracle instance from which the user is accessing the database in a RAC environment.

2. Use a trigger to capture additional information

The following trigger code can be used to gather additional information about unsuccessful login attempts and write them to the database alert log, it is recommended to integrate this code into an existing trigger if you already have a trigger for this triggering event.

CREATE OR REPLACE TRIGGER logon_denied_write_alertlog AFTER SERVERERROR ON DATABASE
DECLARE
 l_message varchar2(2000);
BEGIN
 -- ORA-1017: invalid username/password; logon denied
 IF (IS_SERVERERROR(1017)) THEN
 select 'Failed login attempt to the "'|| sys_context('USERENV' ,'AUTHENTICATED_IDENTITY') ||'" schema'
 || ' using ' || sys_context ('USERENV', 'AUTHENTICATION_TYPE') ||' authentication'
 || ' at ' || to_char(logon_time,'dd-MON-yy hh24:mi:ss' )
 || ' from ' || osuser ||'@'||machine ||' ['||nvl(sys_context ('USERENV', 'IP_ADDRESS'),'Unknown IP')||']'
 || ' via the "' ||program||'" program.'
 into l_message
 from sys .v_$session
 where sid = to_number(substr(dbms_session.unique_session_id,1 ,4), 'xxxx')
 and serial# = to_number(substr(dbms_session.unique_session_id,5 ,4), 'xxxx');
 
 -- write to alert log
 sys.dbms_system .ksdwrt( 2,l_message );
 END IF;
END;
/

Some sample output from the alert.log looks like:

Tue Jan 06 09:45:36 2015
Failed login attempt to the "appuser" schema using DATABASE authentication at 06-JAN-15 09:45:35 from orafrms@unix_app_001 [10.218.64.44] via the "frmweb@unix_app_001 (TNS V1-V3)" program.

3. Setting an event to generate trace files on unsuccessful login.

You can instruct the database to write a trace file whenever an unsuccessful login attempt is made by setting the following event (the example below will only set the event until the next time the database is restarted. Update your pfile or spfile accordingly if you want this to be permanent).

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

Trace files will be generated in user_dump_dest whenever someone attempts to login using an invalid username / password. As the trace is requested at level 10 it will include a section labeled PROCESS STATE that includes trace information such as :

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

In this case it was an 'frmweb' client running as OS user 'orafrms' that started the client session. The section "Call Stack Trace" may aid support in further diagnosing the issue.

Note: If the OS user or program is 'oracle' the connection may originate from a Database Link.

4. Using SQL*Net tracing to gather information

A sqlnet trace can provide you with even more details about the connection attempt but use this only if none of the above are successful in determining the origin of the failed login as it will be hard to find what you are looking for if you enable sqlnet tracing (and it can potentially consume large amounts of disk space).

To enable SQL*Net tracing create or edit the server side sqlnet.ora file and add the following parameters:

# server side sqlnet trace parameters
trace_level_server = 16
trace_file_server=server
trace_directory_server =