ID #161

How do I protect a directory with mod_auth_mysql?

Applies to: Grid System

In order to use mod_auth_mysql, you need to have a database. You can create and delete MySQL databases in the Onsite control panel.

To use mod_auth_mysql, go to the directory that you want to protect and create or upload a file called: .htaccess (include the "." before the "htaccess").

Files that begin with a dot are hidden from regular file listing (ls) commands. To see files that begin with a dot, do a complete file listing command (ls -a).

In that .htaccess file, have the following lines:

AuthName "Your Protected Area"
AuthType Basic

# Directives specific to mod_auth_mysql
# mod_auth_mysql automatically connects to
# host: db.modwest.com

# Auth_MySQL_Info [server] [user] [pass]
Auth_MySQL_Password MYSQL_PASS
Auth_MySQL_Password_Table MYSQL_TABLE_NAME
Auth_MySQL_Encrypted_Passwords Off
# turn the above to "On" if you used crypt() on users'
# passwords before storing them
Auth_MySQL_Non_Persistent On
# turn the above to "Off" if you want
# other authentication to take over in case the visitor
# fails mysql authentication
Auth_MySQL_Empty_Passwords Off
# turn the above to "On" if you want to allow authenticate
# just a correct username and no password

# End mod_auth_mysql specific directives

require valid-user

Where "MYSQL_UNAME" is your own MySQL username, "MYSQL_PASS" is your own MySQL password, "MYSQL_DATABASE_NAME" is your own MySQL database name, "MYSQL_TABLE_NAME" is the name of the table in your database that contains the username and password fields that will be used to do the authentication, "NAME_OF_USERNAME_FIELD" is the name of the field in the specified table that stores the username, and "NAME_OF_PASSWORD_FIELD" is the name of the field in the specified table that stores the password.

For example, if you created a database from your control panel named "mycompany", and inside that database created a table named "employees" with these fields:

create table employees ( my_username varchar(25), my_passwd varchar(25) );

| Field | Type | Null | Key | Default | Extra |
| my_username | varchar(25) | YES | | NULL | |
| my_passwd | varchar(25) | YES | | NULL | |

You can either create the table like the one above in the MySQL Monitor from a shell prompt, or use the MySQL Admin web page of your Control Panel. If you use the MySQL Admin web page, it would be prudent to make the username field at least "unique" if not a "primary key", although this is not necessary for mod_auth_mysql to work.

If you created the above table named "employees" inside your database named "mycompany", and your MySQL Username is "mysqluser" and your MySQL password is "mysqlpass", then your .htaccess file in the directory you want to protect would look like this:

AuthName "Your Protected Area"
AuthType Basic
Auth_MySQL_Username mysqluser
Auth_MySQL_Password mysqlpass
Auth_MySQL_DB mycompany
Auth_MySQL_Password_Table employees
Auth_MySQL_Username_Field my_username
Auth_MySQL_Password_Field my_passwd
Auth_MySQL_Encrypted_Passwords Off
Auth_MySQL_Non_Persistent On
Auth_MySQL_Empty_Passwords Off
require valid-user

Then go to any URL underneath that directory where you have put the above .htaccess file and you will only be allowed in if you supply a username and password that exists in your table.

For example, if you inserted this data into your table:

insert into employees values('abc', '123');

| my_username | my_passwd |
| abc | 123 |

then you could only get into the site with the username "abc" and password "123".

The directive Auth_MySQL_Info is allowed in your .htaccess file and takes 3 arguments: database_host mysql_username mysql_password
Use of this directive is not needed if you are using the Auth_MySQL_Password directives and intend to connect to db.modwest.com. If you want to connect to a different database host, you should using this directive.

Last update: 2010-10-03 13:12
Author: FAQ Admin
Revision: 1.2

Digg it! Share on Facebook Print this record Send FAQ to a friend Show this as PDF file
Please rate this FAQ:

Average rating: 0 (0 Votes)

completely useless 1 2 3 4 5 most valuable

You can comment this FAQ

Comment of Anonymous:
Documentation from the mod_auth_mysql available on sourceforge and customized slightly by Modwest. This is the version that came AFTER the one by Rob McCool & Brian Behlendorf, adapted to Shambhala by rst, and converted to use MySQL by Vivek Khera. It is also AFTER the one written by Zeev, and is an attempt to merge both pieces of code.

mod_auth_mysql, like other apache authentication modules, is used in order to protect pages with username/password. The unique thing is that the passwords and usernames is stored in a MySQL database for much quicker access. Also, unlike the previous implementation of the module, SQL links are kept alive in between hits to acheive even better performance.

Protecting a directory with a username/password is simple, and involves two steps:

1. Creating the necessary SQL information.
2. Telling apache to protect the page using that information.

Creating the necessary SQL information

You would generally need one table, that contains 3 fields - username, password, and group. In some cases the group wouldn't be required and in others you may want to have extra fields in that table for other usages. If you already have the database and table with the necessary fields, you can skip to the next phase. Otherwise:

1. Create a database to store the authentication table, e.g.:
prompt> mysqladmin create http_auth
NOTE: You *don't* have to have this table in a seperate database, you can skip creating a new database and use an existing database if it fits your needs.

2. Create the auth table, e.g.:
prompt> mysql http_auth
mysql> create table mysql_auth (
-> username char(25),
-> passwd char(25),
-> groups char(25),
-> primary key (username)
-> );
NOTE 1: You *don't* have to use a new table for this purpose;
You can use existing fields in existing tables for
this purpose.
NOTE 2: All of the above names (the table name and field
names) are the defaults the module looks for. They
CAN be overriden using directives.
NOTE 3: The username/passwd information and username/group
information can be stored in seperate tables (using
different table names for the password table and group
table). This is useful if you want some users to have
multiple (or no) groups. In order to do that, you
should have one row in the username/passwd table, and
multiple rows in the username/group table, one for
each group the user is in.

3. Insert the information into the table. Both the username and group fields are plaintext, whereas the password field should contain standard UNIX DES encrypted passwords (this can be overriden using a directive as well, but the default is using encrypted passwords).

Telling apache to protect the page using that information

1. Specify the MySQL Username and Password to use to make the connection to the database server by putting in an .htaccess file either:



Auth_MySQL_Username user
Auth_MySQL_Password passwd

The latter 2 lines will presume you mean to connect to db.modwest.com. If you want to connect to a different server, you'll need to use just the first directive instead.

2. Create (or update) a file named .htaccess inside the
directory you would like to protect. Here are a few simple
.htaccess files (full documentation about the various possible
non-MySQL-auth specific directives can be obtained from the
apache docs):

(I) Protect your information allowing access to any user that's
in the SQL auth table:
AuthName "Private Area" # use some informative name
AuthType Basic
require valid-user # allow any valid user to access

(II) Allow access only to specific users:
AuthName "Private Area"
AuthType Basic
require user johndoe devnull # let only johndoe and devnull in

(III) Allow only members of group 'executives' access the information:
AuthName "Private Area"
AuthType Basic
require group executives # allow only members of this group in

3. Take a look at the following directives, and see if you need to
use any of them:

The MySQL database to use. If you havne't specified
Auth_MySQL_General_DB earlier, in the httpd.conf file, you
*must* specify this directive.
Auth_MySQL_DB http_auth

The name of the MySQL table that contains user:password pairs.
By default it is 'mysql_auth'.

The name of the MySQL table that contains user:group pairs.
Typically you'd probably just want to triplets of
user:password:group inside the same table, but you can use a
different table for user:group pairs if you'd like. By default
it is 'mysql_auth'.

The field name of the username field. By default it is

The field name of the password field. By default it is

The field name of the group field. By default it is 'groups'.

Auth_MySQL_Empty_Passwords on/off
Whether or not to allow empty passwords. If the password
field is empty (equals to '') and this is set to 'On', users
would be able to access the page by just specifying their
username without any password checking. If this is 'Off', they
would be denied access. Default: On.

Auth_MySQL_Encryption_Types [Plaintext, Crypt_DES,
Crypt_MD5, MD5, MySQL]
This directive tells the authentication module which
encryption type(s) to use. It overrides the
Auth_MySQL_Scrambled_Passwords and
Auth_MySQL_Encrypted_Passwords directives if it appears after
them. More than one encryption type may be specified, to
instruct the module to check each password through more than
one encryption scheme. For example,
Auth_MySQL_Encryption_Types Plaintext Crypt_DES will instruct
the module to check each password both as-is, and through DES

Auth_MySQL_Encrypted_Passwords on/off
Whether or not to use standard UNIX DES encrypted passwords.
If turned on, the module expects the password field to contain
standard UNIX DES encrypted passwords (2 bytes salt plus 11
bytes encrypted data). If turned off, the passwords are
expected to be plaintext, unless Auth_MySQL_Scrambled_Passwords
is turned on. Use of this directive is not encouraged - use
Auth_MySQL_Encryption_Types instead. Default: On.

Auth_MySQL_Scrambled_Passwords on/off
Whether or not to use passwords scrambled with MySQL's
password() routine. If turned on, the module expects the
password field to contain standard passwords encrypted with
the SQL password() function in MySQL. If turned off, the
passwords are expected to be plaintext, unless
Auth_MySQL_Encrypted_Passwords is turned on. Use of this
directive is not encouraged - use
Auth_MySQL_Encryption_Types instead. Default: Off.

Auth_MySQL_Authoritative on/off
Whether or not to authenticate using other authentication
modules after the user is successfully authenticated by the
MySQL auth module.
Default: On (i.e., don't pass on the request).

Auth_MySQL_Non_Persistent on/off
By turning on this option, you can tell the module to close the
MySQL link after each authentication request. Note that I
can't think of any good reason to do it, unless your platform
makes MySQL go crazy when it has plenty of simultaneous threads
(bad handling of file descriptor may cause that). In my
opinion, one should increase the maximum number of simultaneous
threads in MySQL and keep this option Off. Default: Off.

Auth_MYSQL on/off
Whether or not to enable MySQL authentication. If it's off,
the MySQL authentication will pass on the authentication job
to the other authentication modules (e.g. the flatfile auth
module). If it's on, and a database name was specified - the
MySQL module will be used for authentication.
Added at: 2002-06-03 19:54

Comment of Anonymous:
If you want to use the group feature, you need to add this to your .htaccess file:

Auth_MySQL_Group_Table groups
Auth_MySQL_Group_Field groupname
require group somegroup

and that implies that you have a table named "groups" with a field named "groupname", and in order to get authenticated, a person has to have the groupname field in the groups table literally be 'somegroup'.

mysql> select * from groups;
| username | groupname |
| abc | somegroup |

where "abc" is a user in your users table.
Added at: 2002-06-02 19:57

Comment of Anonymous:
If your database host (db.yourdomain.com) does not resolve to the same machine as "db.modwest.com" then you will need to put in a directive for the correct database hostname. I don;t know how you do that though.
Added at: 2004-06-11 15:50