Restricting Access to Database Operations

There are times when access to a particular database or operation on a database should be restricted to certain users. The NCSA httpd server has built-in user authentication features that can be easily adapted to work with an online database. The discussion below is borrowed heavily from the Mosaic User Authentication tutorial , which goes into a less-detailed but more generally applicable discussion of the topic.

What to Protect

The server authentication scheme is intended to manage access to file system objects. Access can be granted to certain people or sites or certain sites can be refused access.

In this example, we have a CGI script we want to control access to because it contains the database interface routines. Access control can be established in the directory where the CGI script lives and only selected users or sites will be able to interact with our database.

If all of our interface functions are contained in a single executable then protecting certain interface functions from unauthorized access is a little trickier. Briefly, you can do this by creating a symbolic link to the CGI executable in a protected directory and calling the protected version whenever a controlled procedure is desired. More details on this below.

The Access Control File

In the directory where the protected script exists, create a file called .htaccess. In the file, put the following:

AuthUserFile /home/beowulf/public_html/.htpasswd
AuthGroupFile /dev/null
AuthName Test
AuthType Basic

<Limit GET>
require user pumpkin
</Limit>

Choose an appropriate name and directory for AuthUserFile, which will be created shortly. It should not be in the protected directory, as it will contain all the usernames and passwords for access to that directory.

The AuthGroupFile can be used to set up access for specified groups, which are defined in a "group file". This is detailed below.

The AuthName above is the name of the form that will appear on the dialog box requesting a password.

The Password File

NCSA httpd comes with a program called htpasswd. To create the password file and add a user to it, run

htpasswd /home/beowulf/public_html/.htpasswd pumpkin

replacing the path for the password file to the path appropriate for your system. The file will be created and the username "pumpkin" added to it. You will be prompted twice for "pumpkin's" password.

There is absolutely no connection between users in this file and users on your local host. Users with local accounts can use the same (or different) names in your local password file, with same (or different) passwords. External users can have usernames in this file without having an account on your system. This method only controls access to the files in the desired directory.

That's all there is to it. Any user attempting to access a file in the protected directory will be prompted for a password by their client browser, which will pass it back to your server for verification. If they are in the password file, access will be allowed.

Special Cases

Group Access

What if you want to give access to a group of people? Do the following:

Add the users to the password file, using htpasswd, as above.

Create a group file containing the names of the users to be allowed access in the same directory as the password file (not required, but not a bad idea). You might call it .htgroups for consistency. It should look like this:
```
add-access: pumpkin peanuts almonds walnuts
```

Modify the .htaccess file as follows:

AuthUserFile /home/beowulf/public_html/.htpasswd
AuthGroupFile /home/beowulf/public_html/.htgroups
AuthName AddForm
AuthType Basic

<Limit GET>
require group add-access
</Limit>

Add more groups as necessary. Don't forget to add the users in the group file to the .htpasswd file - they'll need passwords.

Controlling Specific Files

Some fancy footwork here. You can't limit access to a specific file, only to a specific directory. If you want to limit access to a specific file, set up a "protected directory", as above, with nothing in it. Then put the file you wish to limit access to into that directory.

You can get fancier if you're running on a Unix-variant system. In this example, we have a database to which we wish to restrict the ability of users to search for records. One script, called wdb.cgi (see elsewhere in this tutorial for more information on WDB) contains all the operations on the database - query, add, update and delete. How can we control access to just one of the functions when all of them are contained in the same script? Instead of breaking the program up into pieces, try this:

Create an empty directory and establish protections for it. In this case, the .htaccess file looks like this:

AuthUserFile /a/ump/csc/home/beowulf/public_html/.htpasswd
AuthGroupFile /dev/null
AuthName DBAccess
AuthType Basic

<Limit GET>
require user pumpkin
</Limit>

In this directory, create a symbolic link to the program. You could copy the file, but why waste disk space? The only files in this directory are the .htaccess file and the symlink.

Lastly, you need to have the calling program point to the symlink, not the original copy. Access to the symlink will be protected, but access to the original will be unlimited. My sample script looks like this:

<FORM ACTION="http://cscsun1.larc.nasa.gov/~beowulf/db/secure/wdb.cgi/cfoobar/restaurant_join/query_form">
<INPUT TYPE="submit" VALUE="Search for a record in the database">

That's it. Give it a try (user pumpkin, password pie):

However, Matt Healy brought up a good point. By playing with the URL information, a clever hacker might be able to access the protected function by calling the unprotected version of the script. This could totally defeat your security precautions.

To eliminate this possibility, Matt checks the SCRIPT_NAME environment variable to see which script has been called. If the script was called using the secure version, the secure functions are available. If not, you can't use the protected functions.

For example, look at the following Perl code:

if ( $ENV{'SCRIPT_NAME'}  =~  [Regexp] ) {
# They came in via the private path
      $MyScriptUrl = [secure link];
      $form{'TABLE'} = 'Private_Sequences';
      $form{'TITLE'} = 'All Records';
      #Note: You are now accessing the Secure Version ...

} else {
# They came in via the public path
      $MyScriptUrl = [public link];
      $form{'TABLE'} = 'Public_Sequences';
      $form{'TITLE'} = 'Public Records';
      #Note: You are now accessing the Public Version ...
}

This prevents hackers from calling the restricted function in the unprotected script. If the script was not called from the secure path, the protected functions are not available. Period. Thanks for the idea, Matt.

NOTE: Once a user has entered the login name and password, they will have access to all of the files in the protected directory for the entire time they are running the same session of their Web browser. If a user exits the browser, then starts a new session and attempts to access the protected directory, they will have to enter the login name and password again.

Limiting Access to/from Specific Hosts

Minor mod to the .htaccess file. Try this:

AuthUserFile /dev/null
AuthGroupFile /dev/null
AuthName ExampleAllowFromNCSA
AuthType Basic

<Limit GET>
order deny,allow
deny from all
allow from .ncsa.uiuc.edu
</Limit>

You could modify the allow and deny fields as necessary to manage access.

To deny access to an individual user, just delete their line from the .htpasswd file. Obvious, but I thought it should be said.

Thanks to Kevin Marlowe for researching this information and handing it to me in understandable form.