Friday, January 30, 2009

Oracle Logon Trigger for Auditing

Oracle Audit tables (aud$) don't collect program information, so you need a table and logon trigger if you want to collect this information.

CREATE TABLE cmc_audit_users
(
username VARCHAR2(30),
osuser VARCHAR2(30),
sid NUMBER ,
host VARCHAR2(30),
ip_address VARCHAR2(30),
program VARCHAR2(48),
logon_time DATE
) ;

CREATE OR REPLACE TRIGGER LOGON_AUDIT_TRIGGER
AFTER LOGON ON DATABASE
BEGIN
INSERT
INTO cmc_audit_users VALUES
(
sys_context('USERENV','SESSION_USER'),
sys_context('USERENV','OS_USER') ,
sys_context('USERENV','SID') ,
sys_context('USERENV','HOST') ,
sys_context('USERENV','IP_ADDRESS') ,
sys_context('USERENV','MODULE') ,
sysdate
);
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
/