Re: On 9i and 10g, When an Oracle User Last Accessed the Database?
From: Mark D Powell <Mark.Powell_at_eds.com>
Date: Sun, 30 Aug 2009 09:08:11 -0700 (PDT)
Message-ID: <54420225-ccc8-4e3b-a178-aae9b172b319_at_z34g2000vbl.googlegroups.com>
On Aug 30, 9:45 am, Maxim Demenko <mdeme..._at_gmail.com> wrote:
> ddf wrote:
> > 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
>
> Well, the docs are relatively clear on that question -http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statem...
> <quote>
> System_privilege
>
> Specify a system privilege to audit SQL statements that are authorized
> by the specified system privilege.
>
> Rather than specifying many individual system privileges, you can
> specify the roles CONNECT, RESOURCE, and DBA. Doing so is equivalent to
> auditing all of the system privileges granted to those roles.
> </quote>
>
> If "audit connect" was issued, all system privileges which connect role
> contains (contains == granted to) are audited ( i.e. - only one from
> 10g onwards - the create session privilege). Summarizing, i would say -
> the audit create session and audit connect are practically the same
> (create session will be audited regardless it privilege was granted
> directly or via role connect). The same is valid for "audit resource" -
> if one issue a "create table" regardless it privilege was given directly
> or via role, it will be audited.
>
> So, the auditing mentioned roles are simply shortcuts to corresponging
> audit individual statements ( i.e., "audit resource" means
> audit create trigger
> audit create sequence
> audit create type
> audit create procedure
> audit create cluster
> audit create operator
> audit create indextype
> audit create table
> )
>
> As far as i interpret the Palooka's statement - audit connect means
> audit usage of connect role - it is wrong interpretation, because, that
> implies for me - the user should have a connect role granted to be
> audited ( which is obviously not the case). Another possible
> interpretation could be - audit connect means auditing of system
> privileges which connect role contains - is obviously correct, but this
> is the David's interpretation ( he wasn't agreed with).
>
> Just my understanding on how it works, maybe somebody will correct me.
>
> Best regards
>
> Maxim- Hide quoted text -
>
> - Show quoted text -
Date: Sun, 30 Aug 2009 09:08:11 -0700 (PDT)
Message-ID: <54420225-ccc8-4e3b-a178-aae9b172b319_at_z34g2000vbl.googlegroups.com>
On Aug 30, 9:45 am, Maxim Demenko <mdeme..._at_gmail.com> wrote:
> ddf wrote:
> > 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
>
> Well, the docs are relatively clear on that question -http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statem...
> <quote>
> System_privilege
>
> Specify a system privilege to audit SQL statements that are authorized
> by the specified system privilege.
>
> Rather than specifying many individual system privileges, you can
> specify the roles CONNECT, RESOURCE, and DBA. Doing so is equivalent to
> auditing all of the system privileges granted to those roles.
> </quote>
>
> If "audit connect" was issued, all system privileges which connect role
> contains (contains == granted to) are audited ( i.e. - only one from
> 10g onwards - the create session privilege). Summarizing, i would say -
> the audit create session and audit connect are practically the same
> (create session will be audited regardless it privilege was granted
> directly or via role connect). The same is valid for "audit resource" -
> if one issue a "create table" regardless it privilege was given directly
> or via role, it will be audited.
>
> So, the auditing mentioned roles are simply shortcuts to corresponging
> audit individual statements ( i.e., "audit resource" means
> audit create trigger
> audit create sequence
> audit create type
> audit create procedure
> audit create cluster
> audit create operator
> audit create indextype
> audit create table
> )
>
> As far as i interpret the Palooka's statement - audit connect means
> audit usage of connect role - it is wrong interpretation, because, that
> implies for me - the user should have a connect role granted to be
> audited ( which is obviously not the case). Another possible
> interpretation could be - audit connect means auditing of system
> privileges which connect role contains - is obviously correct, but this
> is the David's interpretation ( he wasn't agreed with).
>
> Just my understanding on how it works, maybe somebody will correct me.
>
> Best regards
>
> Maxim- Hide quoted text -
>
> - Show quoted text -
I agree with Maxim. Using the Oracle security manual for 10gR2 the AUDIT SESSION command is identified as the proper setting to use to audit Oracle session creation and disconnect. This command has been available since at least version 7.0.
http://download.oracle.com/docs/cd/B19306_01/network.102/b14266/cfgaudit.htm#i1014788
See topic: Auditing Connections and Disconnections
HTH -- Mark D Powell -- Received on Sun Aug 30 2009 - 11:08:11 CDT