Re: Tracing the rejected connection

On Mar 21, 10:51 am, Steve Howard <stevedhow..._at_gmail.com> wrote:
> On Mar 20, 11:07 pm, DriVE_mE_cRazY <k.senthilmuru..._at_gmail.com>
> wrote:
>
> > Hi all,
>
> > We are facing a strange issue in our database. We are getting alert in
> > a particular time period that a particular database user ( say X )
> > has exceeded log on failures count.I believe some program is  trying
> > to connect to db as  oracle database user (X) from a unix account
> > ( say Y ). sudo from that unix account to oracle user account ( from Y
> > to X ) is disabled.No cron jobs, batch programs are running at that
> > time.I'm very much confused how to trace that program/user
>
> > Please help me .
>
> > thanks,
>
> > senthil
>
> Hi,
>
> Try...
>
> SQL> audit connect whenever not successful;
>
> Audit succeeded.
>
> SQL> conn rep/foo
> ERROR:
> ORA-01017: invalid username/password; logon denied
>
> Warning: You are no longer connected to ORACLE.
> SQL> conn / as sysdba
> Connected.
> SQL> select count(*) from aud$;
>
>   COUNT(*)
> ----------
>          0
>
> SQL> show parameter audit_trail
>
> NAME                                 TYPE        VALUE
> ------------------------------------ -----------
> ------------------------------
> audit_trail                          string      NONE
> SQL> alter system set audit_trail=db scope=spfile;
>
> System altered.
>
> SQL> startup force
> ORACLE instance started.
>
> Total System Global Area  167772160 bytes
> Fixed Size                  1260696 bytes
> Variable Size             109052776 bytes
> Database Buffers           54525952 bytes
> Redo Buffers                2932736 bytes
> Database mounted.
> Database opened.
> SQL> conn rep/foo
> ERROR:
> ORA-01017: invalid username/password; logon denied
>
> Warning: You are no longer connected to ORACLE.
> SQL> conn / as sysdba
> Connected.
> SQL> select userid,timestamp# from aud$;
>
> USERID                         TIMESTAMP#
> ------------------------------ -------------------
> REP
>
> SQL>
>
> HTH,
>
> Steve

The DBA_AUDIT_SESSION view and the OS usernane and host columns (os_username, userhost) might be of interest. The same information could be extracted from the DBA_AUDIT_TRAIL view but if other auditing is also being done the specialized view offers ease of use advantages.

HTH -- Mark D Powell -- Received on Fri Mar 21 2008 - 10:16:23 CDT