create or replace trigger tr_smtp_oratools_conn after logon on database

/*****************************************************************************
Script Name ....: tr_smtp_oratools_conn.sql
Written by .....: Pavel L. Descoteaux, CEO and Founder
                  Copyleft 2008 by banjora [auxilium]
Description ....: This trigger creates the vehicle to identify non-allowable
                  programmes and apply the appropriate action
                  Simply install this trigger as the SYS/SYSDBA Administrator
                  privileges so that the SYS tables can be queried.
Dependencies ...: Oracle JServer needs to be installed and configured. Pointe
                  the IP address to the local SMTP (simple mail transport) 
                  server. No pipes orexternal procedures are required.
                  The oramon.omon_% reference tables must be accessible.
Considerations .: This script is provided for business and educational purposes
                  only, and is only supported by banjora [auxilium]. The script 
                  has been tested and appears to work as intended. You should 
                  always run new scripts on a test instance initially.
Version History : This code should not be modified without authorization from
                  the Database Administrator.

Date Description
------------ ----------------------------------------------------------------
2008-02-02   Completed and tested main body of the statement

*****************************************************************************/

declare
conn utl_smtp.connection;

emailserver varchar2(30) := '000.000.000.000'; -- SMTP Server IP Address
port number := 25;

crlf varchar2(2) := chr(13)||chr(10);
msg long ;
msg_body varchar2(4000);

vSubjectLine varchar2(1000);
vToList varchar2(2000);
vCcList varchar2(2000);
vBccList varchar2(2000);
vSenderEmail varchar2(2000);

vApplTool varchar2(255) ;
vApplVers varchar2(255) ;
vOraSid varchar2(255) ;
vOSAcct varchar2(255) ;
vMachID varchar2(255) ;
vDBUser varchar2(255) ;
vTermID varchar2(255) ;
vProcID varchar2(255) ;
vSessID varchar2(255) ;
vStatID varchar2(255) ;
vBGName varchar2(255) ;
vConTms date ;

vSecMessage varchar(2000) := 'Company Security Violation!';
vRecCount01 number(20) ;
vRecCount02 number(20) ;
vRecCount03 number(20) ;
vKillFlag varchar(1) ;
vActionTaken varchar2(255) ;


cursor c1 is
select
 hdr.emaillist_catid,
 dtl.email_address_id,
 hdr.emaillist_sortid
from
 oramon.omon_emaillist_hdr hdr,
 oramon.omon_emaillist_dtl dtl
where
 ((((upper(dtl.emaillist_grpid)) in ('SENDER','AUDITOR'))
 and ((trunc(dtl.effect_datetime)) <= trunc(sysdate))
 and ((dtl.term_datetime) is null))
 or (( dtl.term_datetime > trunc(sysdate)))
 and ((upper(dtl.site_location_id) = 'ALL')))
 and hdr.emaillist_catid = dtl.emaillist_catid
order by 3;


cursor c20 is
select
 hdr.oracle_sid,
 hdr.site_location_id,
 decode(lower(hdr.prim_location_flag),'y','*'||hdr.site_location_name,hdr.site_location_name) site_location_name,
 hdr.site_location_abbr,
 hdr.prim_location_flag,
 hdr.address_line_01,
 hdr.address_line_02,
 hdr.city_name,
 hdr.state_province_id,
 hdr.state_province_name,
 hdr.country_name,
 hdr.postal_code_id,
 hdr.phone_number_id
from
 oramon.omon_dbsupport_hdr hdr
where
 (((hdr.oracle_sid) = (select db.name from sys.v_$database db))
 and ((trunc(effect_datetime)) <= trunc(sysdate))
 and ((hdr.term_datetime) is null))
order by 3;


/*****************************************************************************
Beginning the trigger ....
*****************************************************************************/

begin

select distinct
 lower(nvl(rtrim(ss.program,chr(0)), 'bg-process')) appltool_id,
 lower(trim(ss.module)||' '||trim(ss.action)) appltool_vers,
 db.oracle_sid,
 lower(substr(ss.osuser,1,60)) osacct_id,
 lower(rtrim(ss.machine, chr(0))) machine_id,
 lower(substr(nvl(ss.username,bg.name),1,60)) dbuser_id,
 lower(ss.terminal) terminal_id,
 ss.process process_id,
 ss.sid||','||ss.serial# session_id,
 lower(ss.status) status_id,
 lower(bg.name) bgname_id,
 sysdate conn_tms
into
 vApplTool,
 vApplVers,
 vOraSid,
 vOSAcct,
 vMachID,
 vDBUser,
 vTermID,
 vProcID,
 vSessID,
 vStatID,
 vBGName,
 vConTms
from
 sys.v_$session ss,
 sys.v_$bgprocess bg,
 sys.v_$process pr,
 (select upper(db.name) oracle_sid from sys.v_$database db) db
where
 ((ss.paddr = pr.addr)
 and (bg.paddr(+) = pr.addr)
 and rownum <=1
 and ((ss.sid) = (select distinct sid from sys.v_$mystat where rownum = 1)));


select
 count(1) into vRecCount01
from
 oramon.omon_toolpriv_hdr hdr
where
 (((trunc(hdr.effect_datetime)) <= trunc(sysdate))
 and ((hdr.term_datetime) is null)
 and ((upper(hdr.appltool_flag)) = 'A'))
 and ((upper(hdr.appltool_id))
 in ((upper(vApplTool))));


select
 count(1) into vRecCount02
from
 oramon.omon_toolpriv_aud aud;



/*****************************************************************************
Start the validation process ....
*****************************************************************************/

if vRecCount01 = 0 then


select
 count(1) into vRecCount03
from
 oramon.omon_toolpriv_hdr hdr,
 oramon.omon_toolpriv_dtl dtl
where
 ((hdr.hdr_toolpriv_num = dtl.hdr_toolpriv_num)
 and ((upper(vOSAcct)) = (upper(dtl.osacct_id)))
 and ((vApplTool) = (hdr.appltool_id))
 and (upper(hdr.appltool_flag) = 'C')
 and (((trunc(dtl.effect_datetime)) <= trunc(sysdate))
 and ((dtl.term_datetime) is null)));


if vRecCount03 = 0 then
  vKillFlag := 'Y';
  vActionTaken := 'Terminated User''s Session';
else
  vKillFlag := 'N';
  vActionTaken := 'Session Validated and is Allowed';
end if;


/*****************************************************************************
Insert the session information to the audit table ....
*****************************************************************************/

insert into
 oramon.omon_toolpriv_aud
(select distinct
  decode(vRecCount02, 0, 100, num.seqnum),
  vApplTool,
  vApplVers,
  vOraSid,
  vOSAcct,
  vMachID,
  vDBUser,
  vTermID,
  vProcID,
  vSessID,
  vStatID,
  vBGName,
  vActionTaken,
  vConTms
 from
  dual,
  (select max(aud.aud_toolpriv_num) + 1 seqnum from oramon.omon_toolpriv_aud aud) num);

commit;


/*****************************************************************************
Prepare and send the email notification to the receipients ....
*****************************************************************************/

conn:= utl_smtp.open_connection(emailserver, port);
utl_smtp.helo(conn, emailserver);

for c1rec in c1 loop
if upper(c1rec.emaillist_catid) = 'SENDER' then
  utl_smtp.mail(conn,c1rec.email_address_id);
  vSenderEmail := c1rec.email_address_id;
else
  utl_smtp.rcpt(conn,c1rec.email_address_id);
end if;

if upper(c1rec.emaillist_catid) = 'TO' then
  vTolist := vToList || c1rec.email_address_id || ';';
end if;

if upper(c1rec.emaillist_catid) = 'CC' then
  vCclist := vCcList || c1rec.email_address_id || ';';
end if;

if upper(c1rec.emaillist_catid) = 'BCC' then
  vBcclist := vBccList || c1rec.email_address_id || ';';
end if;
end loop;


for c20rec in c20 loop
if upper(c20rec.prim_location_flag) = 'Y' then
  vSubjectLine := 'Oracle database tool ''' || vApplTool || ''' connection has been made on ' || c20rec.site_location_abbr || ' - [' || c20rec.site_location_id || ']';
end if;
end loop;

msg:=
'Date: ' || to_char(new_time(sysdate,'GMT','GMT'), 'mm/dd/yyyy hh24:mi') || crlf ||
'From: ' || vSenderEmail || crlf ||
'Subject: ' || vSubjectLine || crlf ||
'To: ' || VToList || crlf ||
'Cc: ' || VCcList || crlf ||
'Bcc: ' || VBccList || crlf || vSubjectLine || crlf || crlf;


msg := msg || crlf || crlf;
msg := msg || 'C O N N E C T I O N   I N F O R M A T I O N: ' || crlf ||
'----------------------------------------------------------' || crlf;

msg := msg || 'Tool Version : ' || vApplTool || crlf ||
'User Name : ' || vDBUser || crlf ||
'OS Name : ' || vOSAcct || crlf ||
'Machine Name : ' || vMachID || crlf ||
'Terminal Name : ' || vTermID || crlf ||
'Session ID : ' || vSessID || crlf ||
'Action Taken : ' || vActionTaken || crlf ||
'Connection Time : ' || rpad(to_char(vConTms,'yyyy/mm/dd hh24:mi:ss am'),28,' ') || crlf || crlf;


msg := msg || crlf || crlf;
msg := msg ||'S I T E L O C A T I O N   I N F O R M A T I O N:' || crlf ||
'----------------------------------------------------------' ||crlf;
for c20rec in c20 loop
msg := msg || c20rec.site_location_name || crlf
  || c20rec.address_line_01 || crlf
  || c20rec.city_name || ', ' || c20rec.state_province_name || ' ' || c20rec.postal_code_id || crlf
  || c20rec.phone_number_id || crlf || crlf;
end loop;


utl_smtp.data(conn, msg);
utl_smtp.quit(conn);


/*****************************************************************************
Write the message to the Oracle Alertlog file ....
*****************************************************************************/


sys.dbms_system.ksdwrt(2, crlf );
sys.dbms_system.ksdwrt(2, '+--------------------------------------------+' );
sys.dbms_system.ksdwrt(2, ' ' || to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss') );
sys.dbms_system.ksdwrt(2, ' ' || vSecMessage );
sys.dbms_system.ksdwrt(2, ' OS User Name : ' || vOSAcct );
sys.dbms_system.ksdwrt(2, ' DB User Name : ' || vDBUser );
sys.dbms_system.ksdwrt(2, ' Machine ID ..: ' || vMachID );
sys.dbms_system.ksdwrt(2, ' Programme: ..: ' || vApplTool );
sys.dbms_system.ksdwrt(2, ' Action Taken : ' || vActionTaken );
sys.dbms_system.ksdwrt(2, '+--------------------------------------------+' );
sys.dbms_system.ksdwrt(2, crlf );


/*****************************************************************************
Terminate the offending session, if applicable ....
*****************************************************************************/

if vKillFlag = 'Y' then
  raise_application_error(-20001, vSecMessage || ', ' || vOSAcct || ', ' || vTermID);
end if;

end if;
end;
/

show errors