Finding the origin of failed login attempts

Finding the origin of failed login attempts


The Oracle auditing utility has this command:

audit create session whenever not successful;

To enable auditing of failed sign-on attempts:

1 - Add initialization parameters & bounce instance:

audit_trail=true

 audit_file_dest='/u01/app/oracle/mysid/mydir/'

2 - Enable auditing of failed logion attempts 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')
from
   dba_audit_trail;

 OS_USERNAME     USERNAME        TERMINAL        TO_CHAR(TIMESTAMP,'
 --------------- --------------- --------------- -------------------
fred         SCOTT              app93           05-16-2009 16:21:13


======================

col ntimestamp# for a30 heading "Timestamp"
col userid for a20 heading "Username"
col userhost for a15 heading "Machine"
col spare1 for a15 heading "OS User"
col comment$text for a80 heading "Details" wrap

select ntimestamp#, userid, userhost, spare1, comment$text from sys.aud$ where returncode=1017 order by 1;