I get several calls on finding out how user accounts are locked and how can we track the host server and OS account name which issued wrong passwords. Here is one method of doing this if you have auditing enabled.
So in my case it was OS user, oracle which was trying to connect as WASADMIN with wrong password.
1) Make sure that auditing is enabled and audit_trail is OS. You could find several documents on how to do auditing using DBA option and later querying aud$ table or checking aud file created if it is audit_trail is OS.
SQL> show parameter audit
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest string /audit/ppos1
audit_sys_operations boolean TRUE
audit_syslog_level string
audit_trail string OS
2) Connect as sysdba and issue audit command
[oracle@admtrac001:/backup/arac/audit] $ sqlplus '/ as sysdba'
SQL*Plus: Release 10.2.0.4.0 - Production on Tue Mar 6 13:40:50 2012
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
SQL> audit session by WASADMIN by session whenever not successful;
Audit succeeded.
In 11g, you need to do “by access” instead of by session ( audit session by WASADMIN by access whenever not successful)
3) Look unsuccessful login attempts by looking for Error Code: 1017 in *.aud files at /audit/ppos1 directory.
$ grep 'RETURNCODE\:\[4\] \"1017\"' *.aud
ora_madhu.aud:SESSIONID:[7] "8167751" ENTRYID:[1] "1" STATEMENT:[1] "1" USERID:[5] "WASADMIN" USERHOST:[10] "admtrac001" TERMINAL:[5] "pts/3" ACTION:[3] "100" RETURNCODE:[4] "1017" COMMENT$TEXT:[20] "Authenticated by: OS" OS$USERID:[6] "oracle"
4) Stop auditing when you got enough information by connecting as SYSDBA
SQL> noaudit session by WASADMIN;
nice
ReplyDelete