How to Password-Protect Directories with mod_authn_dbd and MySQL on Apache (Debian 8)

This tutorial exists for these OS versions

On this page

  1. 1 Preliminary Note
  2. 2 Installing MySQL or MariaDB
  3. 3 Configuring mod_authn_dbd
  4. 4 Links

This guide explains how to password-protect web directories (with users from a MySQL database) with mod_authn_dbd on Apache2 on a Debian 8 (Jessie) server. It is an alternative to the plain-text password files provided by mod_auth and allows you to use normal SQL syntax to create/modify delete users. You can also configure mod_authn_dbd to authenticate against an existing MySQL user table. The apache mod_authn_dbd is a replacement for mod_auth_mysql.

 

1 Preliminary Note

I use the vhost http://www.example.com here with the vhost configuration file /etc/apache2/sites-available/example.com.vhost and the document root /var/www/www.example.com/web. I want to password-protect the directory /var/www/example.com/web/protecteddir in this tutorial (translates to http://www.example.com/protecteddir/).

You can use this tutorial for the basic LAMP server if you do not have Apache installed yet.

 

2 Installing MySQL or MariaDB

I will use MariaDB, a MySQL fork here instead of MySQL. But MySQL works as well if you prefer that. To install MariaDB, we run:

apt-get -y install mariadb-server mariadb-client

You will be asked to provide a password for the MySQL root user:

New password for the MariaDB "root" user: <-- yourrootsqlpassword
Repeat password for the MariaDB "root" user: <-- yourrootsqlpassword

Install the DBD MySQL module:

 apt-get install libaprutil1-dbd-mysql

Afterwards, enable the mod_authn_dbd module:

a2enmod dbd
a2enmod authn_dbd
authn_socache

Restart Apache:

service apache2 restart

 

3 Configuring mod_authn_dbd

You can find the documentation for mod_authn_dbd in the Apache documentation here http://httpd.apache.org/docs/current/mod/mod_authn_dbd.html.

Having read these two files, we create a MySQL database called examplecomdb in which we will create the table mysql_auth which will contain our users and passwords. In addition to that we create the MySQL user examplecom_admin - this user will be used by mod_auth_mysql to connect to MySQL later on:

mysqladmin -u root -p create examplecomdb
mysql -u root -p
GRANT SELECT, INSERT, UPDATE, DELETE ON examplecomdb.* TO 'examplecom_admin'@'localhost' IDENTIFIED BY 'examplecom_admin_password';
GRANT SELECT, INSERT, UPDATE, DELETE ON examplecomdb.* TO 'examplecom_admin'@'localhost.localdomain' IDENTIFIED BY 'examplecom_admin_password';
FLUSH PRIVILEGES;

(Replace examplecom_admin_password with a password of your choice.)

USE examplecomdb;
create table mysql_auth (
username varchar(255) not null,
passwd varchar(255),
groups varchar(255),
primary key (username)
);

(Of course, you can as well use existing tables holding your user credentials, and you can as well have additional fields in the table, such as a field that defines if a user is active or not, for example.)

Now we insert the user test into our mysql_auth table with the password test; this user belongs to the group testgroup.

The password has to be hashed, I will use an SHA1 hash here, the hash can be created with the htpasswd command on the Linux shell:

htpasswd -bns test  test

The result is this:

test:{SHA}qUqP5cyxm6YcTAhz05Hph5gvu9M=

The first part is the username "test", separated by ":" and then comes the hashed password. We need the hashed password "{SHA}qUqP5cyxm6YcTAhz05Hph5gvu9M=" only to insert it into our user database. The MySQL query is this:

INSERT INTO `mysql_auth` (`username`, `passwd`, `groups`) VALUES('test', '{SHA}qUqP5cyxm6YcTAhz05Hph5gvu9M=', 'testgroup');

Then we leave the MySQL shell:

quit

The configuration for mod_authn_dbd as to be included into the vhost file, it may not be added inside a .htaccess file. Therefore we edit vhost file and add the following configuration at the end of the file:

nano /etc/apache2/sites-available/example.com.vhost
[...]
 # mod_dbd configuration
DBDriver mysql
DBDParams "dbname=examplecomdb user=examplecom_admin pass=examplecom_admin_password"

DBDMin 4
DBDKeep 8
DBDMax 20
DBDExptime 300

<Directory "/var/www/example.com/web/protecteddir">
# mod_authn_core and mod_auth_basic configuration
# for mod_authn_dbd
AuthType Basic
AuthName "My Server"

# To cache credentials, put socache ahead of dbd here
AuthBasicProvider socache dbd

# Also required for caching: tell the cache to cache dbd lookups!
AuthnCacheProvideFor dbd
AuthnCacheContext my-server

# mod_authz_core configuration
Require valid-user

# mod_authn_dbd SQL query to authenticate a user
AuthDBDUserPWQuery "SELECT passwd FROM mysql_auth WHERE username = %s"
</Directory>

Reload Apache:

service apache2 reload

If you have additional fields in your MySQL table that define if a user is allowed to log in or not (e.g. a field called active), you can add it to the SQL user query like this:

[...]
AuthDBDUserPWQuery "SELECT passwd FROM mysql_auth WHERE username = %s and active = 'yes'"
[...]

The require valid-user directive makes that each user listed in the mysql_auth table can log in as long as he/she provides the correct password. If you only want certain users to be allowed to log in, you'd use something like

[...]
require user jane joe
[...]

instead. And if you only want members of certain groups to be allowed to log in, you'd use something like this:

[...]
require group testgroup
[...]

That's it! Now try to access http://www.example.com/protecteddir/, and you should be asked for a username and password:

Enter the username and password.

Login succeeded.

Share this page:

5 Comment(s)