For example:
CREATE DATABASE apacheauth;

Next, create a user that will be used by Apache to access the credentials:

GRANT SELECT ON apacheauth.* TO apache@localhost IDENTIFIED BY ‘apachepass’;

Now that the database user is created, it’s time to add a table to hold the login information. For now start out with a simple table that just holds the username and password:

USE apacheauth;
CREATE TABLE user_info (
user_name varchar(50) NOT NULL,
user_password varchar(50) NOT NULL,
PRIMARY KEY (user_info)
);

You can name the table and columns however you like. In the preceding example, user_info is used for the table name, and user_name and user_password are used for the login/password combination because these are the default names recognized by mod_auth_mysql.

In later versions of mod_auth_mysql, the default column for password is user_password, not user_passwd as it states in the module documentation.

Next, create an initial test user account that you’ll use later when testing the authentication:

INSERT INTO user_info (user_name, user_password)
VALUES (‘testuser’, SHA1(‘testpass’));

Notice that the SHA1 function is used on the password in the statement, instead of the PASSWORD()
function. With mod_auth_mysql you can use a handful of encryption methods for the password—this
example, just happens to use SHA1.

Installing the Module

Once you have your database table set up, you’ll need to actually download the source for mod_auth_mysql, available at http://modauthmysql.sourceforge.net. At the time of this writing, the current version was 2.9.0, so you would download mod_auth_mysql-2.9.0.tar.gz.

After you’ve downloaded the module source, extract the tarball:

tar -xzvf mod_auth_mysql-2.9.0.tar.gz

Once the tarball is extracted, change directories to the newly created source folder:

cd mod_auth_mysql-2.9.0

To build the module from the downloaded source code, you’re actually going to use Apache’s apxs
command directly. Execute the following command, using the path to your apxs binary on your system;
the default Apache location of /usr/local/apache2/bin is used here:

/usr/local/apache2/bin/apxs -c -lmysqlclient -lm -lz mod_auth_mysql.c

If the build is successful, you should see a couple of compilation lines, and no errors. If, instead, you get an error about a missing mysql.h file, and a whole slew of MySQL-related errors, you might need to manually specify the paths to the MySQL libraries and include files. Using the default locations from MySQL, your command might instead look like this:

/usr/local/apache2/bin/apxs -c -lmysqlclient -lm -lz
-L /usr/local/mysql/lib/mysql
-I /usr/local/mysql/include/mysql
mod_auth_mysql.c

To install the newly compiled module, you’ll need to execute the following command as root. As usual, change the path to apxs to point to your apxs binary:

/usr/local/apache2/bin/apxs -i mod_auth_mysql.la

The next installation task you need to perform is to add the necessary command to load the module in httpd.conf. To do this, open your httpd.conf file, find a suitable location to add a module definition— usually at the very end of the configuration file, or next to other LoadModule statements—and add the following:

LoadModule mysql_auth_module modules/mod_auth_mysql.so

Finally, restart Apache.

Configuration and Usage

Once you have your database tables ready and modules installed, all that remains is to tell Apache what needs to be protected. Like standard Apache basic authentication, you’ll need to add some configuration directives to either a section in httpd.conf, or create a .htaccess file in the directory you choose to protect.

Telling Apache to validate user credentials against your MySQL database would usually involve the following at a minimum:

AuthName “”
AuthType Basic
AuthMySQLDB dbname
AuthMySQLUser userid
AuthMySQLPassword password
AuthMySQLEnable On
require valid-user

The first two lines and the last line of these directives should already be familiar to you. They simply define the name of the protected area, the type of authentication, and that any valid user found in the checked resource is allowed access. The other lines tell Apache where to find the authentication information.
Instead of giving Apache the name of a file that contains the passwords for each valid user, you’re supplying database information—database name, username, and password—so that Apache and mod_auth_mysql know where to look. The sixth line, AuthMySQLEnable On, simply tells Apache to actually use mod_auth_mysql—it’s a way to disable MySQL authentication without having to completely unload or remove the module.

To use the authentication tables you created earlier, you would add the following in your or .htaccess definitions:

AuthName “MySQLAuth”
AuthType Basic
AuthMySQLDB apacheauth
AuthMySQLUser apache
AuthMySQLPassword apachepass
AuthMySQLEnable On
AuthMySQLPwEncryption sha1
require valid-user

Here, you’re telling Apache and mod_auth_mysql to authenticate users against your apacheauth database. Apache will be logging into the database using the apache account you created earlier, and you’re also telling mod_auth_mysql to hash the given password with SHA1, before matching it against the value in the database.

At this point, you should be able to log into your protected area via a web browser, supply the user credentials added to the user_info table (testuser:testpass), and be successfully authenticated in the directory.

While this example is rather simplistic and stripped down, mod_auth_mysql actually provides a boatload of configuration options, so you can customize your authentication for nearly any situation. The following sections describe some of the configuration options.

AuthMySQLEnable On | Off

This enables or disables mod_auth_mysql authentication, without you having to remove or unload the module from Apache directly.

AuthMySQLHost localhost | hostname | ipaddress

This is the hostname or IP address of the MySQL database server. If your environment runs Apache and MySQL on different machines, you’ll need to use this. The default value is localhost, so machines running both Apache and MySQL can usually ignore this setting.

AuthMySQLPort port_number

This is the TCP/IP port on which MySQL is listening. The default value for MySQL is port 3306, but can be changed. The default value for this option is also 3306, so the option needs to be specified only if MySQL is listening on a nonstandard port.

AuthMySQLSocket socket_file_path

This is the UNIX socket file used to access MySQL on a UNIX machine. The default value is /tmp/mysql.sock, and like AuthMySQLPort, it needs to be used only in nonstandard installations.

AuthMySQLUser userid

This is the user ID used to access the MySQL database holding the authentication information. Most database systems will not allow users or the Apache process to access the database without a proper userid/password combination, so set this along with AuthMySQLPassword to a non-root user account that has access to the authentication tables. At a minimum, the user will need SELECT access to the authentication tables.

AuthMySQLPassword password

Used in conjunction with AuthMySQLUser, this option specifies the password for the user account accessing the MySQL authentication tables.

AuthMySQLDB dbname

This is the name of the database in MySQL that holds the authentication tables. If no name is specified, the default value of test will be used.

AuthMySQLUserTable table_name

This is the name of table in the database that holds the usernames and passwords. If no table is specified, he default value of user_info is used.

AuthMySQLNameField column_name

This is the name of the column in the user table (AuthMySQLUserTable) that holds the usernames. When creating the tables in MySQL, ensure that this column has an index or key, ensuring each value is unique. The =length of the column is whatever you decide it should be in MySQL. The default value is user_name.

AuthMySQLUserCondition

When mod_auth_mysql performs a user lookup, it performs a simple SQL SELECT statement, with any criteria needed to perform a match. The AuthMySQLUserCondition field allows you to specify extra parameters to be used in the WHERE clause of the SQL statement.

AuthMySQLPasswordField column_name

This is the name of the column in the user table that holds the users’ passwords. The length of the column in MySQL should be at least as long as the encrypted password hash. The default value for AuthMySQLPasswordField is user_password.

AuthMySQLPwEncryption none | crypt | scrambled | md5 | aes | sha1

This is the method of encryption used when encoding the passwords in the database, as follows:

❑ none: No encryption/plain text
❑ crypt: UNIX crypt() encryption
❑ scrambled: MySQL PASSWORD() encryption
❑ md5: MD5 hashing
❑ aes: Advanced Encryption Standard (AES) encryption
❑ sha1: Secure Hash Algorith (SHA1)

If no value is specified, the default value of crypt is used.
When using AES password encryption, make sure the password field in your MySQL user tables is a BLOB type, not a CHAR, VARCHAR, or BINARY type. Unless the column type is one of the BLOB variations, MySQL will strip any extra encoded characters from the encrypted passwords—and you’ll never be able to match the given password to the database values.

AuthMySQLSaltField
This allows you to specify one of three values to use as the “salt value” when encrypting passwords. A value of <> tells mod_auth_mysql to use the password itself as the salt field. A value of uses the value of string as the salt field. Any string not surrounded by less-than/greater-than symbols is treated as the name of a database column, the value of which is used as the salt field.

AuthMySQLGroupTable
This is used to set the name of the table containing group information, when Apache is told to check groups instead of individual users (require group versus require valid-user).

AuthMySQLGroupCondition
Similar to AuthMySQLUserCondition, this directive allows you to specify conditional statements to include in the WHERE clause when matching user groups.

AuthMySQLGroupField
This is the name of the field in the database that contains the group names. This field is used whenm matching groups using Apache’s require group directive.

AuthMySQLAuthoritative
This tells Apache whether or not to fallback to other modules if MySQL authentication fails.