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