How to check failed login attempts in Oracle

We sometime might see accounts getting locked in the Oracle Database. This may be because of failed login attempt due to the wrong password. So we often look at How to check failed login attempts in Oracle. Let’s discuss those below

How to check failed login attempts in Oracle

we can find the failed login attempt in the database using the below query. You need to provide the username if you are looking for a particular user or just leave blank, it will show for all the users

SELECT
  TO_CHAR(TIMESTAMP,'MM/DD HH24:MI') TIMESTAMP,
  SUBSTR(OS_USERNAME,1,20) OS_USERNAME,
  SUBSTR(USERNAME,1,20) USERNAME,
  SUBSTR(TERMINAL,1,20) TERMINAL,
  ACTION_NAME,
  RETURNCODE,
   OS_USERNAME,
   USERNAME,
   USERHOST  
FROM
  SYS.DBA_AUDIT_SESSION
WHERE
  USERNAME LIKE '%&1%'
  AND TIMESTAMP BETWEEN SYSDATE-1 AND SYSDATE and RETURNCODE in (1017,20008)
ORDER BY
  TIMESTAMP DESC;

We have another view DBA_AUDIT_TRAIL, which can be also used to detect invalid login attempts. If you are interested in a particular user, give the username as input

alter session set nls_date_format = 'dd-mm-yyyy hh24:mi:ss';
select username, os_username, userhost, client_id, timestamp, from dba_audit_trail where returncode = 1017 and timestamp > sysdate - 1  and  USERNAME LIKE '%&1%'  order by timestamp;

You can below query also to summarize all the invalid attempts in the database

select username, os_username, userhost, client_id, trunc(timestamp), count(*) failed_logins from dba_audit_trail where returncode = 1017 and timestamp > sysdate - 7 group by username, os_username, userhost, client_id, trunc(timestamp) order by trunc(timestamp);

We can also find the number of attempt before the account get locked using the below query

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

These all queries fetch the data from SYS.AUD$ tables which are enabled by setting audit_trail=DB in init.ora

–To Enable

ALTER SYSTEM SET audit_trail=db SCOPE=SPFILE;
shutdown immediate
startup

–To Disable

ALTER SYSTEM SET audit_trail=NONE SCOPE=SPFILE;
shutdown immediate
startup

If don’t have audit enabled then and you don’t want to set it, then you can set the event to generate the trace whenever failed login attempt happened

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

You can also set trigger to write failed login information in alert log

create or replace trigger logon_denied_to_alert
after servererror on database
declare
message varchar2(256);
IP varchar2(15);
v_os_user varchar2(80);
v_module varchar2(50);
v_action varchar2(50);
v_pid varchar2(10);
v_sid number;
v_program varchar2(48);
v_client_id VARCHAR2(64);
begin
IF (ora_is_servererror(1017)) THEN
-- get IP for remote connections:
if sys_context('userenv','network_protocol') = 'TCP' then
IP := sys_context('userenv','ip_address');
end if;

select distinct sid into v_sid from sys.v_$mystat;
SELECT p.SPID, v.PROGRAM into v_pid, v_program
FROM V$PROCESS p, V$SESSION v
WHERE p.ADDR = v.PADDR AND v.sid = v_sid;

v_os_user := sys_context('userenv','os_user');
dbms_application_info.READ_MODULE(v_module,v_action);

v_client_id := sys_context('userenv','client_identifier');

message:= to_char(sysdate,'Dy Mon dd HH24:MI:SS YYYY')||
' logon denied '|| 'IP ='||nvl(IP,'localhost')||' pid = '||v_pid||
' os user = '||v_os_user||' client id = '||v_client_id||
' with program= '||v_program||' module ='||v_module||' action='||v_action;

sys.dbms_system.ksdwrt(2,message);
end if;
end;
/

I hope you like this content on failed login attempts in Oracle

Related Articles
How to purge AUD$ table in Oracle : check out detailed steps on How to purge AUD$ table in Oracle using oracle standard package DBMS_AUDIT_MGMT
Query to get session details in oracle : Check out How to find session details in Oracle ,how to get the sql text from sid in oracle,how to find sql query using sql_id in oracle
oracle ebs failed login attempts : check oracle ebs failed login attempts, How to enable signon auditing , Auditing reports in EBS,how to purge signon audit data,oracle ebs user login history