Skip to Main Content

Trouble with Triggers

PhelitJan 27 2012 — edited Oct 5 2016
Hi,

I am gathering the Logon, logoff and logon fail data on a table using triggers.

These are the scripts I use:

Sequence and Table:

---------------------------------------------------------

CREATE SEQUENCE seq_numero
INCREMENT by 1
MINVALUE 1
MAXVALUE 9999999999
START with 1;


CREATE TABLE logonaudittable
(
id NUMBER,
event VARCHAR2(15),
sid NUMBER,
serial# NUMBER,
orario DATE,
username VARCHAR2(30),
osuserid VARCHAR2(30),
machinename VARCHAR2(64)
)
/

---------------------------------------------------------
And here the triggers:

CREATE OR REPLACE TRIGGER logonauditing AFTER LOGON ON database
DECLARE
id NUMBER(10);
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 WHERE audsid = userenv('sessionid');
BEGIN
SELECT seq_numero.nextval INTO id FROM dual;
OPEN c1;
FETCH c1 INTO v_sid, v_serial, osuserid, machinename;

INSERT INTO logonaudittable VALUES ( id, 'LOGON', v_sid, v_serial, sysdate,
user, osuserid, machinename );

CLOSE c1;
END;
/




CREATE OR REPLACE TRIGGER logoffauditing
BEFORE LOGOFF ON database
DECLARE
id NUMBER(10);
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 WHERE audsid = userenv('sessionid');
BEGIN
SELECT seq_numero.nextval INTO id FROM dual;
OPEN c1;
FETCH c1 INTO v_sid, v_serial, osuserid, machinename;

INSERT INTO logonaudittable VALUES ( id, 'LOGOFF', v_sid, v_serial, sysdate,
user, osuserid, machinename );

CLOSE c1;
END;
/




CREATE OR REPLACE TRIGGER logonfailauditing
AFTER SERVERERROR ON database
DECLARE
id NUMBER(10);
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 WHERE audsid = userenv('sessionid');
BEGIN
IF (IS_SERVERERROR(1017)) THEN
OPEN c1;
FETCH c1 INTO v_sid, v_serial, osuserid, machinename;
SELECT seq_numero.nextval INTO id FROM dual;
INSERT INTO logonaudittable VALUES ( id, 'FAILLOGON', v_sid, v_serial, sysdate,
user, osuserid, machinename );
CLOSE c1;
END IF;
END;
/


---------------------------------------------------------


I have Oracle 10.2.0.4 and Windows server 2003 R2

My problem is that I need to gather the info only of some users and not all of them. How can I modify the triggers so that I only get for example SYS and SYSTEM users? I tried various things, but they are wrong. Please help!

Thank you!
Comments
Post Details
Added on Jan 27 2012
19 comments
980 views