Simple User Authentication with Apache and MySQL

July 12, 2010
By Jason Gilmore

Send Email »
More Articles »

Managing Accounts in MySQL

Using Apache's native .htpasswd capabilities, you can password-protect a directory in mere minutes. However, maintaining user accounts can be difficult, particularly in situations where account subscriptions are regularly created, ending, or renewed. A more flexible solution is managing the account credentials within a MySQL table and configuring Apache to compare the provided credentials against this repository. You can then create a Web-based interface to manage these accounts, or even simply manage them using a utility such as phpMyAdmin.

Begin by creating the table used to manage the account credentials. At a minimum, this table should contain columns for storing the account username and password. I'll call this table accounts:

CREATE TABLE accounts ( username VARCHAR(100) NOT NULL, password CHAR(32) NOT NULL, PRIMARY KEY(username) );

Apache's default behavior is to use DES for password encryption. However, you can also use MySQL's native password() function. I've opted to use the latter and so have adjusted the password column width so it can manage 41 characters, which is the size of a string encrypted using the password() function.

When the table has been created, add a few test accounts. As I mentioned previously you could use a utility such as phpMyAdmin to perform this task, but in any case the SQL query will look something like this:

INSERT INTO accounts VALUES('jason', password('secret'));

With a few rows added, the accounts table will look like this:

+----------+-------------------------------------------+ | username | password | +----------+-------------------------------------------+ | jason | *14E65567ABDB5135D0CFD9A70B3032C179A49EE7 | | susie | *7C67218EF7410F248763DC99AEDC705367393C4A | +----------+-------------------------------------------+

Next you'll need to configure Apache so it can communicate with the accounts table. This is done by installing the mod_auth_mysql module. If you're running Ubuntu or Debian, you simply execute the following command:

%>sudo apt-get install libapache2-mod-auth-mysql

When mod_auth_mysql is installed, enable the module by executing the following command:

%>sudo a2enmod auth_mysql

Restart Apache, and make sure the module has been loaded by executing the following command:

%>apache2 -M

In the list of modules you should see auth_mysql_module. When configured, you can update your .htaccess file to refer to the accounts table by adding the following directives:

AuthBasicAuthoritative Off AuthMYSQL on AuthMySQL_Authoritative on AuthMySQL_Host localhost AuthMySQL_User root AuthMySQL_Password jason AuthMySQL_DB developer AuthMySQL_Password_Table accounts AuthMySQL_Username_Field username AuthMySQL_Password_Field password AuthMySQL_Encryption_Types MySQL Auth_MySQL_Scrambled_Passwords on

Save the file and attempt to access the restricted directory. This time Apache will talk to MySQL to determine whether you're allowed to enter!

Conclusion

You certainly can find more flexible authentication solutions, such as that provided by the Zend_Auth component, but if you're merely looking for a simple way to protect content, Apache's native capabilities should be more than sufficient for most cases.