Oracle Database

ORACLE-WIKI.NET

Background & Overview

The following step by step guide explains how to audit failed logon attempts.

Step-By-Step Guide

1. Add initialization parameters & bounce instance:

• alter system set audit_trail=true scope=spfile sid='*';
• alter system set audit_file_dest='/u01/app/oracle/mysid/mydir/' scope=spfile sid='*';

2. Enable auditing of failed logon attempts

• sqlplus / as sysdba
• SQL> audit create session whenever not successful;

3. You can now view failed login attempts in dba_audit_trail

SELECT
os_username,
username,
terminal,
to_char(timestamp,'MM-DD-YYYY HH24:MI:SS') as timestamp
FROM
DBA_AUDIT_TRAIL;

Example Output

OS_USERNAME     USERNAME        TERMINAL        TIMESTAMP
--------------- --------------- --------------- -------------------
FRED            SCOTT           MYAPP           05-16-2009 16:21:13