session identifier for connections auditing

From: Le JeanMimi <scjm_at_noos.fr>
Date: 7 Nov 2002 11:47:29 -0800
Message-ID: <c7be5048.0211071147.6e5dd7fe@posting.google.com>

First of all, thank you (especially Jonathan Lewis) for the answers to my previous questions.

My boss wants to log users connections and deconnections to the database (8.1.6) in a table.

I created :
- a table in which each line refers to a connection or a deconnection.   (=> two lines for a started then closed individual session) - an after logon trigger which logs Connections - a before logon trigger which logs Deconnections

This works well - it seems.

Now I want to write a view which joins the table with itself to display information for each session : "started - ended - duration" So I need a good session identifier to match the two lines.

For joining I use the AUDSID stored in my table (from SYS_CONTEXT('USERENV','SESSIONID')).
I'm not sure it's the best solution...
-> is it (really) unique ? (what if the instance is restarted ?)
-> what if someone connects internal ?

 Is there a better identifier ? Or a better approach ?

TIA ! Jean-Michel

CREATE OR REPLACE TRIGGER system.cl_logon_trigger AFTER LOGON ON DATABASE
BEGIN   INSERT INTO system.connection_log
  (

    cl_id,
    cl_date_event,
    cl_type_event,
    cl_session_user,
    cl_session_userid,
    cl_sessionid,
    cl_host,
    cl_os_user,
    cl_ip_address,
    cl_network_protocol,
    cl_authentication_type,
    cl_instance

  )
  VALUES
  (
    seq_connection_log.NEXTVAL,
    SYSDATE,
    'C',

    SYS_CONTEXT('USERENV','SESSION_USER'),
    SYS_CONTEXT('USERENV','SESSION_USERID'),
    SYS_CONTEXT('USERENV','SESSIONID'),
    SYS_CONTEXT('USERENV','HOST'),
    SYS_CONTEXT('USERENV','OS_USER'),
    SYS_CONTEXT('USERENV','IP_ADDRESS'),
    SYS_CONTEXT('USERENV','NETWORK_PROTOCOL'),
    SYS_CONTEXT('USERENV','AUTHENTICATION_TYPE'),
    SYS_CONTEXT('USERENV','INSTANCE')

  );

  COMMIT; END;
/

SELECT

        C.cl_date_event "started"
        D.cl_date_event "ended",
        ROUND((NVL(D.cl_date_event,SYSDATE) -
C.cl_date_event)*60*24,2) "duration(min)",
        C.cl_session_user,
        C.cl_session_userid,
        C.cl_sessionid,
        C.cl_host,
        C.cl_os_user,
        C.cl_ip_address,
        C.cl_network_protocol,
        C.cl_authentication_type,
        C.cl_instance
  FROM
        connection_log C,
        connection_log D
  WHERE
        C.cl_sessionid      = D.cl_sessionid(+)
   AND  C.cl_type_event     = 'C'

   AND D.cl_type_event(+) = 'D'
; Received on Thu Nov 07 2002 - 13:47:29 CST