Mir Sayeed Hassan – Oracle Blog

How to check the Failed Login Attempts in Oracle Database.

Assume you or someone else has logged to the database with failed username & password.

SQL> connect systest/alksflkagf
ERROR:
ORA-01017: invalid username/password; logon denied

Lets try to find out the failed login attempt with time based

Query to find the time based event for sameday(today)

select to_char(TIMESTAMP, 'YYYY-mm-dd HH12:MI:SS') FROM dba_audit_trail WHERE returncode=ORA-1017 AND TIMESTAMP > sysdate-1;

Query to find the time based event for last few hours ex: (5 hours)

select to_char(TIMESTAMP, 'YYYY-mm-dd HH12:MI:SS') FROM dba_audit_trail WHERE returncode=ORA-1017 AND TIMESTAMP > SYSDATE-(5/24);

Query to find the time based event for last 1 hour

select to_char(TIMESTAMP, 'YYYY-mm-dd HH12:MI:SS') FROM dba_audit_trail WHERE returncode=ORA-1017 AND TIMESTAMP > SYSDATE-(1/24);

Query to find the time based event for last 30 minutes

select to_char(TIMESTAMP, 'YYYY-mm-dd HH12:MI:SS') FROM dba_audit_trail WHERE returncode=ORA-1017 AND TIMESTAMP > SYSDATE-(30/1440);

Create a script in database & run it whenever it require

[oracle@ractest1(rac11) ~]$ vi /home/oracle/scripts/users_failedloginattempts.sql

set linesize 240
set pagesize 9999
COL username FORMAT a15 HEAD 'Oracle_User'
COL os_username FORMAT a15 HEAD 'OS_User'
COL userhost FORMAT a20 HEAD 'Host'
COL client_id FORMAT a20 HEAD 'Client_ID'
COL timestamp FORMAT a20 HEAD 'Timestamp'

ACCEPT v_Days PROMPT 'Days [1]: ' DEFAULT 1
PROMPT

alter session set NLS_TIMESTAMP_FORMAT ='YYYY-MM-DD HH:MI:SS.FF';

SELECT username, os_username, userhost, client_id, trunc(timestamp) "Timestamp", count(*) "Failed_Logins" FROM dba_audit_trail
WHERE returncode=ORA-1017 AND --ORA-1017 is invalid username/password
TIMESTAMP > sysdate-&v_Days
GROUP BY username, os_username, userhost, client_id, trunc(timestamp);

:wq

Execute the script:

SQL> @/home/oracle/scripts/users_failedloginattempts.sql;
Days [1]: 1
Session altered.

old 3: TIMESTAMP > sysdate-&v_Days
new 3: TIMESTAMP > sysdate-1

Oracle_User OS_User Host Client_ID Timestamp Failed_Logins
--------------- --------------- -------------------- -------------------- -------------------- -------------
SYSTEST oracle ractest1.local 06-DEC-22 2

=====Hence tested & verified in our test env=====