Apache AuthMySQL

I had the pleasure of trying to figure out how to use AuthMySQL the other day, as I wanted to password protect my folders (in a simple fashion, since the content wasn’t essential) but wanted to avoid the whole flat-file .htpasswd system.

Lots of searching on Google helped, but it was funny that I found I didn’t have the Mod_Auth_MySQL module in XAMPP. It turns out the later releases of XAMPP removed the module for some reason. (There was a reason for it, but I wasn’t really interested in it, since I just wanted it working on my local machine!).

Getting the XAMPP Module

Getting it was actually pretty simple. You just need to find and download the 1.6.2 version of XAMPP. Make sure you download the correct version based on your operating system (since grabbing the Windows file wouldn’t work on Linux etc…).

It sounds insane downloading the entire thing for just one file, but unfortunately that’s really all you can do (unless you manage to find it elsewhere).

Once you have the mod_auth_mysql.so file, put it in your xampp/apache/modules folder, and then edit the xampp/apache/httpd.conf file.

Down around the file (was line 132 for me) is a line that looks like:

;LoadModule mysql_auth_module modules/mod_auth_mysql.so

The semi-colon at the front is basically a “comment”, disabling the module. Remove it, and then save the file. Restart apache (in Windows you can use the XAMPP manager).

Creating the Database Table

This is MySQL authentication, so we need to create the table. The table is pretty simple, at the most it needs 3 fields, a username, a password and a group.

Here’s a quick create script, but you can obviously make (or use) your own. I would suggest you don’t use plain passwords, rather if you use the MySQL md5 function, apache can work with that.

CREATE TABLE IF NOT EXISTS `auth_user` (
  `id` int(3) NOT NULL AUTO_INCREMENT,
  `username` varchar(25) NOT NULL,
  `password` varchar(32) NOT NULL,
  `usergroup` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
);

Then add a user to the database.

INSERT INTO `auth_user` (`username`, `password`, `usergroup`) VALUE ('username', 'password', 'groupname');

The group field should just be a group name, a simple string. We’re being simple here, but things can get more complicated if you want (such as multiple groups).

Setting Up

You can set up the authentication in your .htaccess file, like anything else.

Edit your file and add the following:

# MySQL Auth
AuthMySQLHost {your database host}
AuthMySQLUser {database username}
AuthMySQLPassword {database password}
AuthMySQLDB {database name}

AuthMySQLUserTable {user table}

AuthMySQLNameField {username field name}
AuthMySQLPasswordField {password field name}
AuthMySQLGroupField {group field name}

AuthMySQLNoPasswd on

AuthMySQLPwEncryption md5
AuthMySQLEnable on

# Standard auth stuff
AuthType Basic
AuthName "{Public name}"

Anything in {brackets} were added by me, and should be replaced (with no brackets).

Finally, on the folders you want to protect, just add the regular directives.

# If you want to allow any valid-user
Require valid-user

# If you want to allow only a specific group(s)
Require group groupname secondgroupname

That’s really it.