mod_auth_mysql

mod_auth_mysql was written in order to allow users to use the blazing quick speed of MySQL in order to store authentication information for their apache web servers.

You can get the module here or from TCX's Contrib directory.

mod_auth_mysql usage guide

This manual assumes you've successfully compiled mod_auth_mysql, and that you're now trying to figure out how to use it.

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:

Creating the necessary SQL information.

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.

3. Insert the information into the table. Both the username and group fields are pre, whereas with the password, 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

If you're using a MySQL server other than localhost, and/or you want to specify a different user than the httpd user when accessing the MySQL server, and/or you need to specify a password for that user, you'd need to add the following line somewhere in your httpd.conf (doesn't really matter where):

Auth_MySQL_Info [host] [user] [password]

This information can *only* be specified in the server's httpd.conf, since it's used server-wide.

If you're going to use mainly one MySQL database for all of your pages, you should probably add the following line to your httpd.conf as well:

Auth_MySQL_General_DB [database_name]

The database can be set on a per-directory basis using a different directive in .htaccess, as mentioned later in this file.

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):

Allow any known user to accses:

AuthName My Company's Financial Information	the realm name, use some informative name
AuthType Basic	keep it that way
require valid-user	allow any valid user to access

Allow access only to specific users:

AuthName My Company's Financial Information	the realm name, use some informative name
AuthType Basic	keep it that way
require user johndoe devnull	let only johndoe and devnull access

Allow only members of group 'executives' access the information:

AuthName My Company's Financial Information	the realm name, use some informative name
AuthType Basic	keep it that way
require group executives	allow only members of this group to access

Note that with Apache 1.3, you would have to encapsulate the AuthName with double quotes if it contains spaces, e.g.
AuthName "My Company's Financial Information"

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

Auth_MySQL_DB [database_name]	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. Example: Auth_MySQL_DB http_auth
Auth_MySQL_Password_Table [password_table_name]	The name of the MySQL table that contains user:password pairs. By default it is 'mysql_auth'.
Auth_MySQL_Group_Table [group_table_name>	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'.
Auth_MySQL_Username_Field [username_field_name]	The field name of the username field. By default it is 'username'.
Auth_MySQL_Password_Field [password_field_name]	The field name of the password field. By default it is 'passwd'.
Auth_MySQL_Group_Field [group_field_name]	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, 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 crypt.
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. 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_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_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 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.

If you still have any questions, feel free to write me at bourbon@netvision.net.il

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.
3.	Insert the information into the table. Both the username and group fields are pre, whereas with the password, 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).