Secure Email Notification System

 

Dave Qi

203-940-8316

142 Lockwood Avenue,

Stamford, CT 06902

USA

David.qi@reuters.com

 

Abstract

 

Set up a subscription system that send an alert or reminder as an email to the user who schedules a ticket on a HTML form or to the email address that the user specified in the form.

 

 

Introduction

 

Temprary network users (contractors, outside consultants, temp, part-time employees, etc..), unlike perminant employees , present a network resource permission problem. Namely, when a temp users no longer need to access a shared resources on the network directory, there is no adequate way to timely notify network adinistrator to de-commission the assigned-permissions other than NA’s own dilegence to keep track the permission list and occational often late notes from Human Resource.

 

This problem often present network security problems if not addressed in a timely fathion.

One way to address the problem proactively is to automate a system/procedures so Admin’s can be timely notified through email and de-commison the access to shared network resources.

This project will use the web as the front-end to implement a email scheduling/alerting system. Network Admin can log onto the web and shedule an expiration date for a user to use a shared directories on the server. For example, A system administrator may want to grant a temporary user 4-month permission to use a group shared directory on the file server. He can fill out an on-line ticket using the email notification system and specify sending an alert/notification to the administrator or someone else when four months are over. And he then either take off the access permissions or reschedule an expiration date if needed

 

Background

 

Use a database in a tandem with a website is one of the hottest areas in Web technology. Web pages provide information. What better way could there be to companies than information in a database.

 

The problem is how do you get the information from the database to the we pages. Active Server Pages(ASP) has a database component(ADO) to make this easy. ADO is an object-oriented interface to database servers. With ASP, you use ADODB, which is an implemantation of ADO optimized for OLE DB providers, suh as ODBC.

 

The connnetion-less protocol, HTTP 1.0, is used only to quickly send and receive ASCII based information. Consequently, Web-based application can rely either on continuously updating an application tracking database on the Web server or writing excessive amounts of information to client-site cookies. With ASP, a server-site Session object gives cookie-based browsers a method to track user-specific information across different ASP pages. The user-specific information can be stored and accessed from any page in the ASP pplication by the individual user. The data, accessible only to the specific user that created the information.

 

 

Tools Used

 

 

Platform

 

The development was uildt on Windows NT 4.0.

 

Implementation

 

  1. Installed Internet Information Server 4.0
  2. Designed forms needed for interface
  3. Built a SQL server and created a database for use.
  4. Created a login database user
  5. Designed the needed database tables which should contain all input from filled froms.
  6. Created an ODBC data source on the IIS server to point to the SQL database.
  7. Coded the ASP pages to implement the Session object and database connection object.

 

 

References:

 

 

 

Programming Code

 

  1. login.asp

    2. <%@ LANGUAGE="VBSCRIPT" %>

     

    <HTML>

    <HEAD>

    <META NAME="GENERATOR" Content="Microsoft Visual InterDev 1.0">

    <META HTTP-EQUIV="Content-Type" content="text/html; charset=iso-8859-1">

    <TITLE>Document Title</TITLE>

    </HEAD>

    <BODY bgcolor=blue>

     

    Your SessionID is <% Response.Write(Session.SessionID) %>

    Your Session Timeout is <% = Session.Timeout %>

     

     

    <!-- Insert HTML here -->

    <% IF IsEmpty(Request.Form("login")) THEN

    Response.Write "Please enter your Name"

    End IF

    %>

    <FORM ACTION="access.asp" METHOD=post>

    <CENTER>

    <TABLE BORDER =5>

    <TR>

    <TH>User Name </TH>

    <TD><INPUT NAME="login" TYPE=TEXTBOX MAXLENGTH=20></TD>

    </TR>

     

    <TR>

    <TH>Password </TH>

    <TD><INPUT NAME="password" TYPE=PASSWORD MAXLENGTH=10></TD>

    </TR>

    </TABLE>

    <BR>

    <P><INPUT TYPE="SUBMIT" VALUE="Submit Login"></P>

    </TABLE>

    </CENTER>

    </FORM>

     

    </BODY>

    </HTML>

     

  2. access.asp

    3.  

    <%@ LANGUAGE="VBSCRIPT" %>

     

    <HTML>

    <HEAD>

    <META NAME="GENERATOR" Content="Microsoft Visual InterDev 1.0">

    <META HTTP-EQUIV="Content-Type" content="text/html; charset=iso-8859-1">

    <TITLE>Document Title</TITLE>

    </HEAD>

    <BODY>

     

    <!-- Insert HTML here -->

     

    <% theDate = now

    Response.Write(theDate)%>

    Your SessionID is <% Response.Write(Session.SessionID) %>

    Your Session Timeout is <% = Session.Timeout %>

     

    <% Response.write "Welcome " & Request.Form("login") & "." %>

    <% Response.write "your password is " & Request.Form("password")%>

     

    <% Session("sLogin")=Request.Form("login")

    Session("sPassword")=Request.Form("password")

    %>

     

    <% login1 =Session("sLogin")

    password1=Session("sPassword")

    %>

     

    <% '=login1 %>

    <% '=password1 %>

     

    <%set conn=server.createobject("ADODB.Connection")

    conn.ConnectionTimeout = 45

    conn.CommandTimeOut = 45

    conn.open "DSN=ais", "d_qi", "d_qi" %>

    <%Response.Write(conn.Errors.Count)%>

     

    <%IF conn.Errors.Count >0 THEN

    FOR each error in conn.Errors

    Response.Write("Connection Error " & Error.Number & ":" & Error.Description)

    NEXT

    END IF %>

     

    <% set cmd=Server.CreateObject("ADODB.Command")

    cmd.CommandText="select * from AccessList " & _

    "where users = '"&login1&"' AND password='"&password1&"' "

    'cmd.CommandText = "INSERT INTO dir_permission " & _

    '"(username, server, dir, expiration) " & _

    '" VALUES ('"&sTempUser&"', '"&sServer1&"', '"&sDir1&"', '"&sDate2&"')"

     

    set cmd.ActiveConnection =conn

    %>

     

    <% set rs = Server.CreateObject("ADODB.Recordset")

    'rs.open [Source, ActiveConnection, Cursortype, Lcoktype, Options

    set rs =cmd.Execute %>

     

    <%

    IF rs.EOF THEN

    Response.write("<P><H2>Incorrect login or password <H2></P>")

    %>

    <% ELSE %>

    <P><H2>Welcome to SITG's directory permission Notification page

    or Temporary Employee </H2></P>

    <HR>

     

    <FORM METHOD="POST" ACTION="getinfo.asp" >

    <P>Use <STRONG> Submit</STRONG> to submit your form</P>

     

    <P>Please type in the temporary user login:

    <INPUT TYPE="text" NAME="tempUser">

    </P>

     

    <TABLE BORDER>

    <TR>

    <TH bgcolor=cyan><P>Servers to access (All if apply):<BR></P></TH>

    <TH bgcolor=cyan><P>Directory Permissioned </P></TH>

    <TH bgcolor=cyan><P>Expired On (mm/dd/yy)</P></TH></TR>

    <TR>

    <TD><INPUT TYPE="checkbox" NAME="server1">XSTS02F<BR></TD>

    <TD><INPUT TYPE="text" NAME="dir1"><BR></TD>

    <TD><INPUT TYPE="text" NAME="date1"></TD></TR>

     

    <TR>

    <TD><INPUT TYPE="checkbox" NAME="server2">XSTS05F<BR></TD>

    <TD><INPUT TYPE="text" NAME="dir2"><BR></TD>

    <TD><INPUT TYPE="text" NAME="date2"></TD></TR>

    <TR>

    <TD><INPUT TYPE="checkbox" NAME="server3">XSTS06F<BR></TD>

    <TD><INPUT TYPE="text" NAME="dir3"><BR></TD>

    <TD><INPUT TYPE="text" NAME="date3"></TD></TR>

    </TABLE>

    <P><INPUT TYPE="SUBMIT" VALUE="Submit Query">

    <INPUT TYPE="RESET" VALUE="Clear"></P>

     

     

    </FORM>

    <TABLE BORDER=2>

    <TR>

    <TD>LOGIN</TD>

    <TD>PASSWORD</TD>

    <TD>E-MAIL</TD>

    </TR>

    <% Dim i

    i= 0 %>

     

    <% do while i < 5 AND not rs.EOF

    Response.write("<TR>")

    Response.write("<TD>")

    Response.write(rs("users"))

    Response.write("</TD>")

    Response.write("<TD>")

    Response.write(rs("password"))

    Response.write("</TD>")

    Response.write("<TD>")

    Response.write(rs("emailAddr"))

    Response.write("</TD>")

    Response.write("</TR>")

    rs.moveNext

    i = i+ 1

    loop %>

     

    </TABLE>

    <% END IF %>

     

    <% 'rs_main.AddNew

    'rs_main("username") = "Jim"

    'rs_main("server") = "xsts05f"

    'rs_main("dir") = "project"

    'rs_main("expiration")="5/16/98"

    ' rs_main.update %>

     

    <% set Session("sConn") = conn %>

    <% set Session("sCmd") = cmd %>

    <% set Session("sRs") = rs %>

     

    </BODY>

    </HTML>

     

  3. getinfo.asp

 

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2 Final//EN">

<TITLE>Hello World ASP</TITLE>

<%@ LANGUAGE = VBScript%>

 

<% '=Request.Form %>

 

<% set conn = Session("sConn")

set cmd = Session("sCmd")

set rs = Session("sRs") %>

 

<% Dim Err

err = 0 %>

 

<% TempUser =Request.Form("tempUser")

 

Server1=Request.Form("server1")

Dir1=Request.Form("dir1")

Date1= Request.Form("date1")

 

Server2=Request.Form("server2")

Dir2=Request.Form("dir2")

Date2= Request.Form("date2")

 

Server3=Request.Form("server3")

Dir3=Request.Form("dir3")

Date3= Request.Form("date3") %>

 

<% IF (Server1 = "on") THEN

Server1 = "XSTS02F"

IF (isEmpty(Dir1)) OR (isEmpty(Date1)) THEN

Response.write("<P><H3>One or more required fields is empty.</H3>")

Response.write("<H3>Please re-sumbmit your form.</H3>")

err = err + 1

END IF

ELSE

IF (not isEmpty(Dir1)) OR (not isEmpty(Date1)) THEN

Response.write("<P><H2>You need to check XSTS02F server first.</H2>")

Response.write("<H2>Please re-submit your form. </H2>")

err = err + 1

END IF

END IF

%>

 

<% IF (Server2 = "on") THEN

Server2 = "XSTS05F"

IF (isEmpty(Dir2)) OR (isEmpty(Date2)) THEN

Response.write("<P><H3>One or more required fields is empty.</H3>")

Response.write("<H3>Please re-sumbmit your form.</H3>")

err = err + 1

END IF

ELSE

IF (not isEmpty(Dir2)) OR (not isEmpty(Date2)) THEN

Response.write("<P><H2>You need to check XSTS02F server first.</H2>")

Response.write("<H2>Please re-submit your form. </H2>")

err = err + 1

END IF

END IF

%>

 

<% IF (Server3 = "on") THEN

Server3 = "XSTS06F"

IF (isEmpty(Dir3)) OR (isEmpty(Date3)) THEN

Response.write("<P><H3>One or more required fields is empty.</H3>")

Response.write("<H3>Please re-sumbmit your form.</H3>")

err = err + 1

END IF

ELSE

IF (not isEmpty(Dir3)) OR (not isEmpty(Date3)) THEN

Response.write("<P><H2>You need to check XSTS02F server first.</H2>")

Response.write("<H2>Please re-submit your form. </H2>")

err = err + 1

END IF

END IF

%>

 

<% IF (isDate(Date1)) THEN

Date1 = CDate(Date1)

IF (Date1 < now) THEN

Response.write("The expiration Date is already past")

err = err + 1

END IF

ELSE

Response.write("invalid directory expiration date value entered for XSTS02F server")

err = err + 1

END IF

%>

 

<% IF (err = 0) THEN

IF (Server1 = "XSTS02F") THEN

cmd.CommandText = "INSERT INTO dir_permission " & _

"(username, server, dir, expiration) " & _

" VALUES ('"&TempUser&"', '"&Server1&"', '"&Dir1&"', '"&Date1&"')"

set cmd.ActiveConnection =conn

set rs =cmd.Execute

END IF

IF (Server2 = "XSTS05F") THEN

cmd.CommandText = "INSERT INTO dir_permission " & _

"(username, server, dir, expiration) " & _

" VALUES ('"&TempUser&"', '"&Server2&"', '"&Dir2&"', '"&Date2&"')"

set cmd.ActiveConnection =conn

set rs =cmd.Execute

END IF

IF (Server3 = "XSTS06F") THEN

cmd.CommandText = "INSERT INTO dir_permission " & _

"(username, server, dir, expiration) " & _

" VALUES ('"&TempUser&"', '"&Server3&"', '"&Dir3&"', '"&Date3&"')"

set cmd.ActiveConnection =conn

set rs =cmd.Execute

END IF

END IF %>

 

<% cmd.CommandText="select * from dir_permission "

set cmd.ActiveConnection =conn

set rs =cmd.Execute %>

<H3> You have successfully updated directory permission </H3>

<TABLE BORDER=2>

<TR>

<TD>USERNAME</TD>

<TD>SERVER</TD>

<TD>DIRECTORY</TD>

<TD>EXPIRATION</TD>

</TR>

<% Dim i

i= 0 %>

 

<% do while i < 35 AND not rs.EOF

Response.write("<TR>")

Response.write("<TD>")

Response.write(rs("username"))

Response.write("</TD>")

Response.write("<TD>")

Response.write(rs("server"))

Response.write("</TD>")

Response.write("<TD>")

Response.write(rs("dir"))

Response.write("<TD>")

Response.write(rs("expiration"))

Response.write("</TD>")

Response.write("</TR>")

rs.moveNext

i = i+ 1

loop %>

 

</TABLE>