Oracle User Login / Error Email Notification

connect sys as sysdba user and run two scripts for install and configure utl_mail package

SQL> conn sys@orcl as sysdba
Enter password: ******
Connected.
SQL> @c:\oracle\product\10.2.0\db_1\rdbms\admin\utlmail.sql

Package created.


Synonym created.

SQL> @c:\oracle\product\10.2.0\db_1\rdbms\admin\prvtmail.plb;

Package body created.

No errors.

Set SMTP_OUT_SERVER parameter for smtp_exchange_server. This parameter is not modifiable means we have to bounce our database to set this parameter

SQL> alter system set smtp_out_server = 'mail.uniconindia.in' scope=spfile;



SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup

Grant EXECUTE privs to user which use utl_mail package.

SQL> grant execute on utl_mail to ldbo;

Grant succeeded.


---------------------------------Database Shutdown Notification-------------------


CREATE OR REPLACE TRIGGER LDBO_shut_notifications
before shutdown on database
DECLARE
b VARCHAR2(3) := UTL_TCP.CRLF;
l_subject VARCHAR2(40) := 'Problem Alert - LD Database is Down';
l_message VARCHAR2(500);

BEGIN
l_message :=
'Date/Time: ' || TO_CHAR( SYSDATE, 'mm/dd/yyyy hh24:mi:ssss' ) || 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 || 'Protocol: ' || SYS_CONTEXT( 'USERENV', 'NETWORK_PROTOCOL' ) || b;

l_message :=
l_message || 'Database Instance: ' || ora_instance_num || b;
l_message :=
l_message || 'Database Name: ' || ora_database_name || b;

BEGIN
utl_mail.send
( sender => 'Dbanotification@uniconindia.in',
recipients => 'dbamonitoring@uniconindia.in',
subject => l_subject,
message => l_message );

EXCEPTION
WHEN others THEN
RAISE;

END;
END LDBO_shut_notifications;
/

---------------------------------Database Startup Notification-------------------

CREATE OR REPLACE TRIGGER LDBO_start_notifications
after startup on database
DECLARE
b VARCHAR2(3) := UTL_TCP.CRLF;
l_subject VARCHAR2(40) := 'Recovery Alert - LD Database is Up';
l_message VARCHAR2(500);

BEGIN
l_message :=
'Date/Time: ' || TO_CHAR( SYSDATE, 'mm/dd/yyyy hh24:mi:ssss' ) || 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 || 'Protocol: ' || SYS_CONTEXT( 'USERENV', 'NETWORK_PROTOCOL' ) || b;

l_message :=
l_message || 'Database Instance: ' || ora_instance_num || b;
l_message :=
l_message || 'Database Name: ' || ora_database_name || b;

BEGIN
utl_mail.send
( sender => 'Dbanotification@uniconindia.in',
recipients => 'dbamonitoring@uniconindia.in',
subject => l_subject,
message => l_message );

EXCEPTION
WHEN others THEN
RAISE;

END;
END LDBO_start_notifications;
/




------------------------------------SYS User Logon-------------


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

BEGIN
if (USER in ('SYS')) then
l_message :=
'Date/Time: ' || TO_CHAR( SYSDATE, 'mm/dd/yyyy hh24:mi:ssss' ) || 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 || 'Protocol: ' || SYS_CONTEXT( 'USERENV', 'NETWORK_PROTOCOL' ) || b;

l_message :=
l_message || 'Database Instance: ' || ora_instance_num || b;
l_message :=
l_message || 'Database Name: ' || ora_database_name || b;

BEGIN
utl_mail.send
( sender => 'Dbanotification@uniconindia.in',
recipients => 'kgupta2@uniconindia.in',
subject => l_subject,
message => l_message );

EXCEPTION
WHEN others THEN
RAISE;

END;
END IF;
END SYS_logon_notifications;
/


------------------------------------LDBO- DBA User Logon-------------

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')) 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 || 'Protocol: ' || SYS_CONTEXT( 'USERENV', 'NETWORK_PROTOCOL' ) || b;

l_message :=
l_message || 'Database Instance: ' || ora_instance_num || b;
l_message :=
l_message || 'Database Name: ' || ora_database_name || b;

BEGIN
utl_mail.send
( sender => 'Dbanotification@uniconindia.in',
recipients => 'kgupta2@uniconindia.in',
subject => l_subject,
message => l_message );

EXCEPTION
WHEN others THEN
RAISE;

END;
END IF;
END LDBO_logon_notifications;
/

------------------------------------Failed Logon-------------

CREATE OR REPLACE TRIGGER failed_logon_notifications
AFTER SERVERERROR ON DATABASE
DECLARE
b VARCHAR2(3) := UTL_TCP.CRLF;
l_subject VARCHAR2(40) := 'Alert - Failed Login';
l_message VARCHAR2(500);

BEGIN
IF ora_is_servererror( 28000 ) THEN
l_message :=
'Date/Time: ' || TO_CHAR( SYSDATE, 'mm/dd/yyyy hh24:mi:ssss' ) || 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 || 'Protocol: ' || SYS_CONTEXT( 'USERENV', 'NETWORK_PROTOCOL' ) || b;

l_message :=
l_message || 'Database Instance: ' || ora_instance_num || b;
l_message :=
l_message || 'Database Name: ' || ora_database_name || b;

BEGIN
utl_mail.send
( sender => 'Dbanotification@uniconindia.in',
recipients => 'kgupta2@uniconindia.in',
subject => l_subject,
message => l_message );

EXCEPTION
WHEN others THEN
RAISE;

END;
END IF;
END failed_logon_notifications;




-----------------------------------Listener Error-----




CREATE OR REPLACE TRIGGER failed_listener_notifications
AFTER SERVERERROR ON DATABASE
DECLARE
b VARCHAR2(3) := UTL_TCP.CRLF;
l_subject VARCHAR2(40) := 'TNS:listener could not hand off client connection';
l_message VARCHAR2(500);

BEGIN
IF ora_is_servererror( 12518 ) THEN
l_message :=
'Date/Time: ' || TO_CHAR( SYSDATE, 'mm/dd/yyyy hh24:mi:ssss' ) || 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 || 'Protocol: ' || SYS_CONTEXT( 'USERENV', 'NETWORK_PROTOCOL' ) || b;

l_message :=
l_message || 'Database Instance: ' || ora_instance_num || b;
l_message :=
l_message || 'Database Name: ' || ora_database_name || b;

BEGIN
utl_mail.send
( sender => 'Dbanotification@uniconindia.in',
recipients => 'kgupta2@uniconindia.in',
subject => l_subject,
message => l_message );

EXCEPTION
WHEN others THEN
RAISE;

END;
END IF;
END failed_logon_notifications;





-----------------------------Trigger Insert--------------------


CREATE OR REPLACE TRIGGER insert_ecn
before insert on ldbo.TBLDIGITALSIGNEDREPORTS
DECLARE
b VARCHAR2(3) := UTL_TCP.CRLF;
l_subject VARCHAR2(40) := 'ECN Process Done Successfully';
l_message VARCHAR2(500);

BEGIN
if (USER in ('ECNUSER')) 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 || 'Protocol: ' || SYS_CONTEXT( 'USERENV', 'NETWORK_PROTOCOL' ) || b;

l_message :=
l_message || 'Database Instance: ' || ora_instance_num || b;
l_message :=
l_message || 'Database Name: ' || ora_database_name || b;

BEGIN
utl_mail.send
( sender => 'Dbanotification@uniconindia.in',
recipients => 'kgupta2@uniconindia.in',
subject => l_subject,
message => l_message );

EXCEPTION
WHEN others THEN
RAISE;

END;
END IF;
END insert_ecn;
/



---------------Trigger if login at unofficial time---------



CREATE OR REPLACE PROCEDURE secure_dml(dateval IN DATE)IS
BEGIN
IF TO_CHAR (dateval, 'HH24:MI') NOT BETWEEN '08:00' AND '20:00'
OR TO_CHAR (dateval, 'DY') IN ('SAT', 'SUN') THEN
RAISE_APPLICATION_ERROR (-20205, 'Changes only allowed during office hours');
END IF;
END secure_dml;
/

CREATE OR REPLACE TRIGGER secure_data
BEFORE INSERT OR UPDATE OR DELETE
ON orders
FOR EACH ROW
BEGIN
secure_dml(:NEW.datecol);
END;
/

INSERT INTO orders VALUES ('ABC', 999, SYSDATE-1);
INSERT INTO orders VALUES ('ABC', 999, SYSDATE-4/24);
INSERT INTO orders VALUES ('ABC', 999, SYSDATE+1);

------------Trigger To Disallow Entry Of Future Dates ------



CREATE OR REPLACE TRIGGER t_date
BEFORE INSERT
ON orders
FOR EACH ROW

DECLARE
bad_date EXCEPTION;
BEGIN
IF :new.datecol > SYSDATE THEN
RAISE_APPLICATION_ERROR(-20005,'Future Dates Not Allowed');
END IF;
END;
/

INSERT INTO orders VALUES ('ABC', 999, SYSDATE-1);
INSERT INTO orders VALUES ('ABC', 999, SYSDATE);
INSERT INTO orders VALUES ('ABC', 999, SYSDATE+1);


--------------------Holiday login-------

CREATE TABLE Company_holidays (Day DATE);


CREATE OR REPLACE TRIGGER Emp_permit_changes
BEFORE INSERT OR DELETE OR UPDATE ON Emp99
DECLARE
Dummy INTEGER;
Not_on_weekends EXCEPTION;
Not_on_holidays EXCEPTION;
Non_working_hours EXCEPTION;
BEGIN
/* check for weekends: */
IF (TO_CHAR(Sysdate, 'DY') = 'SAT' OR
TO_CHAR(Sysdate, 'DY') = 'SUN') THEN
RAISE Not_on_weekends;
END IF;
/* check for company holidays:*/
SELECT COUNT(*) INTO Dummy FROM Company_holidays
WHERE TRUNC(Day) = TRUNC(Sysdate);
/* TRUNC gets rid of time parts of dates: */
IF dummy > 0 THEN
RAISE Not_on_holidays;
END IF;
/* Check for work hours (8am to 6pm): */
IF (TO_CHAR(Sysdate, 'HH24') < 8 OR
TO_CHAR(Sysdate, 'HH24') > 18) THEN
RAISE Non_working_hours;
END IF;
EXCEPTION
WHEN Not_on_weekends THEN
Raise_application_error(-20324,'May not change '
||'employee table during the weekend');
WHEN Not_on_holidays THEN
Raise_application_error(-20325,'May not change '
||'employee table during a holiday');
WHEN Non_working_hours THEN
Raise_application_error(-20326,'May not change '
||'Emp_tab table during non-working hours');
END;




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