Apache2 with MySQL authentication

Here are the notes I compiled while getting MySQL authentication to work with
Apache2 on Ubuntu 8.04.  I assume they will work fairly generically for most
distributions.  This also assumes you have installed the necessary packages.

First we will cover the MySQL configuration and then go over how to get Apache
to use it.

*** MySQL configuration ***
Create a database and tables that will be used to authenticate users against.

mysql -u root -p

CREATE DATABASE `apache_auth`;
GRANT SELECT ON `apache_auth`.* TO apache_auth@localhost IDENTIFIED BY 'db_password';
FLUSH PRIVILEGES;
USE `apache_auth`;
CREATE TABLE `web_users` (
	`username` varchar(25) NOT NULL default '',
	`passwd` varchar(40) NOT NULL default '',
	PRIMARY KEY (`username`)
	) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Holds users authorized to log into the website';
CREATE TABLE `web_groups` (
	`username` varchar(25) NOT NULL default '',
	`groups` varchar(25) NOT NULL default '',
	KEY `groups` (`groups`)
	) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Holds the groups to which users belong';
ALTER TABLE `web_groups` ADD CONSTRAINT `userchange`
	FOREIGN KEY (`username`) REFERENCES `web_users` (`username`)
	ON UPDATE CASCADE ON DELETE CASCADE;
INSERT INTO `web_users` (`username`, `passwd`) VALUES ('sally', SHA1('password-for-sally'));
INSERT INTO `web_groups` (`username`, `groups`) VALUES ('sally', 'webadmins');

Notes:
Obviously, you can choose a different database name besides "apache_auth" and
set the password.  The same goes for the table and field names.

Why do we use the InnoDB rather than MyISAM?  Only for one reason: the
referential integrity (look at the ALTER TABLE line).  Let's say that you want
to update Sally's username, from 'sally' to 'sally.baker'.  With the
constraint, all you have to do is update the username in the web_users table,
and it will automatically update the web_groups table for you.  Similarly, if
you delete 'sally', then any references to that username in the web_groups
table will be deleted as well.  It justs lets the database do the housekeeping
for you.  Also, you cannot put a user that does not exist into the web_groups
table.

When we inserted the same user, you'll notice that we hashed the password with
SHA1().  This isn't necessary, but probably good practice.  You could also use
MD5(), PASSWORD(), or no function (plain text) depending on your needs.

You'll see how they fit into the Apache configuration next.

*** Apache configuration ***

We need to get the following line into the Apache configuration:
LoadModule auth_mysql_module /usr/lib/apache2/modules/mod_auth_mysql.so
In order to do this on Ubuntu, you should simply do this:
ln -s /etc/apache2/mods-available/auth_mysql.load /etc/apache2/mods-enabled/auth_mysql.load

You'll then want to add the line:
Auth_MySQL_Info localhost apache_auth db_password
With Ubuntu, create /etc/apache2/mods-available/auth_mysql.conf and put the
above line into the file. (Don't forget to put a symbolic link to the
mods-enabled directory.) You'll then want to set the permissions of the file
so that not everyone can read the file, but the webserver can, i.e:
-rw-r----- 1 root www-data 51 2008-06-13 13:40 auth_mysql.conf

At this point, you'll want to modify the specific Directory directive in your
Apache configuration that you want to use MySQL, so it looks somewhat like the
following:

<Directory /var/www/info/>
	AuthName "Restricted Directory Access"
	AuthType Basic
	AuthUserFile /dev/null
	AuthBasicAuthoritative off

	Auth_MySQL on
	Auth_MySQL_Authoritative on
	Auth_MySQL_DB apache_auth
	Auth_MySQL_Password_Table web_users
	Auth_MySQL_Username_Field username
	Auth_MySQL_Password_Field passwd
	Auth_MySQL_Group_Table web_groups
	Auth_MySQL_Group_Field groups
	Auth_MySQL_Empty_Passwords off
	Auth_MySQL_Encryption_Types SHA1Sum

	require group webadmins
</Directory>

You should be good to go after a reload of the Apache configuration.

Notes:
The AuthUserFile and AuthBasicAuthoritative lines may not be needed with every
distribution.  Ubuntu seems to need them though in order to work.

You can obviously change the DB, Table, and Field lines as needed to match your
setup (even to an existing table of users).  The fields actually default to the
values given above, so I didn't need to put them.  However, I did so that it
would be easy to see what needed to be changed if needed.

Auth_MySQL_Encryption_Types can be: Plaintext, Crypt_DES, Crypt_MD5, Crypt,
PHP_MD5, SHA1Sum, MySQL.  (MySQL is the PASSWORD function in MySQL, PHP_MD5 is
the MD5 function in MySQL and in PHP, SHA1Sum is used in the examples above,
and Plaintext is pretty self-explanatory.)

You can require multiple groups, just like in the rest of Apache, or a
"require valid-user" line works as well.  It should be the same as what you are
used to with your Apache configuration.  You can add more directives as needed.

------

I hope this helps someone.  Let me know if there are errors above.

-W Gillespie (wgillespie, es2eng.com)

Last updated: 6/25/2008