Hi,

how can I best save and retrieve variables based on a session ?

The problem :
-a user logs in multiple times from different clients / from the same
client PC (i.e. multiple sessions, same user)
- I want to save the session parameters (login time etc. - as in
v$session view) and access these variables from PL/SQL
- do I have to save his data in a table and retrieve them every time ?
- how can I best access the session-id to uniquely identify the
session (performance) ?

TIA

Ted Knijff
EMail: knijff (AT) bigfoot (DOT) com

Ted Knijff wrote:

Personally I'd alter the default profile and make it impossible to do so.

But if you can't or choose not to ... look at SYSTEM TRIGGERS.
--
Daniel Morgan
http://www.outreach.washington.edu/e...ad/oad_crs.asp
damorgan@x.washington.edu
(replace 'x' with a 'u' to reply)

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


On Sat, 19 Jul 2003 11:08:45 -0700, "Anna C. Dent"
<anacedent (AT) hotmail (DOT) com> wrote:

EMail: knijff (AT) bigfoot (DOT) com

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 (DOT) com> wrote:

EMail: knijff (AT) bigfoot (DOT) com