Apache « mathz.nu

2012/08/09

Apache2 authentication using MySQL backend

Filed under: Apache — Mathz @ 08:35

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 httpauth < create_db.sql

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

3. Creating users

Here we will be using sha1 password. To create a password, you can use the following command:

# echo ‘password’ | sha1sum
c8fed00eb2e87f1cee8e90ebbe870c190ac3848c –

thus, the SHA1 encrypted version of password password is c8fed00eb2e87f1cee8e90ebbe870c190ac3848c.

Now, lets create a user foobar with password ‘password’ and a group it will belong to called ‘foobargroup’.

mysql> USE httpauthdb;
mysql> INSERT INTO users (login, pass, firstname, lastname, email) VALUES ('foobar', 'c8fed00eb2e87f1cee8e90ebbe870c190ac3848c', 'foo', 'bar', 'foobar@example.com');
mysql> INSERT INTO groups (name) VALUES ('foobargroup');
mysql> INSERT INTO usergroup (uid, gid) VALUES (uid, gid);

Where uid and gid have to be replaced with the one created during the 2 first INSERTS.

4. Setting apache

Now, go to your site configuration edit it and add, in between your Directory tags for instance:

    ## mod auth_mysql
    AuthBasicAuthoritative Off
    AuthMYSQL on
    AuthMySQL_Authoritative on
    AuthMySQL_DB httpauthdb
    Auth_MySQL_Host localhost
    Auth_MySQL_User httpauth
    Auth_MySQL_Password httpauthpassword
    AuthMySQL_Password_Table users
    AuthMySQL_Username_Field login
    AuthMySQL_Password_Field pass
    AuthMySQL_Empty_Passwords off
    AuthMySQL_Encryption_Types SHA1Sum
    # Standard auth stuff
    AuthType Basic
    AuthName "restricted zone"

    Require valid-user

Now, after reloading apache you should be able to authenticate as user foobar with password password.

sudo a2enmod auth_mysql

sudo service apache2 restart

Comments (0)