By

In my last article, I talked about using databases for authentication, and I introduced mod_auth_dbm as a possible way to do that.

This week, we'll look at MySQL, a very popular database server, and using mod_auth_mysql to use MySQL to store your authentication information.

A little about MySQL

MySQL is a wonderful database server, which is distributed under the GPL, and is available from http://www.mysql.com/ MySQL is lightweight and fast. It lacks some of the features of larger, more expensive database servers, such as stored procedures, triggers, and various other things, but it contains most of the functionality needed for most small to medium projects. And, it contains some cool stuff like a regular expression language that can be used in SQL statements.

Because MySQL is free, and because it is just such a great database, it is the favorite database in use by folks on *nix operating systems--particularly folks with small budgets. And it also runs on Windows.

mod_auth_mysql

mod_auth_mysql lets you put your usernames and passwords in a MySQL database, and authenticate directly against that. There are a number of advantages to this, in addition to the obvious one of speed of data access. If, for example, you are already storing user information in a database table, it would be irritating to have to store the username and password in another location (the htpasswd file). You would have to maintain the data in two places, and if you let them get out of sync, users would be unable to log in. With mod_auth_mysq, however, you can authenticate directly against the databsae, and keep your authentication information just one place. Usernames and passwords can be updated with a SQL query, with no messing around in text files. And users' group membership can be easily altered.

Installation and Configuration

You can get mod_auth_mysql, and learn more about it, at http://bourbon.netvision.net.il/mod_auth_mysql/

mod_auth_mysql can be compiled as a DSO (Dynamic Shared Object), and then included in the server with a configuration directive. For more details on this, please see earlier articles in which we discuss DSOs.

To configure mod_auth_mysql, you need to tell it what database you want to authenticate against, and what fields in which table contain the relevant information.

The following are the configuration directives that you'll need to know about:

Auth_MySQL_Info [host] [user] [password]

This directive tells where your server is running, and what username and password are necessary to get data from the database. This directive is only necessary if the server is running somewhere other than localhost, or if access is via some user other than the httpd user.

If all of your authentication will be done against the same database, you'll probably want to set the following directive:

Auth_MySQL_General_DB [database_name]

If you'll be authenticating different directories or files against different databases, you can leave this out, and set the database in the various directories.

The following directives can appear either in your httpd.conf configuration file, or in the various directories in .htaccess files. (See Ken Coar's article about .htaccess files for more information.)

Note that you'll be using the usual directives to set up password protection on the directory:

        AuthType Basic
        AuthName "Members Only"
        require group admin

Auth_MySQL_DB [database_name]--Tells which database you are authenticating against.

Auth_MySQL_Password_Table [password_table_name]--Tells which table in that database contains the password information. Unless you specify, it is assumed that the username is contained in the field 'username', and the password is contained in a field 'password'. You can change this. (See below.)

Auth_MySQL_Group_Table [group_table_name]--Ordinarily, you'll probably just want to store the group field in the same table as the usernames and passwords, but if you need to store it in a different table, this is where you'll specify where that is.

Auth_MySQL_Username_Field [username_field_name]--If your username is a field other than 'username', you can specify that with this directive.

Auth_MySQL_Password_Field [password_field_name]--If your password is a field other than 'password', you can specify that with this directive.

Auth_MySQL_Group_Field [group_field_name]--If your group name is a field other than 'groups', you can specify that with this directive.

Auth_MySQL_Encrypted_Passwords on/off--Tells mod_auth_mysql whether the passwords are in the database encrypted, or plain-text. This is on by default - that is, it is assumed that your passwords are stored encrypted.

There are several other directives, but these are the main ones that you will be using most of the time. The following is an example .htaccess file that works for me:

        Auth_MySQL_Info localhost db_user db_password
        Auth_MySQL_DB   authentication
        Auth_Mysql_Password_Table       passwords

        AuthType Basic
        AuthName "Members Only"
        require valid-user

The above assumes that the username is in a field username, and the password is encrypted, and is stored in the field password.

Now What?

Once you have your .htaccess file set up as described above, you will get the password dialog as normal. There will be no difference to the user.

You can maintain your user and password lists via whatever database management tool you are used to using. There's no handy tool like dbmmanage for managing these accounts from the command line, but I'm working on one.

You can use Perl and DBI to talk to your database. In my next column, I'll be talking at greater length about using Perl to manage your password files. There are a plethora of ways to do this, so it really merits its own article.

Summary

mod_auth_mysql allows you to keep your users, passwords, and groups, in a MySQL database. MySQL is a lightweight, fast, free database server which is available for most popular operating systems.

Future columns

Please let me know if there are other topics that you'd like for me to talk about in future columns. You can send your suggestions to me at And please let me know what you think of my columns, at that same address. Many thanks for reading down this far!

--Rich