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

From: Palooka <nobody_at_nowhere.com>
Date: Sat, 29 Aug 2009 00:05:13 +0100
Message-ID: <JoZlm.9663$I07.163_at_newsfe04.ams2>

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 Received on Fri Aug 28 2009 - 18:05:13 CDT