Oracle: check the existance of logon/logoff triggers
Friday, July 25th, 2008SELECT DECODE((COUNT(trigger_name)),0,’LOGON trigger missing’, ‘Number of logon triggers: ‘ || COUNT(trigger_name) ) “INFO”
FROM sys.dba_triggers
WHERE TRIGGERING_EVENT LIKE ‘LOGON%’ AND status=’ENABLED’ AND owner=’SYS’
UNION
SELECT DECODE((COUNT(trigger_name)),0,’LOGOFF trigger missing’,”, ‘Number of logoff triggers:’ || COUNT(trigger_name)) “INFO”
FROM sys.dba_triggers
WHERE TRIGGERING_EVENT LIKE ‘LOGOFF%’ AND status=’ENABLED’ AND owner=’SYS’
The field TRIGGERING_EVENT could have the spaces at the end! Very clever…
Oracle: logon trigger
Sunday, November 4th, 2007Create 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)
);