login

Oracle: check the existance of logon/logoff triggers

Friday, July 25th, 2008

select 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, 2007

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)
);