web analytics
«  
  »

Oracle: logon trigger

Create the table to save logon information:

CREATE TABLE logonaudittable (
event VARCHAR2(10),
sid NUMBER,
serial# NUMBER,
TIMESTAMP DATE,
username VARCHAR2(30),
osuserid VARCHAR2(30),
machinename VARCHAR2(64)
);

Trigger for logons:

CREATE OR REPLACE TRIGGER logon_trg
AFTER LOGON ON DATABASE
DECLARE machinename VARCHAR2(64);
 osuserid VARCHAR2(30);
 v_sid NUMBER(10);
 v_serial NUMBER(10);
CURSOR c1 IS
 SELECT sid, serial#, osuser, machine FROM v$session v
 WHERE v.sid = (SELECT sid FROM gv$mystat WHERE ROWNUM = 1 ) ;
BEGIN
OPEN c1;
FETCH c1 INTO v_sid, v_serial, osuserid, machinename;
INSERT INTO logonaudittable VALUES ( 'LOGON',
 v_sid, v_serial,
 SYSDATE, USER, osuserid, machinename );
CLOSE c1;
END;

Trigger for logoffs:

CREATE OR REPLACE TRIGGER logoff_trg
BEFORE LOGOFF ON DATABASE
DECLARE machinename VARCHAR2(64);
osuserid VARCHAR2(30);
v_sid NUMBER(10);
v_serial NUMBER(10);
CURSOR c1 IS
 SELECT sid, serial#, osuser, machine FROM v$session v
 WHERE v.sid = (SELECT sid FROM gv$mystat WHERE ROWNUM = 1 ) ;
BEGIN
OPEN c1;
FETCH c1 INTO v_sid, v_serial, osuserid, machinename;
INSERT INTO logonaudittable VALUES ( 'LOGOFF', v_sid, v_serial, SYSDATE, USER, osuserid, machinename );    
CLOSE c1;
END;
September 24th, 2009 | Tags: , , | Category: Oracle

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

  

  

  

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Categories

Archives

A sample text widget

Etiam pulvinar consectetur dolor sed malesuada. Ut convallis euismod dolor nec pretium. Nunc ut tristique massa.

Nam sodales mi vitae dolor ullamcorper et vulputate enim accumsan. Morbi orci magna, tincidunt vitae molestie nec, molestie at mi. Nulla nulla lorem, suscipit in posuere in, interdum non magna.