Test of Logon/logoff triggers

Test environment: Oracle 9.2.0.8

Steps:

1. Create a table to store access info

     create table db_admin.user_access
     (
    user_id           varchar2(30),
    os_user           varchar2(30),
    session_id           number(8),
    host              varchar2(30),
    last_program      varchar2(48),
    last_action       varchar2(32),
    last_module       varchar2(32),
    logon_day                 date,
    logon_time        varchar2(10),
    logoff_day                date,
    logoff_time       varchar2(10),
    elapsed_minutes       number(8)
     )
     ;

2. Create logon trigger

   create or replace trigger db_admin.logon_audit_trigger
   AFTER LOGON ON DATABASE 
   BEGIN
   insert into db_admin.user_access values(
      user,
      sys_context('USERENV','OS_USER'),
      sys_context('USERENV','SESSIONID'),
      sys_context('USERENV','HOST'),
      null,
      null,
      null,
      sysdate,
      to_char(sysdate, 'hh24:mi:ss'),
      null,
      null,
      null
   );
   END;
   /
 
3. Create logoff trigger

   create or replace trigger db_admin.logoff_audit_trigger
   BEFORE LOGOFF ON DATABASE
   BEGIN
   -- ***************************************************
   -- Update the last action accessed
   -- ***************************************************
   update
   user_access
   set
   last_action = (select action from v$session where     
   sys_context('USERENV','SESSIONID') = audsid)
   where
   sys_context('USERENV','SESSIONID') = session_id;
   --***************************************************
   -- Update the last program accessed
   -- ***************************************************
   update
     user_access
   set
   last_program = (select program from v$session where     
   sys_context('USERENV','SESSIONID') = audsid)
   where
   sys_context('USERENV','SESSIONID') = session_id;
   -- ***************************************************
   -- Update the last module accessed
   -- ***************************************************
   update
      user_access
   set
   last_module = (select module from v$session where     
   sys_context('USERENV','SESSIONID') = audsid)
   where
   sys_context('USERENV','SESSIONID') = session_id;
   -- ***************************************************
   -- Update the logoff day
   -- ***************************************************
   update
      user_access 
   set
      logoff_day = sysdate
   where
      sys_context('USERENV','SESSIONID') = session_id;
   -- ***************************************************
   -- Update the logoff time
   -- ***************************************************
   update
      user_access
   set
      logoff_time = to_char(sysdate, 'hh24:mi:ss')
   where
      sys_context('USERENV','SESSIONID') = session_id;
   -- ***************************************************
   -- Compute the elapsed minutes
   -- ***************************************************
   update
   user_access
   set
   elapsed_minutes =     
   round((logoff_day - logon_day)*1440)
   where
   sys_context('USERENV','SESSIONID') = session_id;
   END;
   /
 
4. Generate report:


SQL> ho cat get_rpt.sql
col db_user format a10
col os_user format a10
col host format a15
col logon_time format a25
col logoff_time format a25
set linesize  100
 
select
       user_id db_user,
       os_user,
       host,
       to_char(logon_day, 'YYYY-MM-DD HH24:MI:SS') logon_time,
       to_char(logoff_day, 'YYYY-MM-DD HH24:MI:SS') logoff_time
from db_admin.user_access
where user_id in ('SYS', 'SYSTEM')
/



Example:

DB_USER    OS_USER    HOST            LOGON_TIME                LOGOFF_TIME
---------- ---------- --------------- ------------------------- -------------------------
SYSTEM     oracle     bnscqsd2        2008-05-08 08:35:31       2008-05-08 08:35:39
SYS        oracle     bnscqsd2        2008-05-08 08:49:30
SYSTEM     oracle     bnscqsd2        2008-05-08 08:49:43       2008-05-08 08:49:47
SYS        oracle     bnscqsd2        2008-05-08 09:03:20
SYS        oracle     bnscqsd2        2008-05-08 09:16:58
SYS        oracle     bnscqsd2        2008-05-08 09:18:11
SYS        oracle     bnscqsd2        2008-05-08 09:29:44
SYSTEM     oracle     bnscqsd2        2008-05-08 09:30:18       2008-05-08 09:30:37
SYS        oracle     bnscqsd2        2008-05-08 09:30:37
SYS        oracle     bnscqsd2        2008-05-09 01:00:00
SYS        oracle     bnscqsd2        2008-05-10 01:00:00
SYS        oracle     bnscqsd2        2008-05-11 01:00:00
SYS        oracle     bnscqsd2        2008-05-12 00:00:01
SYS        oracle     bnscqsd2        2008-05-12 01:00:01
SYSTEM     oracle     bnscqsd2        2008-05-12 11:57:43       2008-05-12 11:57:54
SYSTEM     oracle     bnscqsd2        2008-05-12 11:58:13


Note: Logoff info about SYS user failed to get inserted .. 
      The record in the user_access table is updated according to the 'SESSIONID',
      however,  SESSIONID of the session created by 'SYS' always zero


SQL> show user
USER is "OPS$ORACLE"
SQL> select sys_context('USERENV','SESSIONID') from dual;    

SYS_CONTEXT('USERENV','SESSION
-------------------------------------------------------------
1140840
 
SQL> conn / as sysdba
Connected.
SQL> select sys_context('USERENV','SESSIONID') from dual;
 
SYS_CONTEXT('USERENV','SESSION
-------------------------------------------------------------
0
 
SQL> connect system
Enter password: 
Connected.
SQL> select sys_context('USERENV','SESSIONID') from dual;
 
SYS_CONTEXT('USERENV','SESSION
-------------------------------------------------------------
1140841
 
SQL> conn / as sysdba
Connected.
SQL> select sys_context('USERENV','SESSIONID') from dual;
 
SYS_CONTEXT('USERENV','SESSION
-------------------------------------------------------------
0
 
SQL>