Oracle Auditing for failure login attempts

So let's enable auditing by changing this init.ora parameter and bouncing the database.


SQL> alter system set audit_trail=db scope=spfile
SQL> /

System altered.

SQL> startup force
ORACLE instance started.

Total System Global Area 1436884992 bytes
Fixed Size 2148072 bytes
Variable Size 788535576 bytes
Database Buffers 637534208 bytes
Redo Buffers 8667136 bytes
Database mounted.
show parameter audit
Database opened.
SQL>


SQL> audit session whenever not successful ;

Audit succeeded.

SQL> connect blah/blah
ERROR:
ORA-01017: invalid username/password; logon denied


Warning: You are no longer connected to ORACLE.
SQL> connect /as sysdba

Connected.
SQL> SQL>

SQL> col os_username format a15
SQL> col userhost format a15
SQL> col userhost format a15
SQL> col timestamp format a25
SQL> set pages 120 lines 120
SQL> col logoff_dlock format a15
SQL> select os_username,
2 username,
3 userhost,
4 to_char(timestamp,'mm/dd/yyyy hh24:mi:ss') timestamp,
5 returncode
6 from dba_audit_session
7 where action_name = 'LOGON'
8 and returncode > 0
9 order by timestamp ;

OS_USERNAME USERNAME USERHOST TIMESTAMP RETURNCODE
--------------- ------------------------------ --------------- ------------------------- ----------
oracle BLAH fcqaodbs01 05/03/2010 11:12:31 1017


Simple isn't it!
We can do a number of different fine-grained auditing in 11g (better than 10g). Keep an eye out for more information on this blog!