Re: On 9i and 10g, When an Oracle User Last Accessed the Database?

From: ddf <oratune_at_msn.com>
Date: Sun, 30 Aug 2009 05:52:40 -0700 (PDT)
Message-ID: <ba5f9f37-cee8-450d-9959-c31b3b7b575b_at_j9g2000vbp.googlegroups.com>

On Aug 28, 6:05 pm, Palooka <nob..._at_nowhere.com> wrote:
> On 08/28/2009 08:50 PM, ddf wrote:
>
>
>
> > On Aug 28, 2:26 pm, Palooka<nob..._at_nowhere.com>  wrote:
> >> On 08/28/2009 07:21 PM, ddf wrote:
>
> >>> On Aug 28, 11:10 am, "if_inves..._at_yahoo.com"
> >>> <basis_consult..._at_hotmail.com>    wrote:
> >>>> Hi,
>
> >>>> On Oracle 10g and 9i, is there a way to find out when a given user
> >>>> last accessed the
> >>>> database?
>
> >>>> Thanks,
> >>>> QZ
>
> >>> In '10g' this is fairly easy; in the init.ora file set
>
> >>> audit_trail=db
>
> >>> then restart the database.  While still connected as SYS type:
>
> >>> audit connect;
>
> >>> and you're done as userid and logoff$time is populated in SYS.AUD$
> >>> (along with other bits of information).  To report the last logoff for
> >>> a user:
>
> >>> select userid, max(nvl(logoff$time, sysdate)) last_logoff
> >>> from sys.aud$
> >>> group by userid;
>
> >>> All users who have connected and disconnected or who are still
> >>> connected will be reported (those still connected will display a
> >>> last_logoff value of sysdate).
>
> >>> With '9i' you'll need two triggers and a table to do this; read here:
>
> >>>http://oratips-ddf.blogspot.com/2006/05/audit-this.html
>
> >>> to see a script to create a table, logon and logoff triggers to
> >>> provide that information.
>
> >> Audit session, not audit connect. And it works in 9i too. No need for
> >> triggers.
>
> >> Palooka- Hide quoted text -
>
> >> - Show quoted text -
>
> > Sorry, no, in 10g and later audit connect is a valid command and will
> > audit database  connections.
>
> Wrong. It audits use of the connect role.
>
> Palooka- Hide quoted text -
>
> - Show quoted text -

Explain, then, why this user accouint, absent the CONNECT role, has audit records in AUD$ after 'audit connect;' has bene executed:

SQL> create user bloppo identified by yarg;

User created.

SQL> grant create session to bloppo;

Grant succeeded.

SQL> connect bloppo/yarg
Connected.
SQL>
SQL> connect / as sysdba
Connected.
SQL> Select userid, max(nvl(logoff$time, sysdate)) last_logoff From sys.aud$
Group by userid;
  2 3

USERID                         LAST_LOGO
------------------------------ ---------
SYSTEM                         30-AUG-09
BLOPPO                         30-AUG-09

SQL> The statement enables connection auditing whether or not the user has been granted tbe CONNECT role.

David Fitzjarrell Received on Sun Aug 30 2009 - 07:52:40 CDT