Setting Up a MySQL Based Website – Part II

by Andrew Chen

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.

Page 2: Setting up Mod_auth_mysql