Re: On 9i and 10g, When an Oracle User Last Accessed the Database?
Date: Sun, 30 Aug 2009 05:52:40 -0700 (PDT)
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:
> >>> 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;
SQL> grant create session to bloppo;
SQL> connect bloppo/yarg
SQL> connect / as sysdba
SQL> Select userid, max(nvl(logoff$time, sysdate)) last_logoff From sys.aud$
Group by userid;
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