Setting Up a MySQL Based Website - Part II

By: Andrew Chen
Monday, January 24, 2000 07:49:58 AM EST
URL: http://www.linuxplanet.com/linuxplanet/tutorials/1447/1/

Performing User Authentication via MySQL

In my last article covering MySQL and Web servers, I talked about creating a guest book that would allow our visitors to leave a message for everyone to see. This was all fine and dandy; however, there is more that we can do with a Web site using mySQL and Apache. In this article covering the creation of a mySQL-based Web site, we'll be talking about using mySQL as a user-authentication database.

One of the many popular features of a Web site is to create a "Members Only" section--a section that can be only accessed by authenticated users. Apache includes facilities to do this without the help of an external module, but only with a flat file or a basic database file.

A flat file can be useful when you'll only have a handful of people, and want an easy way to administer the user list. Since the password database will be text (similar to /etc/passwd), opening it in any text editor will allow you to edit user names and passwords. The drawbacks to flat-file databases? With a large number of entries, authentication becomes slower and administration becomes much harder. When using a database file, access times are somewhat quicker, but administration is harder since the file is not text-based.

For large Web sites, a separate database program is necessary to keep track and authenticate users. This is where mySQL comes in. By creating a mySQL table for authenticating users, or using an existing table, you can administer user list with the mySQL database tool and have fast authentication. For this example, we will be using an existing table of users, which we can assume is the list of "members" for this Web site. Our sample table will look something like this:

We created this table in the apache database under the table name of members. We have also created a special mySQL username of apache with a password of authenticate. This account will be used to read in the authentication table when a user accesses a "Members Only" page.

Setting up Mod_auth_mysql

For the purpose of this tutorial, we'll be working with a source distribution of Apache, under Slackware Linux 4.0. For RedHat users, there are RPMs with mod_auth_mysql already compiled in. (Newer versions of Slackware Linux have Apache with mod_auth_mysql already compiled in as well.)

In order to setup mySQL-based authentication under Apache, we need to recompile Apache, along with the mod_auth_mysql module. You can grab your own copy of Apache from http://www.apache.org/dist/. (You can also download a copy of mod_auth_mysql from the author's Web page at http://bourbon.netvision.net.il/mod_auth_mysql/).

After unpacking both archives, change to the unpacked mod_auth_mysql directory and run ./configure --with-apache=../apache_1.3.11. If this continues without incident, you should be all set to run make. This make should be quick, with instructions following. Change to your Apache directory and run ./configure --activate-module=src/modules/auth_mysql/libauth_mysql.a, followed by a make all install. After some puttering, if all went without incident, you should have your very own copy of Apache installed in /usr/local/Apache.

The next step is to configure Apache to query the mySQL database every time we request mySQL-based authentication. Inside /usr/local/Apache/conf, we'll create a file named mysql.conf. We'll keep our global configuration parameters inside here.

##
## mysql.conf - Configuration file for mod_auth_mysql 2.20 for
## Apache 1.3.x
##
## Andrew Chen - aznthinker@iname.com - http://www.linuxplanet.com/

# Configure the target authentication host with the mySQL server
# Syntax: Auth_MySQL_Info host user password
Auth_MySQL_Info localhost apache authenticate

# Specify which database to find the users table
# Syntax: Auth_MySQL_General_DB database
Auth_MySQL_General_DB apache

# Specify the tables for passwords and groups
# Syntax: Auth_MySQL_Password_Table table
Auth_MySQL_Password_Table members
Auth_MySQL_Group_Table members

# Specify the fields for username, password, and group
# Syntax: Auth_MySQL_Username_Field field
Auth_MySQL_Username_Field username
Auth_MySQL_Password_Field passwd
Auth_MySQL_Group_Field groups

# See more options at http://bourbon.netvision.net.il/mod_auth_mysql/

We need to tell Apache to load this extra configuration file at startup time by adding the following line to the end of httpd.conf:

Include mysql.conf .

At this point, feel free to start up Apache the way you typically do. For most source installations, the proper command is /usr/local/apache/bin/apachectl restart.

Protecting a Directory

In the directory you wish to protect, create a .htaccess file with the following lines:

AuthName Administrators Only
AuthType Basic
require group admin

This will cause most browsers to prompt for a user name and password. In this scenario, Apache will query mySQL for authentication information. First, it will verify the user name and password match. If they do, it will check if the user is in the group "admin". If so, the user will be granted access; else, access will be denied with "Authentication Failed." Other configurations are possible with mod_auth_mysql:

AuthName Members and Administrators Only
AuthType Basic
require group user admin

In this scenario, any user in the group "user" or "admin" will be allowed in. You can define as many groups as you want within mySQL by changing the "groups" field:

AuthName Paid Members Only
AuthType Basic
require user bob steve dingo

Here we are limiting it to a list of specific users. This probably won't be used often, since you'll have people like this in a group of their own for easier management.

A Quick Conclusion
The concept of "Members Only" authentication has been around for some time. Even though the examples shown here are very small scale, mySQL is capable of serving Web sites from the smallest to some of the largest around. Possible applications could be allowing employees access to Web mail by authenticating on a company-wide mySQL server, thereby eliminating the need to create a separate user list for Apache. The possibilities are endless.