Simple User Authentication with Apache and MySQL

 

Whether you're a college professor wishing to grant students access to test results, or a research firm seeking to provide paying subscribers access to custom reports, you'll need to devise some way for your users to identify themselves in order to gain access to the restricted content. By identifying themselves, the users authenticate their identities. They often accomplish this authentication by providing a username and password, for which the college professor could provide the entire class with a single authentication combination. In other cases, however, you should grant a unique username and password to each subscriber.

In this article I'll show you how to implement both of these authentication approaches using nothing more than the Apache Web Server's native capabilities. The first approach can be implemented in mere minutes using a text file and a few command-line calls. The second approach is a tad more involved, requiring a bit of additional server configuration and a MySQL database, although you'll gain some additional flexibility along the way.

Before we begin, keep in mind that while you can indeed use these approaches to restrict access to a certain part of your website, they do not protect the transmission of the username and password from the client's computer to the server! Because the credentials are transmitted in plaintext, a savvy attacker could capture the information as it's passed over the network, thereby allowing the attacker to masquerade as an authorized user. To ensure maximum security, you should configure your server to use an SSL certificate, a task that although fairly easy to accomplish is out of the scope of this article.

Storing Credentials in a Text File

Apache has long supported a fairly simple authentication solution involving storing a username and password within a text file, which resides within the directory you'd like to protect. This text file is named .htpasswd, and its contents generally look like this:

jason:npvd3u9E86LaU susie:z4ETQllnrvvDk

You'll create this file and add user accounts to it using a command-line utility appropriately named htpasswd. To password-protect a particular directory within your website, navigate to that directory via the command line and execute the following command:

%>htpasswd -c .htpasswd jason 

This command accomplishes two important tasks: It creates the file and then creates a new user named jason. When you execute the command you'll be prompted to provide and confirm a password for user jason.

When your .htpasswd file and user are created, you're free to add other users by executing the same command but this time omitting the -c option and providing a different username. For instance, to add another user named susie you would execute the following command and again provide and confirm Susie's password:

%>htpasswd .htpasswd susie 

Creating the .htpasswd file isn't enough to protect the directory. You'll also need to modify Apache's configuration to recognize the .htpasswd file. Thankfully these configuration changes can be managed locally, meaning you won't need to modify Apache's httpd.conf file in order to effect these changes -- something that wouldn't be possible in most hosting environments. Instead, you'll use a file named .htaccess, which can be used to configure Apache on a per-directory basis. Although the .htaccess file can be used to perform many tasks, for the purposes of authentication you'll place it in the same directory as your .htpasswd file, adding the following lines to it:

AuthName "Restricted Content"
AuthType Basic
AuthUserFile /var/www/wjgilmore.com/members/.htpasswd
Require valid-user


When saved, navigating to the directory will cause Apache to verify whether the user has already provided valid credentials. If so, the user is granted access to the directory.

 

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!

 source