Database Security | Programming Microsoft Visual InterDev 6.0

Web applications that provide access to data in a database typically have additional security requirements. Visual InterDev and Active Server Pages (ASP) provide certain features by default, as well as other options to enhance secure access to your application.

Visual InterDev uses two different types of database logins: design-time (that is, how Visual InterDev itself logs into the database) and run-time (how users log from the browser to the Microsoft ActiveX Data Objects [ADO] model).

You can set run-time logins to a default by using the Data Connection Properties sheet (on the Authentication tab). You can access the Data Connection Properties sheet by expanding the global.asa node within the Project Explorer, right-clicking the appropriate data connection, and then choosing Properties from the context menu. This login information, including the password, is cached as text in global.asa. If you use this option, database logins are protected from those browsing the site, but other site authors can see global.asa. This is fine for most situations because only authors can see the password, but some authors might not want any information cached in global.asa. Instead, they'll want a login screen that forces users to use their own user names or passwords for validation.

NOTE
If someone tries to access the global.asa file from their Web browser, they will get the following error message:

HTTP/1.1 Requests for GLOBAL.ASA Not Allowed

This prevents people from attempting to read any username or password information that is stored in a global.asa file.

Database security starts with Web security. Before users can access a database over the Web, they must access the page that provides the database interface. If the page does not allow the user access, the user cannot get to the database.

The pages stored on a FrontPage Server Extensions site are subject to all the security of that site. After following the steps for setting browse security, you can extend security by adding a group for database users. A convenient and descriptive name for this group is Web Database Users. This group is then granted access to the pages that provide access to the database. For example, using FrontPage permissions, these pages can all be located in a separate site in which only the Web Database Users have browse privileges.

You can also use NTFS ACLs to restrict access to individual files or folders in a site. Using this technique, you must selectively assign permissions to all of the ASP files and any other files that provide database access.

Another approach is to use a custom login page to authenticate users. To do this, use the global.asa file and the Session_OnStart event to detect the start of a session, and then redirect the user to a login page. The following code does this:

 <SCRIPT LANGUAGE=VBScript RUNAT=Server> Sub Session_OnStart     Response.Redirect "Login.asp" End Sub </SCRIPT>

The key line in the global.asa file is Response.Redirect "Login.asp". This command redirects the user to the login.asp page for validation. The code in login.asp is as follows:

 <%@ Language=VBScript %> <HTML> <HEAD> <META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0"> <TITLE>VI-Bank - Login</TITLE> <LINK REL="stylesheet" TYPE="text/css"      HREF="_Themes/blueprnt/THEME.CSS" VI6.0THEME="Blueprint"> <LINK REL="stylesheet" TYPE="text/css"      HREF="_Themes/blueprnt/GRAPH0.CSS" VI6.0THEME="Blueprint"> <LINK REL="stylesheet" TYPE="text/css"      HREF="_Themes/blueprnt/COLOR0.CSS" VI6.0THEME="Blueprint"> <LINK REL="stylesheet" TYPE="text/css"      HREF="_Themes/blueprnt/CUSTOM.CSS" VI6.0THEME="Blueprint"> </HEAD> <BODY> <TABLE> <TR valign=top> <TD width=125> <!--#INCLUDE FILE="menu.htm"--> </TD> <TD> <H2><FONT COLOR="navy"><I>VI-Bank - Login</I></FONT></H2> <HR style="COLOR: navy"> <P> <FORM METHOD="POST" ACTION="ValidateLogin.asp"> <TABLE> <TR> <TD>Username</TD> <TD><INPUT TYPE="text" SIZE="20" NAME="username"></TD> </TR> <TR> <TD>Password</TD> <TD><INPUT TYPE="password" SIZE="20" NAME="password"></TD> </TR> </TABLE> <P> <INPUT TYPE="Submit" VALUE="Submit" NAME="Submit"> <INPUT TYPE="Reset" VALUE="Reset" NAME="Reset"> </FORM> </TD> </TR> </TABLE> </BODY> </HTML>

The login page accepts the username and password from the user via the appropriate form fields. When the user clicks the Submit button, control is transferred to ValidateLogin.asp. This sample code can be found under the Chap22 folder on the CD-ROM. The database used to validate the login is the VI-Bank Access database found under the VI-Bank folder. Here is the code for the ValidateLogin.asp page:

 <%@ Language=VBScript %> <% Response.Buffer = True %> <% ' VI 6.0 Scripting Object Model Enabled %> <!--#include file="_ScriptLibrary/pm.asp"--> <% if StartPageProcessing() Then Response.End() %> <FORM name=thisForm METHOD=post> <HTML> <HEAD> <META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0"> <LINK REL="stylesheet" TYPE="text/css"      HREF="_Themes/blueprnt/THEME.CSS" VI6.0THEME="Blueprint"> <LINK REL="stylesheet" TYPE="text/css"      HREF="_Themes/blueprnt/GRAPH0.CSS" VI6.0THEME="Blueprint"> <LINK REL="stylesheet" TYPE="text/css"      HREF="_Themes/blueprnt/COLOR0.CSS" VI6.0THEME="Blueprint"> <LINK REL="stylesheet" TYPE="text/css"      HREF="_Themes/blueprnt/CUSTOM.CSS" VI6.0THEME="Blueprint"> </HEAD> <BODY> <!--METADATA TYPE="DesignerControl" startspan … <Recordset Design-Time Control> … <!--METADATA TYPE="DesignerControl" endspan--> <% ' Get the user-entered username and password username = Request.Form("username") password = Request.Form("password") ' Set the SQL String sqlstring = "select ssn, userid, password from login where " + _     "userid = '" + username + "'" rsLogin.setSQLText(sqlstring) ' Open the recordset rsLogin.open() If rsLogin.EOF Then     Response.Redirect("invalidusername.asp") Else     If Trim(rsLogin.fields.getValue("password")) = Trim(password) Then         Session("ssn") = rsLogin.fields.getValue("ssn")         Response.Redirect("main.asp")     Else         Response.Redirect("invalidpassword.asp")     End If End If ' Close the recordset rsLogin.close() %> </BODY> <% ' VI 6.0 Scripting Object Model Enabled %> <% EndPageProcessing() %> </FORM> </HTML>

This file tries to validate the username and password. To retrieve the user name and password from the login.asp file, use the Request object to access the Form collection that contains the variables. The username and password are then stored in variables and the Recordset DTC is used to look up the username in the Login table in the database. If the user name is found and the password matches the password field in the database, the user can access the site via the main.asp file. If the user name is not found or the password does not match, the user cannot access the site and the appropriate error message Web page is displayed to the user. These error-message Web pages are named InvalidUsername.asp and InvalidPassword.asp.

The database used in the previous example consists of one table named Login. This table has three text fields: SSN, Userid, and Password. You can use a separate database for a table such as Login, or you can add the table to an existing database. You can quickly extend this example by adding another table or two to build in group functionality. This way you can separate customers into different classifications or use the functionality to provide similar access to several users.

After you check the user's name and password, you can set a session variable to indicate that user's security level to future pages. In this case, you can take the username and set it in a Session variable with the same name. Then you can create a simple routine that checks this security level variable and either lets the user browse or execute the page, or redirects the user to a page indicating that access has been denied. The most effective way to do this is to write this routine in an include (.inc) file and simply include it at the top of the pages you want to protect.

You can also set the ADO connection string information (stored in the Session object). The passwords and user IDs passed in via the login page are then used for those users when they connect to databases using ADO throughout the site. This is convenient because the ID and password are stored in session variables to begin with. In this way, you can effectively grant different database access levels to different users—introducing another layer of security using the DBMS system. However, keep in mind that the more fine-grained the control, the more difficult it is to administer the site. In many cases, the best way to protect databases is to ensure that the access to database pages is appropriately restricted using any of the techniques we described.

Also, an obvious problem with using a login page is that the user name and password are passed as plain text in the HTTP stream. To protect this information, use SSL encryption.

NOTE
The example above is just one technique for applying database security to your Web application. In general, when adding security to your applications, you should aim to minimize the number of areas where you need to maintain security information. Often developers tie the security back to the Windows NT security model. In other cases, developers tie the security back to the underlying database such as Oracle or Microsoft SQL Server. In this case, instead of maintaining a custom login table within your database, you would have the user enter an Oracle or SQL Server username and password. You then attempt to connect the user to the database. If the connection succeeds, the user has entered a valid login. If the connection fails, the user is not authorized to access your application and data.

Another technique is to always minimize the number of areas where you hard-code login information. Areas to watch out for include the global.asa file and any File DSNs. If you use a File DSN, you can always edit it with a text editor and remove any user name and password information after it has been created. That way, if the file is compromised, no user access information is given away.