Last updated at Fri, 08 Dec 2017 22:04:19 GMT

Synopsis

The mod_authn_dbd is an Apache module that provides the functionality for Apache to authenticate users with MySQL database. This module provides authentication front-ends such as mod_auth_digest and mod_auth_basic to authenticate users by looking up users in MySQL tables. Apache’s mod_authn_dbd supports a wide range of drivers such as, ODBC, MSSQL, SyBase, MySQL, Oracle, PostgreSQL and SQLite. This module allows execution of arbitrary SQL for user / password matching and also support alternative authentication mechanisms by offloading the password matching to your database.

This guide explains how to password-protect Apache web directories with mod_authn_dbd on Ubuntu 16.04 server.

System Requirements

  • Ubuntu 16.04 installed on your server.
  • Static IP address 192.168.0.103 setup on your server.

Install LAMP Server

Before starting, basic LAMP server (Apache, MariaDB, PHP) is need to be installed on your server. First, install Apache, PHP and other required packages with the following command:

apt-get install apache2 php7.0 libaprutil1-dbd-mysql -y

Next, you will need to install MariaDB server on your server. But, the latest version of the MariaDB is not available in Ubuntu 16.04 repository.

So, you will need to add the MariaDB repository to the APT. You can do this with the following command:

apt-get install software-properties-common -y

apt-key adv --recv-keys --keyserver hkp://keyserver.ubuntu.com:80 0xF1656F24C74CD1D8 -y

add-apt-repository 'deb [arch=amd64,i386,ppc64el] http://www.ftp.saix.net/DB/mariadb/repo/10.1/ubuntu xenial main' -y

Next, update the repository and install MariaDB server with the following command:

apt-get update -y

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

Next, start Apache and MariaDB service and enable them to start on boot with the following command:

systemctl start apache2

systemctl start mysql

systemctl enable apache2

systemctl enable mysql

Configure Database for mod_authn_dbd

Next, you will need to create a database, user and password for mod_authn_dbd. This will be used by mod_auth_mysql to connect to MySQL later.

First, login to the MariaDB console:

mysql -u root -p

Enter your root password when prompted, then create a database:

MariaDB [(none)]>create database defaultsite_db;

Next, create a user for defaultsite_db database and grant required privileges with the following command:

MariaDB [(none)]>GRANT SELECT, INSERT, UPDATE, DELETE ON defaultsite_db.* TO 'defaultsite_admin'@'localhost' IDENTIFIED BY 'password';

MariaDB [(none)]>GRANT SELECT, INSERT, UPDATE, DELETE ON defaultsite_db.* TO 'defaultsite_admin'@'localhost.localdomain' IDENTIFIED BY 'password';

Next, flush the privileges with the following command:

MariaDB [(none)]>flush privileges;

Next, change the database to the defaultsite_db and create the table for mysql_auth which will contain your users and passwords.

MariaDB [(none)]>use defaultsite_db;

MariaDB [defaultsite_db]> create table mysql_auth ( username varchar(191) not null, passwd varchar(191), groups varchar(191), primary key (username) );

Next, insert the user siteuser into mysql_auth table with the password siteuser.

First, create a hash password for user siteuser using htpasswd command:

htpasswd -bns siteuser siteuser

Output:

 siteuser:{SHA}tk7HEH6Wo7SKT6+3FHCgiGnJ6dA=

Next, insert siteuser into mysql_auth table with the following query:

MariaDB [defaultsite_db]> INSERT INTO `mysql_auth` (`username`, `passwd`, `groups`) VALUES('siteuser', '{SHA}tk7HEH6Wo7SKT6+3FHCgiGnJ6dA=', 'sitegroup');

Finally, exit from the MariaDB console with the following command:

MariaDB [defaultsite_db]>exit;

Configure Apache

First, you will need to enable mod_authn_dbd Apache module. You can do this with the following command:

a2enmod dbd

a2enmod authn_dbd

a2enmod socache_shmcb

a2enmod authn_socache

Next, create a directory inside the Apache web root which you want to protect using mod_authn_dbd:

mkdir /var/www/html/protecteddir

chown -R www-data:www-data /var/www/html/protecteddir

Next, you will need to add configuration for mod_authn_dbd inside Apache default virtual host file. You can do this by editing 000-default.conf file:

nano /etc/apache2/sites-available/000-default.conf

Add the following lines at the end of the file:

 DBDriver mysql 
 DBDParams "dbname=defaultsite_db user=defaultsite_admin pass=password"
 
 DBDMin 4 
 DBDKeep 8 
 DBDMax 20 
 DBDExptime 300
 
 <Directory "/var/www/html/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>

Finally, restart Apache service to apply these changes:

systemctl restart apache2

Now, open your web browser and type the URL http://your-server-ip/protecteddir, you should be asked for a username and password as shown below:

Enter the username as siteuser and password as siteuser, then click on OK button. You will be redirected to the protecteddir page as shown below:

References