Trace IP address / machine of user logging in database with wrong password Posted by MOHAMMED ABDUL HAMEED on 3:19 AM

Enable audit in database with parameter AUDIT_TRAIL

SQL> show parameter audit_trail;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_trail                          string      DB_EXTENDED

 Enable session auditing

SQL> audit session;

Audit succeeded.

 Check DBA_AUDIT_TRAIL view:

SQL> select os_username, userhost, username, action_name, timestamp, returncode
  2  from dba_audit_trail
  3  where returncode=1017;

OS_USERNAME
--------------------------------------------------------------------------------
USERHOST
--------------------------------------------------------------------------------
USERNAME                       ACTION_NAME                  TIMESTAMP RETURNCODE
------------------------------ ---------------------------- --------- ----------
pierre
WORKGROUP\PC-de-pierre
HR                             LOGON                        22-FEB-11       1017

1017 stands for ORA-1017 Oracle error:

oerr ora 1017
01017, 00000, "invalid username/password; logon denied"
// *Cause:
// *Action:

OS_USERNAME is OS account name of user that is trying to connect to Oracle
USERHOST is the machine name where executable has tried to connect.