Tuesday, March 15, 2011

Email Notification for LDBO login

CREATE OR REPLACE TRIGGER LDBO_logon_notifications
after logon on database
DECLARE
b VARCHAR2(3) := UTL_TCP.CRLF;
l_subject VARCHAR2(40) := 'Alert - LDBO Login';
l_message VARCHAR2(500);

BEGIN
if (USER in ('LDBO') AND SYS_CONTEXT('USERENV','Module') not IN ('vfp9.exe') AND SYS_CONTEXT( 'USERENV', 'IP_ADDRESS' ) NOT IN ('192.168.0.25','192.168.1.84','192.168.1.199','192.168.1.64','192.168.1.126','192.168.1.127','192.168.1.95','192.168.1.163') ) then

l_message :=
'Date/Time: ' || TO_CHAR( SYSDATE, 'mm/dd/yyyy hh24:mi:ss' ) || b;
l_message :=
l_message || 'OS User: ' || SYS_CONTEXT( 'USERENV', 'OS_USER' ) || b;

l_message :=
l_message || 'Host: ' || SYS_CONTEXT( 'USERENV', 'HOST' ) || b;
l_message :=
l_message || 'Terminal: ' || SYS_CONTEXT( 'USERENV', 'TERMINAL' ) || b;

l_message :=
l_message || 'IP Address: ' || SYS_CONTEXT( 'USERENV', 'IP_ADDRESS' ) || b;
l_message :=
l_message || 'Database Name: ' || ora_database_name || b;
l_message :=
l_message || 'Application Name: ' || SYS_CONTEXT('USERENV','Module') || b;

BEGIN
utl_mail.send
( sender => 'kshitij.rakesh@arihantcapital.com',
recipients => 'kshitij.rakesh@arihantcapital.com',
subject => l_subject,
message => l_message );

EXCEPTION
WHEN others THEN
RAISE;

END;
END IF;
END LDBO_logon_notifications;
/

No comments:

Post a Comment

Followers