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

From: Maxim Demenko <>
Date: Sun, 30 Aug 2009 15:45:30 +0200
Message-ID: <>

ddf wrote:
> On Aug 28, 6:05 pm, Palooka <> wrote:
>> On 08/28/2009 08:50 PM, ddf wrote:
>>> On Aug 28, 2:26 pm, Palooka<>  wrote:
>>>> On 08/28/2009 07:21 PM, ddf wrote:
>>>>> On Aug 28, 11:10 am, ""
>>>>> <>    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;
> 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
> ------------------------------ ---------
> 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 - <quote>

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 Received on Sun Aug 30 2009 - 08:45:30 CDT

Original text of this message