Re: On 9i and 10g, When an Oracle User Last Accessed the Database?
From: joel garry <joel-garry_at_home.com>
Date: Sun, 30 Aug 2009 12:44:12 -0700 (PDT)
Message-ID: <ff85e356-1fa9-4831-84ee-b2fc020a3817_at_b18g2000vbl.googlegroups.com>
On Aug 30, 9:08 am, Mark D Powell <Mark.Pow..._at_eds.com> wrote:
> 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/cfgau...
>
> See topic: Auditing Connections and Disconnections
>
> HTH -- Mark D Powell ---
Date: Sun, 30 Aug 2009 12:44:12 -0700 (PDT)
Message-ID: <ff85e356-1fa9-4831-84ee-b2fc020a3817_at_b18g2000vbl.googlegroups.com>
On Aug 30, 9:08 am, Mark D Powell <Mark.Pow..._at_eds.com> wrote:
> 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/cfgau...
>
> See topic: Auditing Connections and Disconnections
>
> HTH -- Mark D Powell ---
Maybe the OP meant "when a given user last accessed the database? " since that is what he wrote, and that could be a lot different than when the user connected. For example, see EM. Agent users could be connected for a long time. Do a ps -ef (or your equivalent) and look for LOCAL=NO with the number of parameters that your ps gives when processes are more than a day old, or just use EM to see how long connected.
jg
-- _at_home.com is bogus. http://www3.signonsandiego.com/stories/2009/aug/29/online-poker-funds-seized-will-stay-put/?uniontribReceived on Sun Aug 30 2009 - 14:44:12 CDT