Email Notification if Total Session count equal to or more than 30.

The e-mail in the solution to this recipe is sent using Oracle’s UTL_MAIL package. By default it is not enabled.

To install it, you must log in as the SYS user and execute the utlmail.sql and prvtmail.plb scripts that reside within the $ORACLE_HOME/rdbms/admin directory.
An outgoing mail server must also be defined by setting the SMTP_OUT_SERVER initialization parameter
prior to use.

SQL> @?/rdbms/admin/utlmail.sql

Package created.

Synonym created.

SQL> @?/rdbms/admin/prvtmail.plb

Package created.

Package body created.

Grant succeeded.

Package body created.

No errors.

SQL> show parameter smtp

NAME                           TYPE        VALUE

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

smtp_out_server              string      smtp-init.service.oracleforensics.com:25

If SMTP server not defined defined it using below command

Sql> alter system set smtp_out_server='smtp-init.service.oracleforensics.com:25' scope=spfile;

Bounce the database.

---CREATE TABLE which store the login details

CREATE TABLE login_audit_table(

ID NUMBER PRIMARY KEY, -- Populated by sequence number login_audit_seq

AUDIT_DATE DATE NOT NULL,

AUDIT_USER VARCHAR2(50) NOT NULL,

AUDIT_IP VARCHAR2(50) NOT NULL,

AUDIT_HOST VARCHAR2(50) NOT NULL);

---Create sequence

CREATE SEQUENCE  "SYS"."LOGIN_AUDIT_SEQ"  MINVALUE 1 MAXVALUE 1000 INCREMENT

BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE;

---Create Procedure “SEND_EMAIL”

CREATE OR REPLACE PROCEDURE "SYS"."SEND_EMAIL" (to_address IN VARCHAR2,

subject IN VARCHAR2,

message IN VARCHAR2) AS

BEGIN

UTL_MAIL.send(sender => 'server_mail@gmail.com',

recipients => to_address,

subject => subject,

message => message,

mime_type => 'text; charset=us-ascii');

END;

NOTE: Where 'server_mail@gmail.com' is an email id of server.

---Create trigger

CREATE OR REPLACE TRIGGER "SYS"."LOGIN_AUDIT_EVENT"

AFTER LOGON ON DATABASE

DECLARE

v_subject VARCHAR2(100) := 'User login audit event triggered';

v_message VARCHAR2(1000);

v_count  NUMBER;

BEGIN

INSERT INTO login_audit_table values(

Login_audit_seq.nextval,

Sysdate,

SYS_CONTEXT('USERENV','SESSION_USERID'),

SYS_CONTEXT('USERENV','IP_ADDRESS'),

SYS_CONTEXT('USERENV','HOST'));

select count(1) into v_count from v$session;

if v_count >= 30 then

v_message := 'User ' ||

SYS_CONTEXT('USERENV','SESSION_USERID') ||

' Total Session count reached   ' ||

                         v_count||'    '||

sysdate ||

' from host ' ||

SYS_CONTEXT('USERENV','HOST');

SEND_email('oracleforensics4U@gmail.com',v_subject,v_message);

end if;

END;

NOTE: Where 'oracleforensics4U@gmail.com' is an email id of dba_group.

You can check the data stored in table login_audit_table using below query

select to_char(AUDIT_DATE,'dd-mm-yyyy hh24-mi-ss'),AUDIT_USER,AUDIT_IP,AUDIT_HOST from login_audit_table;

Mail will automatically shoot once the total session count is equal to or greater than 30. This can be checked using below sql

Sql> select count(*) from v$session;