Oracle Logoff triggers not fired

forum.sourcecode (2000-2005) & forum.td.sourcecode (2005-2010)
Chris Bunch

Oracle Logoff triggers not fired

Post by Chris Bunch » 03 Apr 2006, 12:35

 Posted by:  Chris Bunch 

Hi,

I wonder if anyone has any ideas on this one.
I want to be able to audit when my users logon/logoff an Oracle database. I
have created a very basic trigger
looking similar to this:

CREATE OR REPLACE TRIGGER logoff_audit_trigger
BEFORE LOGOFF ON DATABASE
DECLARE
BEGIN
insert into temp_tab date_field values (sysdate);
END;

Every time someone logs off, for instance in SQL*Plus, it will
add a sysdate value in the temp_tab however when logging off my custom
application it will not.

Does Centura handle it's logoffs in some special way so that a
trigger of this nature will not work?

We are using the mysterious 'SQLDisconnect' function in Centura to logoff
users.
I suspect this is the root of the problem - any suggestions?

Any help is appreciated.

Thanks,

Chris

Keith Sandberg

Re: Oracle Logoff triggers not fired

Post by Keith Sandberg » 03 Apr 2006, 16:19

 Posted by:  Keith Sandberg 

Oracle's default auditing mechanism will record logons/logoffs for you
automatically... read the Oracle docs for more details. There is no need
to do this with your own code (unless you have some issue with the Oracle
auditing stuff?).

Jim McNamara

Re: Oracle Logoff triggers not fired

Post by Jim McNamara » 03 Apr 2006, 16:28

 Posted by:  Jim McNamara 

What is your session sharing setting?

Chris Bunch

Re: Oracle Logoff triggers not fired

Post by Chris Bunch » 04 Apr 2006, 11:34

 Posted by:  Chris Bunch 

Thanks - I have looked into this, and it looks like a timesaver - however I
don't think this will solve the initial problem that the disconnects are not
being fired when the user logsoff my application:

If SqlDisconnect( hSql )
! Unregister the SQL handle
Set __hWndHasSqlHandle[nSql] = hWndNULL
Return TRUE

Chris Bunch

Re: Oracle Logoff triggers not fired

Post by Chris Bunch » 04 Apr 2006, 11:34

 Posted by:  Chris Bunch 


Sorry - not sure what you mean, can you clarify?

Thanks,

Chris

Petra Zamburek

Re: Oracle Logoff triggers not fired

Post by Petra Zamburek » 07 Apr 2006, 09:29

 Posted by:  Petra Zamburek 

Oracle 9.2.0.3.0:

CREATE TABLE yourschema.logon_logoff_log (
terminal VARCHAR2 (32),
osuser VARCHAR2 (32),
username VARCHAR2 (32),
sessionid NUMBER,
program VARCHAR2 (254),
logon_time DATE,
logoff_time DATE);
COMMENT ON TABLE yourschema.logon_logoff_log IS 'protocol of all logon and
logoff';

CREATE OR REPLACE TRIGGER sys.logon_log
AFTER LOGON ON DATABASE

DECLARE PRAGMA AUTONOMOUS_TRANSACTION;

BEGIN
IF USERENV ('sessionid') 0 THEN -- no oracle-processes (e.g.
background-procs)
INSERT INTO yourschema.logon_logoff_log (terminal, osuser, username,
program, sessionid, logon_time)
SELECT USERENV ('terminal'), sys_context ('userenv', 'os_user'), USER,
program, USERENV ('sessionid'), SYSDATE
FROM v$session
WHERE audsid = USERENV ('sessionid');
COMMIT;
END IF;
END;
/
show error;

CREATE OR REPLACE TRIGGER sys.logoff_log
BEFORE LOGOFF ON DATABASE

DECLARE PRAGMA AUTONOMOUS_TRANSACTION;

BEGIN
IF USERENV ('sessionid') 0 THEN -- no oracle-processes (e.g.
background-procs)
UPDATE yourschema.logon_logoff_log
SET logoff_time = SYSDATE
WHERE terminal = USERENV ('terminal')
AND osuser = sys_context ('userenv', 'os_user')
AND username = USER
AND sessionid = USERENV ('sessionid');
COMMIT;
END IF;

END;
/
show error;

Petra

Chris Bunch

Re: Oracle Logoff triggers not fired

Post by Chris Bunch » 07 Apr 2006, 10:17

 Posted by:  Chris Bunch 


Thanks for the reponse Petra, and everyone else.
We have now solved this with the core Oracle Auditing functionality.
I remain mystified as to why our logoff trigger was not fired, even though
SQLPlus, PL/SQL Developer, etc logoffs fired it.

Cheers,

Chris

Return to “td.sourcecode”

Who is online

Users browsing this forum: [Ccbot] and 1 guest