• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
Techgoeasy

Techgoeasy

Learn Oracle, PHP, HTML,CSS,Perl,UNIX shell scripts

  • Home
  • Oracle
    • Oracle database
    • Oracle Ebusiness Suite
    • Oracle weblogic
    • Oracle Performance Tuning
    • Oracle Hyperion
    • Oracle Cloud Tutorials
  • SQL
  • interview questions
  • Linux
  • PHP and HTML
  • Downloads
  • General
Home » Oracle » Oracle Database » How to find the invalid login attempt in the database

How to find the invalid login attempt in the database

October 31, 2021 by techgoeasy Leave a Comment

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;

Filed Under: Oracle, Oracle Database

Reader Interactions

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Primary Sidebar



Subscribe to our mailing list

Enter your email address to subscribe to this blog and receive notifications of new posts by email

Recent Posts

  • How to find weblogic version in Unix & Windows
  • How to use RMAN Catalog Command
  • Oracle drm interview questions
  • How to use RMAN Crosscheck command
  • Oracle Data Relationship Management: Definition, Password , Login

Copyright © 2022 : TechGoEasy

  • Hire me
  • Privacy Policy
  • Contact Us
  • New? Start Here
  • About Us