Re: Oracle8i Logon Trigger

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Sat, 6 Jan 2001 19:53:40 -0000
Message-ID: <978810633.6616.0.nnrp-13.9e984b29@news.demon.co.uk>

You need something like this

a) The privilege 'create any context'
b) The privilege 'create (any) trigger'
c) The privilege to select from sys.v_$session

You then need to:

    Create a context
    Create a package for the context
    Create the logon trigger to set the context

When testing - always create the logon trigger for a schema (JPL in the example below), not on the database, in case something goes badly wrong.

I did the following in schema JPL:

create context uid_context using uid_package;

create or replace package uid_package as -- Could have set both in the same procedure.

    procedure set_sid;
    procedure set_serial;
end;
/

create or replace package body uid_package as

procedure set_sid is

    m_sid number;
begin

    select sid into m_sid from v$session     where audsid = sys_context('userenv','sessionid');     dbms_session.set_context('uid_context','SID',m_sid); end;

procedure set_serial is

    m_serial number;
begin

    select serial# into m_serial from v$session     where audsid = sys_context('userenv','sessionid');     dbms_session.set_context('uid_context','SERIAL',m_serial); end;

end;
/

create or replace trigger jpl_logon
after logon on jpl.schema
begin

     jpl.uid_package.set_sid;
     jpl.uid_package.set_serial;

end;
/

On the next connect, you can test with:

set serveroutput on size 1000000 format wrapped execute dbms_output.put_line(sys_context('uid_context','sid')); execute dbms_output.put_line(sys_context('uid_context','serial'));

--
Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

Practical Oracle 8i:  Building Efficient Databases

Publishers:  Addison-Wesley
See a first review at:
http://www.ixora.com.au/resources/index.htm#practical_8i
More reviews at: http://www.jlcomp.demon.co.uk/book_rev.html



Ken Brown wrote in message
<25B11499EC0272449DD9D7BC33FCCB610987BB_at_dw2.dialwrap.com>...


>What I want is Logon trigger to identify the correct SID and Serial#

>from v$session when a user logs on.

>I then want to set these variables into a context for the user.

>

>Reasoning

>Users are allowed multiple logons

>I am logging activity including v$session data and I don't want to have

>to parse the view to find the correct entry (if I can this way) for the

>record.

>All non data elements to be logged (and are static) should be stored in

>the contect or processing into the log records.

>

>i.e.

>Scott session 1 deletes a record using sqlplus - I have to log as a

>sqlplus delete

>Scott session 2 deletes a record using forms - I have to log as a forms

>delete

>

>Any ideas?

>

Received on Sat Jan 06 2001 - 13:53:40 CST