Failed Login Attempts – DBA_AUDIT_SESSION

How to list logon/logoff sessions in the Oracle (Clinical) database including failed logins?

Example for Oracle 11g database (OC 4.6.x)

Logon to SQL*Plus or TOAD as DBA user in the Oracle database with access to DBA system views and tables.

SQL query DBA Audit Session

/* Logon/Logoffs using referencing lookup function for returning error message for return (error) code */

select 
 username,
 terminal, 
 action_name,
 to_char(timestamp,'YYYYMMDD HH24:MI:SS') timestamp,
 to_char(logoff_time,'YYYYMMDD HH24:MI:SS') logoff_time,
 returncode,
 get_ora_error_message(-returncode) error_msg
from dba_audit_session
where 1=1 -- timestamp > sysdate-7
 AND username like '%' -- = 'OPS$<init>' -- OC users are prefixed with OPS$
 and returncode <> 0 -- <> 0 to list failed logins
 --and logoff_time is not null
order by 5 desc;

Function used to return error message from error code (return code)

create or replace function get_ora_error_message(p_ora_number in number) return varchar2 is 
v_msg varchar2(255); 
begin 
 v_msg := sqlerrm(p_ora_number); 
 return v_msg; 
end; 
/

Return codes and messages (commonly seen)
ORA-01017: invalid username/password; logon denied
ORA-01045: user lacks CREATE SESSION privilege; logon denied
ORA-03136: inbound connection timed out
ORA-28000: the account is locked
ORA-28001: the password has expired
ORA-28003: password verification for the specified password failed
ORA-28007: the password cannot be reused
ORA-28043: invalid bind credentials for DB-OID connection