How-To: Apache2 authentication using MySQL backend

1 minute read

this tutorial will explain how to use a MySQL backend in order to authentication users against your Apache website.

To achieve this we will use Apache2 and its auth_mysql module.

Here, we will assume that you already have a website configured, up and running and that you also have access to a mysql server. The only thing left is to set up authentication.

1. Packages requirements

We need to use a package called libapache2-mod-auth-mysql. If you are using Debian Etch, you will have to compile it yourself. this will not be covered in this tutorial.

On other distros, like ubuntu or debian lenny, simply run:

# apt-get install libapache2-mod-auth-mysql

2. Setting the system

We need to create a Database to host our users and group. Let’s create a user to handle authentication:

# mysql -u root -p

mysql >CREATE DATABASE httpauthdb;
mysql >GRANT USAGE ON *.* TO httpauth@localhost IDENTIFIED BY 'httpauthpassword';
mysql >GRANT ALL PRIVILEGES ON httpauthdb.* TO httpauth@localhost;

then, use the script below and save it as create_db.sql:

CREATE TABLE `groups` (
  `gid` int(10) unsigned NOT NULL auto_increment,
  `name` varchar(50) NOT NULL default '',
  PRIMARY KEY  (`gid`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `usergroup` (
  `uid` int(10) unsigned NOT NULL default '0',
  `gid` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`uid`,`gid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `users` (
  `uid` int(10) unsigned NOT NULL auto_increment,
  `login` varchar(40) NOT NULL default '',
  `pass` varchar(60) NOT NULL default '',
  `firstname` varchar(255) NOT NULL default '',
  `lastname` varchar(255) NOT NULL default '',
  `email` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`uid`),
  UNIQUE KEY `login` (`login`),
  UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

and inject it in your database using the following command:

# mysql -u root -p httpauthdb < create_db.sql

Now, your database is set up, we will need to create some users.