This is the USAGE documentation that is included with the mod_auth_mysql package. I've mirrored it here because the old URL that used to provide this info (http://bourbon.netvision.net.il/mysql/mod_auth_mysql/) is no longer working.

You can download the mod_auth_mysql module here. There's also an updated version available on sourceforge.


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. 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.
  2. 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.
  3. 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):
    1. Protect your company's financial information (not recommended to put on the web:) to any user that's in the SQL auth table:
      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
      	  
    2. 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
      	  
    3. 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 and later, you would have to encapsulate the AuthName with double quotes if it contains spaces, e.g.
      AuthName        "My Company's Financial Information"
           
  4. 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, 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.