Mod_auth_mysql

As part of implementing ETD-db, I needed to password-protect several folders that are part of the ETD-db system. The installation instructions for ETD-db suggest you do this using an .htpasswd file that contains several users and give different users different rights. Reading this I realized that probably one user will serve for several people, unless of course I wanted to create lots and lots of users in my .htpasswd file. I was very happy with this option because it would put a burden on solely me to create and maintain users. So, I started looking for another possible method of authentication for these directories.

First, I considered mod_auth_ldap which allows you to use an LDAP store to authenticate and authorize users. However, I realized that because of the way in which our university is structured it wasn’t likely that all the users would be in a single LDAP store. Sigh. This meant I was going to have to maintain a seperate set of usernames and passwords for ETD-db. The .htpasswd file will allow you to do this but it isn’t very efficient because it needs to be updated on the server and Apache restarted when it is change.

So I started looking at other options. The best one seemed to be mod_auth_mysql. mod_auth_mysql is a module for Apache which allows you to create a database of users in MySQL and utilize this database to authenticate/authorize access to directories on the web server. It has several distinct advantages over using the .htpasswd file. First, because the data is stored in a MySQL database forms can be built to create and update users. This can be done in the programming language of your choice (PHP, Coldfusion, Python, Ruby). Second, mod_auth_mysql will not only authorize by username, it will also authorize by user group. So you can create settings that say everyone with a given group has access to a particular directory. For me this seemed like the ideal solution.

So I installed mod_auth_mysql (in SUSE it is an RPM so this was a snap, but you can compile it from source if you must). Next, I needed to create database in MySQL to hold the information about my users and groups. I named the database etd_reviewers and created two tables user_info and user_group. user_info has two fields user_name and user_passwd. user_group has two fields user_name and user_group. Additionally, you need to create a MySQL user that has SELECT permissions on the user_info and user_group tables. Otherwise mod_auth_mysql will not be able to access the information in the MySQL database. Below is sample syntax for doing this.

grant select on database_name.* to username@localhost identified by (‘password’);

Once you have created the database to hold the user information you need to configure Apache to protect the desired directories.

Below are sample settings to protect a directory. These can be place in an .htaccess file in the directory you want to protect or in your Apache default_settings file.

AllowOverride None
AuthName “ETDdb Users”
AuthType Basic
AuthMySQLHost localhost
AuthMySQLUser mysql_user
AuthMySQLPassword password
AuthMySQLDB database_name
AuthMySQLUserTable user_info
AuthMySQLNameField user_name
AuthMySQLPasswordField user_passwd
AuthMySQLPwEncryption crypt
AuthMySQLGroupTable user_group
AuthMySQLGroupField user_group
AuthMySQLEnable On
require group reviewer

Make sure you restart Apache for these settings to take effect!

Now you can add users to the database to test your settings. Make sure when you add passwords that you use the ENCRYPT function otherwise the passwords won’t properly match. Below is an example of adding a user to user_info and placing that user into the reviewer group.

INSERT into user_info (user_name, user_pass) (‘username’, encrypt(‘password’));
INSERT into user_group (user_name, user_group) (‘username’, ‘group’);

You can add as many users and groups as you need. Mod_auth_mysql is a great alternative method for protecting access to directories on your server. It is more scaleable than using the .htpasswd file, and is relatively easier to install and configure. If you don’t have access to an LDAP server to authenticate your users from, check out this helpful module.