Mod Auth MySQL Under Apache 2 and Debian

Want to support HowtoForge? Become a subscriber!
 

This guide will show you how to use mod_auth_mysql with Apache 2 and Debian.

Firstly if you haven't alrteady done so throw some of the essentials on such as Apache 2 / PHP 4 / MySQL

apt-get install libapache2-mod-php mysql-server php4-mysql libapache2-mod-auth-mysql

Next we need to enable the module, unlike Apache 1, we don't need to modify any configuration files to add sometype of LoadModule statement, simply:

cd /etc/apache2/mods-enabled/

ln -s /etc/apache2/mods-available/auth_mysql.load .

Next we need to set the root password for MySQL

mysqladmin -u root password <password>

Then we need to login into MySQL

mysql -uroot -p

mysql> grant all on auth.* to auth_user@localhost identified by '<password>';

mysql> flush privileges;

mysql> create database auth;

CREATE TABLE `clients` (
`username` varchar(25) NOT NULL default '',
`passwd` varchar(25) NOT NULL default '',
`groups` varchar(25) NOT NULL default '',
PRIMARY KEY (`username`),
KEY `groups` (`groups`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `clients` VALUES ('<username>', '<password>', '<group>');

Changing <username>, <password> and <group> accordingly.

Next we need to modify our Apache 2 configuration.

vi /etc/apache2/apache2.conf

Add the following line to the end of this file

Auth_MySQL_Info localhost <auth_user> <password>

<Directory "<web directory>">
Options +Indexes FollowSymLinks MultiViews
AllowOverride AuthConfig Options FileInfo Limit
Order allow,deny
Allow from all
</Directory>

Adjusting <auth_user>, <password> and <web directory> for the directories you wish to be processed by Apache containing .htaccess files with the relevant directives information.

Restart Apache 2

apache2ctl restart

Finally place an .htaccess file in the directory you wish to protect using mod_auth_mysql. It needs to contain the following structure:

AuthMYSQL on
AuthMySQL_Authoritative on
AuthMySQL_DB auth
AuthMySQL_Password_Table clients
AuthMySQL_Group_Table clients
AuthMySQL_Empty_Passwords off
AuthMySQL_Encryption_Types Plaintext Crypt_DES

AuthName "<description>"
AuthType Basic

<Limit GET POST>
require group <group>
</Limit>

With that file in place it will check the group field in the MySQL and authenicate any users that belong to that particular group, require group can be changed to require a valid user instead if required and multiple groups can be allowed by simply adding a space after the last group to the file looks like:

AuthMYSQL on
AuthMySQL_Authoritative on
AuthMySQL_DB auth
AuthMySQL_Password_Table clients
AuthMySQL_Group_Table clients
AuthMySQL_Empty_Passwords off
AuthMySQL_Encryption_Types Plaintext Crypt_DES

AuthName "<description>"
AuthType Basic

<Limit GET POST>
require group <group1> <group2>
</Limit>


Please do not use the comment function to ask for help! If you need help, please use our forum.
Comments will be published after administrator approval.
Submitted by Anonymous (not registered) on Mon, 2011-08-08 22:55.

Below is not working when i try to authenticate using authmysql (website works fine otherwise)

<VirtualHost *:80>
    ServerName mywebsite.com
    ServerAlias www.mywebsite.com
    ServerAdmin webadmin@mywebsite.com
    ServerSignature Off
    DocumentRoot /var/www/mywebsite.com/www

    <Directory />
        Options +FollowSymLinks
        AllowOverride AuthConfig Options FileInfo Limit
       Order allow,deny
        Allow from all

    # Standard auth stuff
    AuthType Basic
    AuthName "Please authenticate"
    AuthBasicAuthoritative Off
    AuthUserFile /dev/null
    ## mod auth_mysql
    AuthMYSQL on
    AuthMySQL_Authoritative on
    AuthMySQL_DB mywebsitedb
    AuthMySQL_Host localhost
    AuthMySQL_User mywebuser
    AuthMySQL_Password mywebuserpass
    AuthMySQL_Password_Table mywebusers
    AuthMySQL_Username_Field userid
    AuthMySQL_Password_Field userpass
    AuthMySQL_Empty_Passwords off
    AuthMySQL_Encryption_Types SHA1Sum

    Require valid-user
    </Directory>
    <Directory /var/www/mywebsite.com/www/>
        Options +FollowSymLinks +MultiViews +Includes
        AllowOverride None
        Order allow,deny
        allow from all
    </Directory>

    ScriptAlias /cgi-bin/ /var/www/mywebsite.com/cgi-bin/
    <Directory "/var/www/mywebsite.com/cgi-bin/">
        AllowOverride None
        Options +ExecCGI -MultiViews +SymLinksIfOwnerMatch +Includes
        AddHandler cgi-script cgi pl
        Order allow,deny
        Allow from all
    </Directory>

    ErrorLog ${APACHE_LOG_DIR}/error.log

    # Possible values include: debug, info, notice, warn, error, crit,
    # alert, emerg.
    LogLevel warn

    CustomLog ${APACHE_LOG_DIR}/access.log combined

    Alias /doc/ "/usr/share/doc/"
    <Directory "/usr/share/doc/">
        Options Indexes MultiViews FollowSymLinks
        AllowOverride None
        Order deny,allow
        Deny from all
        Allow from 127.0.0.0/255.0.0.0 ::1/128
    </Directory>

    DirectoryIndex /cgi-bin/index.pl

</VirtualHost>

Submitted by d0nut (registered user) on Sun, 2008-10-26 12:19.

when using apache2.2 you'll probably get an error message in logfiles
Internal error: pcfg_openfile() called with NULL filename
i found the solution here: http://readthefuckingmanual.net/error/1387/
Just add AuthUserFile /dev/null as new setting

Submitted by Anonymous (not registered) on Thu, 2006-01-19 06:23.
Trouble Shoot & INSTALL - APACHE/MYSQL/PHP from scratch
-------------------------------------------------------

Since the apache2 that comes with debian proves to be a bitch when it comes to configuration
lets do this...

Reinstall apache2 and also get it to work such that it points to /var/www

DANGEROUS COMMAND SO HANDLE WITH CARE...
----------------------------------------

dpkg --purge --force-depends apache2-common
dpkg --purge --force-depends apache2

MAKE SURE YOU INSTALL OR DEPENDECIES WILL BREAK
------------------------------------------------

apt-get install apache2-common
apt-get install apache2


PHP5 SUPPORT
------------
add the below to "/etc/apt/sources.list"

deb http://people.debian.org/~dexter php5 sarge

install the necessary packages

apt-get install php5 libapache2-mod-php5 php5-mysql php5-curl php5-gd

For a full list of PHP5 modules

apt-cache search php5.

to install php and mysql modules use the below

cd /etc/apache2/mods-enabled/
ln -s /etc/apache2/mods-available/php5.load
ln -s /etc/apache2/mods-available/php5.conf

Now start the beast....

/etc/init.d/apache2 start

now in /var/www run your first php script... Please no "Hello World!!!"

<? phpinfo() ?>

IF THERE IS SOMETHING TOO OBVIOUS OR IF THERE ARE SOME REDUNANT STEPS
PLEASE CORRECT THEM !!! DON'T COMPLAIN ;)

Submitted by Anonymous (not registered) on Wed, 2005-12-21 21:50.

The official way to enable or disable an apache2 module:

a2enmod <module>

a2dismod <module>
Submitted by Anonymous (not registered) on Sat, 2013-02-23 07:16.

Thank you Anonymous for the official and easy way to enable a mod. Exactly what I was looking for. Not sure what all this other crap is about.

sudo a2enmod auth_mysql

worked perfectly.

Submitted by Anonymous (not registered) on Wed, 2006-01-25 20:55.
slight modification to the apache2/php installation i posted before... Do this before you install apache2... or the old settings will remain in the system
  apt-get --purge remove apache2
apt-get --purge remove apache2-common
apt-get --purge remove php4
when you install use this instead
  apt-get --reinstall install php5 libapache2-mod-php5 php5-mysql php5-curl php5-gd

-- thanesh .s
Submitted by xurizaemon (registered user) on Tue, 2007-06-26 22:04.

I was seeing intermittent errors using this module against MySQL 5.0 on both Debian Etch and Ubuntu Feisty. It happens due to a changed behaviour (the default is not to reconnect() when the connection drops from MySQL 5.0.3+) and needs a small patch to the libapache-mod-auth-mysql or libapache2-mod-auth-mysql source.

The patch is available at Giant Robot and the Debian bug is #420010