How to Audit Potential Attempts to Break a Username/Password?

Subject: How to Audit Potential Attempts to Break a Username/Password
Doc ID: Note:221944.1 Type: FAQ
Last Revision Date: 12-MAR-2007 Status: PUBLISHED


Purpose:
~~~~~~~~
How to enable audit to trace a user/terminal who is trying to break in
username and password in the database? This is also a good way to Audit
unsuccessful database login attempts.

Solution:
~~~~~~~~~
Do the following steps to enable auditing:

1. Modify the "init.ora" file, usually located in the "$ORACLE_HOME/dbs"
directory to enable the AUDIT_TRAIL parameter.

AUDIT_TRAIL can be set to one of the following values:

DB or TRUE enables systemwide auditing where audited records are written to
the database audit trail, the SYS.AUD$ table
OS enables systemwide auditing where audited records are written to the
operating system's audit trail.

NONE or FALSE disables auditing.

Example:
--------
AUDIT_TRAIL = TRUE

2. Stop/Start the instance to make the parameter effective.

3. Enable the following audit option

SQL>AUDIT ALL BY ACCESS WHENEVER NOT SUCCESSFUL

4. Then query AUD$ as the following example

SQL> select returncode, action#, userid, userhost, terminal from aud$

RETURNCODE ACTION# USERID USERHOST TERMINAL
---------- ---------- -------- -------------------- --------------------
1017 100 SCOTT WPRATA-BR
1017 100 SCOTT WPRATA-BR
1017 100 SCOTT WPRATA-BR


The return code 1017 means ORA-1017 "invalid username/password; logon denied"
indicating that host WPRATA-BR tried to break in username/password.


References:
~~~~~~~~~~~
Note 1020945.6 How to Setup Auditing
Note 167293.1 Some examples about auditing and output of auditing
Note 30690.1 Init.ora Parameter "AUDIT_TRAIL" Reference Note
Note 352389.1 Finding the source of failed login attempts.