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

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 here with the vhost configuration file /etc/apache2/sites-available/ and the document root /var/www/ I want to password-protect the directory /var/www/ in this tutorial (translates to

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

Restart Apache:

service apache2 restart


3 Configuring mod_authn_dbd

You can find the documentation for mod_authn_dbd in the Apache documentation here

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';

(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:


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:


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/

 # 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/">
# 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"

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, and you should be asked for a username and password:

Enter the username and password.

Login succeeded.

Share this page:

0 Comment(s)

Add comment