Tracking failed logon attempts

-- ################################################
-- # Creator: Cyrille MODIANO
-- # Created: 2011/11/16
-- # Name: Tracking failed logon attempts
-- ################################################
-- #
-- # Compatible: Oracle 9i 10g 11g
-- #
-- ################################################
-- #
-- # This script enable auditing for failed login
-- # attempts
-- #
-- ################################################

-- # 1 - Modifiy initialization parameters

alter system set audit_trail=db scope=spfile;

-- # 2 - Restart the database 

shutdown immediate
startup

-- # 3 - Enable auditing for failed login attempts

audit create session whenever not successful;

-- # 4 - Query the dba_audit_trail view to see failed login attempts with error like ORA-"returncode"

set lines 130
col OS_USERNAME for a20
col USERNAME for a20
col USERHOST for a20

select OS_USERNAME,USERNAME,USERHOST,to_char(timestamp,'MM-DD-YYYY HH24:MI:SS'), returncode
from dba_audit_trail 
where returncode > 0

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.