Oracle Audit Failed Logon Attempts
Do you need to audit failed login attempts to your database, or have accounts becoming locked due to failed login attempts and not sure why? Here's a solution:
You must set the audit_trail=DB
in the init.ora/spfile
then you must audit session whenever not successful ;
Here is a complete walkthrough on the setup and a script that will help query the audit trail for the failed login attempts. Be sure to clean up you audit trail so it does not grow out of control (truncate aud$;
):
SQL> connect sys as sysdba Enter password: ****** Connected. SQL> alter system set audit_trail=DB scope=spfile ; System altered. SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup ; ORACLE instance started. Total System Global Area 612368384 bytes Fixed Size 1250428 bytes Variable Size 234883972 bytes Database Buffers 369098752 bytes Redo Buffers 7135232 bytes Database mounted. Database opened. SQL> audit session whenever not successful ; Audit succeeded. SQL> connect dummy/dummy ERROR: ORA-01017: invalid username/password; logon denied Warning: You are no longer connected to ORACLE. SQL> connect sys as sysdba Enter password: ****** Connected. SQL> select os_username, username, userhost, to_char(timestamp,'mm/dd/yyyy hh24:mi:ss') timestamp, returncode from dba_audit_session where action_name = 'LOGON' and returncode > 0 order by timestamp ; OS_USERNAME USERNAME USERHOST TIMESTAMP RETURNCODE ROQETM4N\Roq Mann DUMMYWORKGROUP\ROQETM4N 11/08/2007 09:07:54 1017 SQL>