Re: quick qestion about logon/logoff triggers - does system have to own them?

From: GS <GS_at_GS.com>
Date: Thu, 27 Sep 2007 14:26:54 GMT
Message-ID: <O4PKi.93585$Pd4.18085@edtnps82>

GS wrote:
<snip>

follow up - I have put the following in place, and it seems to work ok:

---

CREATE TABLE system.session_info
  (user_name VARCHAR2(30),

     log_date DATE,
     session_id VARCHAR2(30),
     ip_addr VARCHAR2(30),
     hostname VARCHAR2(30),
     auth_type VARCHAR2(30),
     event VARCHAR2(8),
     os_user varchar2(30),
     program    VARCHAR2(30))

  tablespace tools;

CREATE OR REPLACE TRIGGER TRG_LOGOFF_INFO BEFORE LOGOFF
ON DATABASE
DECLARE

    session_id VARCHAR2(30);
    ip_addr    VARCHAR2(30);
    hostname   VARCHAR2(30);
    os_user    VARCHAR2(30);
    auth_type  VARCHAR2(30);
    event      VARCHAR2(10);
    program    VARCHAR2(30);

BEGIN
    SELECT sys_context ('USERENV', 'SESSIONID')     INTO session_id
    FROM dual;
    SELECT sys_context ('USERENV', 'IP_ADDRESS')     INTO ip_addr
    FROM dual;
    SELECT sys_context ('USERENV', 'HOST')     INTO hostname
    FROM dual;
    SELECT sys_context ('USERENV', 'OS_USER')     INTO os_user
    from dual;
    SELECT sys_context ('USERENV', 'AUTHENTICATION_TYPE')     INTO auth_type
    FROM dual;
    select 'LOG OFF' into event from dual;     select program into program
    from v$session
    where rownum <=1
    and sys_context('USERENV','SESSIONID') = audsid;     INSERT INTO system.session_info VALUES     (user, sysdate, session_id, ip_addr, hostname, auth_type, event, os_user, program);

   END;
/

CREATE OR REPLACE TRIGGER TRG_LOGON_INFO AFTER LOGON
ON DATABASE
DECLARE

    session_id VARCHAR2(30);
    ip_addr    VARCHAR2(30);
    hostname   VARCHAR2(30);
    os_user    VARCHAR2(30);
    auth_type  VARCHAR2(30);
    event      VARCHAR2(10);
    program    VARCHAR2(30);

BEGIN
    SELECT sys_context ('USERENV', 'SESSIONID')     INTO session_id
    FROM dual;
    SELECT sys_context ('USERENV', 'IP_ADDRESS')     INTO ip_addr
    FROM dual;
    SELECT sys_context ('USERENV', 'HOST')     INTO hostname
    FROM dual;
    SELECT sys_context ('USERENV', 'OS_USER')     INTO os_user
    from dual;
    SELECT sys_context ('USERENV', 'AUTHENTICATION_TYPE')     INTO auth_type
    FROM dual;
    select 'LOG ON' into event from dual;     select program into program
    from v$session
    where rownum <=1
     and sys_context('USERENV','SESSIONID') = audsid;     INSERT INTO system.session_info VALUES     (user, sysdate, session_id, ip_addr, hostname, auth_type, event, os_user, program);

   END;
/

---

  I am trying to figure out now if I am getting an accurate report of which program is being used by the user on logon/logoff. I had to put the "where rownum <= 1" in because the trigger generated an error when trying to log off if that particular user was logged in more than once, particularly SYS user, which always shows a audsid/sessionid of 0. I initially put in a "where user not in ('SYS') clause, but this also caused an error when logging out from SYS (and the bean-counters say I have to track ALL logins)

 From initial testing it seems accurate and works, but I am wondering if I am better off digging into the create context function and creating a "program" variable(?) since that value is not in userenv. Would this be a better approach than hitting the v$session view? Can a context like this be created easily?

thanks Received on Thu Sep 27 2007 - 09:26:54 CDT