« Oracle: password function | Home | Check the default browser »

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;

Topics: Oracle, login | Submitter: checkthis

2 Responses to “Oracle: logon trigger”

  1. msmsr40 Says:
    December 2nd, 2008 at 11:25 pm

    i try to create triger after i create table logonaudittable
    but i find this error

    6/2 PL/SQL: SQL Statement ignored
    7/31 PL/SQL: ORA-00942: table or view does not exist

  2. checkthis Says:
    January 16th, 2009 at 7:38 am

    Just check the owner of the table and the trigger: they both should be created by sys user.

Comments

You must be logged in to post a comment.