Spider Web Woman Designs
Home What We Do Our Portfolio Expression Web Resources FrontPage Resources Web Site Accessibility Search This Site Contact Us

FrontPage 2000 Database Results Wizard (Security)

Table of Contents
 
FrontPage 2000 Database Results Wizard (Security)
     Introduction
     Authentication
     Validation

Introduction

The FrontPage 2000 Database Results Wizard (DRW) can be used to build complete ASP database applications, including queries and forms which allow data to be added, updated and deleted from the database. You can also secure the application using the DRW by ;
  1. adding a login page to authenticate users and 
  2. validating records in a database or pages that users are authorized to access.

The approach presented here is to create a session variable containing the user's ID (UID) upon successful login. The session variable is then used to control queries, updates and adding records to the database. You can download a sample of this application here.

Authentication

You can use the DRW to authenticate (login) users and create a session variable containing the user's ID (UID);

  1. Use this SQL statement:
    SELECT * FROM TableName WHERE UserName = ::username:: AND Password = ::password::
     
  2. Add this ASP script in HTML view after the DRW Results Field (<<Username>>): 
    <%Session("uid")=cStr(fp_rs("ID"))%>
     
  3. Change the 'No records returned' message to: 
    Please enter a valid Username and Password.

This is what the DRW component should look like (with the image showing the location of the script in #2 above);

Username:
Password:
This is the start of the Database Results region.

Username: <<Username>>

This is the end of the Database Results region.

Validation

Once the UID Session variable has been established, it can be used to validate access to web pages or records in a database. 

  1. Validated Users Only

    To make sure the user is logged in, you would simply add an ASP script to every page to redirect unauthenticated users to the login page. This would limit access to the page only to validated users.

    <%
    If Session("uid") = "" Then Response.Redirect "login.asp"
    %>
    <html> 

  2. Limit Access to Pages

    You can restrict access to web pages to one user (or a small number of users) using the session variable. For example;

    <%
    If Session("uid") <> "123"  AND Session("uid") <> "456"  Then Response.Redirect "login.asp"
    %>
    <html>
    will limit access to the page to users 123 and 456.

    You can also do this using a database and restricting access to pages by placing the page content inside the DRW and selecting a user authorization record by passing the uid session variable to the query. If the query returns no records (ie the user is not authorized to view the page), the page will not be displayed.

    For example,  if one or several users are authorized to access the same page using a 'UserPages' table, you would use a query such as;

    SELECT * FROM UserPages WHERE PageID = 123 AND UserID = ::uid::

  3. Limit Access to Data

    You can also limit access to records in a database by passing the uid session variable to a SELECT UPDATE or DELETE query or to a FrontPage 'Save to database'. For example;

    Select User Data If only one user is allowed to access specific records in the database, the passed session variable can be used in the Database Results Wizard SELECT sql statement, such as;

    SELECT * FROM TableName WHERE UID = ::uid::

    or if several users are authorized to access the same record using a 'UserRecords' table, you need a more complex sql statement, such as;

    SELECT * FROM TableName WHERE ID=::id:: AND ID IN (SELECT RecordID FROM UserRecords WHERE UID=::uid::);

    Update User Data To update a user's data, the passed session variable can be used in the Database Results Wizard UPDATE sql statement, such as;

    UPDATE TableName SET TextField = 'formfield1', NumericField = ::formfield2::, DateField = #::formfield3::# WHERE ID = ::uid::

    Delete User Data To delete a user's data, the passed session variable can be used in the Database Results Wizard DELETE FROM sql statement, such as;

    DELETE FROM TableName WHERE ID = ::uid::

    Add User Record You can use the passed session variable with the FP 'Save to database' component to add user specific records to a database.

    The session variable is passed in a hidden form field on an ASP form page;
    <input type="hidden" name="uid" value="<%=session("uid")%>">

     
     
  4. Protecting the page from hackers
Without an additional script, an unscrupulous user could hack this procedure by manually adding someone's uid to the url or form field. Adding this script at the top of each page that queries the database using the UID will overcome this problem;

<%
If Session("uid") <> Request("uid") Then Response.Redirect "login.asp"
%>
<html>

This script should be used on any page which is passed the form field or querystring UID such as a database update or a restricted access web page.

© Copyright 2002 Stephen C. Travis, all rights reserved. Republished with the permission of the author.

Back to Stephen Travis' Thingumajig