Re: Session variables and PL/SQL

Guess what, I am using the auditing feature of Oracle, but the user_audit_trail view does not tell me, for just those "very important columns" who changed what, (from A to B etc.) as I want. (audit table update, table insert, table delete by scott, john... ) But, sigh, I'll just reinvent the wheel, for a change.

Ted

On Sun, 20 Jul 2003 06:20:12 -0700, "Anna C. Dent" <anacedent_at_hotmail.com> wrote:

>Ted Knijff wrote:
>> Sorry I did not specify the problem clearly enough :
>> - the job is to create triggers which perform audit functions
>> - when, where did this user logon
>> - when, where did he logoff
>> - what did he do inbetween
>> under the premise: the same user can log on multiple times, from
>> different PC's, with different apllications ....
>>
>> My option seems to be :
>> Create a logon trigger with
>>
>> -select * from v$session where user#=(select uid from dual)
>> and status='ACTIVE'
>> (we hope the others are not active just right now)
>> The SID in v$session has a value for example 7.
>>
>> -select userenv('SESSIONID') as MY_SID from dual
>> The USERENV('SESSIONID') has a value '735'.
>>
>> -insert into MY_LOGON_LOGTABLE ... MY_SID, LOGIN_DATE etc.
>>
>> Then use this table in my triggers to record :
>> who changed what, from where, in which session, ...
>>
>> But is there an easier way to find the SESSIONID associated
>> with the UID / USERENV or whatever ?
>>
>> Ted
>>
>
>I'm not clear on why you want to re-invent the wheel.
>You might want to take a serious look to the "built-in"
>AUDIT capabilities available in Oracle.
>

EMail: knijff_at_bigfoot.com Received on Sun Jul 20 2003 - 17:24:26 CDT