we can find this 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);
These all queries fetch the data from SYS.AUD$ tables which are enabled by setting audit_trail=DB in init.ora
–For Enable
ALTER SYSTEM SET audit_trail=db SCOPE=SPFILE;
–For Disable
ALTER SYSTEM SET audit_trail=NONE SCOPE=SPFILE;
Leave a Reply