Your Daily Source for Apache News and Information  
Breaking News Preferences Contribute Triggers Link Us Search About
Apache Today [Your Apache News Source] To internet.com

Apache HTTPD Links
Apache-Perl Integration Project
Apache XML Project
The Jakarta Project
PHP Server Side Scripting
ApacheCon
The Java Apache Project
Apache-Related Projects
The Apache FAQ
The Apache Software Foundation
Apache Module Registry
Apache Project

  internet.com

Internet News
Internet Investing
Internet Technology
Windows Internet Tech.
Linux/Open Source
Web Developer
ECommerce/Marketing
ISP Resources
ASP Resources
Wireless Internet
Downloads
Internet Resources
Internet Lists
International
EarthWeb
Career Resources

Search internet.com
Advertising Info
Corporate Info
Apache Guide: Apache Authentication, Part 3
Aug 7, 2000, 03 :32 UTC (23 Talkback[s]) (21412 reads) (Other stories by Rich Bowen)

By

In my last article, I talked about using databases for authentication, and I introduced mod_auth_dbm as a possible way to do that.

This week, we'll look at MySQL, a very popular database server, and using mod_auth_mysql to use MySQL to store your authentication information.

A little about MySQL

MySQL is a wonderful database server, which is distributed under the GPL, and is available from http://www.mysql.com/ MySQL is lightweight and fast. It lacks some of the features of larger, more expensive database servers, such as stored procedures, triggers, and various other things, but it contains most of the functionality needed for most small to medium projects. And, it contains some cool stuff like a regular expression language that can be used in SQL statements.

Because MySQL is free, and because it is just such a great database, it is the favorite database in use by folks on *nix operating systems--particularly folks with small budgets. And it also runs on Windows.

mod_auth_mysql

mod_auth_mysql lets you put your usernames and passwords in a MySQL database, and authenticate directly against that. There are a number of advantages to this, in addition to the obvious one of speed of data access. If, for example, you are already storing user information in a database table, it would be irritating to have to store the username and password in another location (the htpasswd file). You would have to maintain the data in two places, and if you let them get out of sync, users would be unable to log in. With mod_auth_mysq, however, you can authenticate directly against the databsae, and keep your authentication information just one place. Usernames and passwords can be updated with a SQL query, with no messing around in text files. And users' group membership can be easily altered.

Installation and Configuration

You can get mod_auth_mysql, and learn more about it, at http://bourbon.netvision.net.il/mod_auth_mysql/

mod_auth_mysql can be compiled as a DSO (Dynamic Shared Object), and then included in the server with a configuration directive. For more details on this, please see earlier articles in which we discuss DSOs.

To configure mod_auth_mysql, you need to tell it what database you want to authenticate against, and what fields in which table contain the relevant information.

The following are the configuration directives that you'll need to know about:

Auth_MySQL_Info [host] [user] [password]

This directive tells where your server is running, and what username and password are necessary to get data from the database. This directive is only necessary if the server is running somewhere other than localhost, or if access is via some user other than the httpd user.

If all of your authentication will be done against the same database, you'll probably want to set the following directive:

Auth_MySQL_General_DB [database_name]

If you'll be authenticating different directories or files against different databases, you can leave this out, and set the database in the various directories.

The following directives can appear either in your httpd.conf configuration file, or in the various directories in .htaccess files. (See Ken Coar's article about .htaccess files for more information.)

Note that you'll be using the usual directives to set up password protection on the directory:

        AuthType Basic
        AuthName "Members Only"
        require group admin

Auth_MySQL_DB [database_name]--Tells which database you are authenticating against.

Auth_MySQL_Password_Table [password_table_name]--Tells which table in that database contains the password information. Unless you specify, it is assumed that the username is contained in the field 'username', and the password is contained in a field 'password'. You can change this. (See below.)

Auth_MySQL_Group_Table [group_table_name]--Ordinarily, you'll probably just want to store the group field in the same table as the usernames and passwords, but if you need to store it in a different table, this is where you'll specify where that is.

Auth_MySQL_Username_Field [username_field_name]--If your username is a field other than 'username', you can specify that with this directive.

Auth_MySQL_Password_Field [password_field_name]--If your password is a field other than 'password', you can specify that with this directive.

Auth_MySQL_Group_Field [group_field_name]--If your group name is a field other than 'groups', you can specify that with this directive.

Auth_MySQL_Encrypted_Passwords on/off--Tells mod_auth_mysql whether the passwords are in the database encrypted, or plain-text. This is on by default - that is, it is assumed that your passwords are stored encrypted.

There are several other directives, but these are the main ones that you will be using most of the time. The following is an example .htaccess file that works for me:

        Auth_MySQL_Info localhost db_user db_password
        Auth_MySQL_DB   authentication
        Auth_Mysql_Password_Table       passwords
        AuthType Basic
        AuthName "Members Only"
        require valid-user

The above assumes that the username is in a field username, and the password is encrypted, and is stored in the field password.

Now What?

Once you have your .htaccess file set up as described above, you will get the password dialog as normal. There will be no difference to the user.

You can maintain your user and password lists via whatever database management tool you are used to using. There's no handy tool like dbmmanage for managing these accounts from the command line, but I'm working on one.

You can use Perl and DBI to talk to your database. In my next column, I'll be talking at greater length about using Perl to manage your password files. There are a plethora of ways to do this, so it really merits its own article.

Summary

mod_auth_mysql allows you to keep your users, passwords, and groups, in a MySQL database. MySQL is a lightweight, fast, free database server which is available for most popular operating systems.

Future columns

Please let me know if there are other topics that you'd like for me to talk about in future columns. You can send your suggestions to me at And please let me know what you think of my columns, at that same address. Many thanks for reading down this far!

--Rich

  Current Newswire:
Apache 2.0.32 beta is available

Everything Solaris: Apache: The Basics

Apache Jakarta James Mailserver v2.0a2 Released

PostgreSQL v7.2 Final Release

Daemon News: Multiple webservers behind one IP address

Zend Technologies launches Zend Studio 2.0

NuSphere first to enable development of PHP web services

Covalent Technologies raises $18 million in venture capital

Apache 1.3.23 released

wdvl: Build Your Own Database Driven Website Using PHP and MySQL: Part 4

 Talkback(s) Name  Date
  Using a group table
I was disappointed that you didn't cover the use of a group table more. This module has very little documentation with it, especially related to setting up and using a seperate group table for allowing a user to be in more than one group.   
  Aug 7, 2000, 11:47:40
   Re: Using a group table
Actually, I do this myself. I just got it working two weeks ago - the hardest time I had was compling the thing.

Anyway, I've got three tables in mySQL, described here:

table users
user_name VARCHAR(15)
password VARCHAR(15)

table groups
group_name VARCHAR(15)

table group_members
user_name VARCHAR(15)
group_name VARCHAR(15)

The group_members table is a join of users and groups. Then in my httpd.conf file, I've got this (or your .htaccess file if you wish):

AuthName supernet
AuthType Basic
Auth_MySQL on
Auth_MySQL_Authoritative on
Auth_MySQL_Password_Table users
Auth_MySQL_Password_Field password
Auth_MySQL_Username_field user_name
Auth_MySQL_Group_Table group_members
Auth_MySQL_Group_Field group_name
require group employee
Allow from all

And it does work. So it seems like in the group table it looks for a record with both username and password. If that query comes back without any matches, authorization fails.

I really like doing this as opposed to using the text files because its easier to manage. A clever query or two makes managing group membership a piece of cake.
  
  Aug 7, 2000, 13:56:20
  Can't use Auth_MySQL_Info!
When I didn't include Auth_MySQL_Info, it said access deined: passwd not included, but then I included Auth_MySQL_Info localhost username passwd, it said Auth_MySQL_Info not allowed here in the error log file...please help.
Thanks   
  Aug 21, 2000, 08:18:31
   Re: Can't use Auth_MySQL_Info!
Sounds like Auth_MySQL_Info is in an .htaccess file. I believe it must be in your http.conf file.   
  Aug 25, 2000, 17:57:44
  mod_auth_mysql
i tried to follow the link to http://bourbon.netvision.net.il/mod_auth_mysql/ to get the mod but it says it is unavailable. Does anyone know somewhere else I can get it?


Thanks   
  Oct 9, 2000, 03:35:58
   Re: mod_auth_mysql
> i tried to follow the link to http://bourbon.netvision.net.il/mod_auth_mysql/ to get the mod but it says it is unavailable. Does anyone know somewhere else I can get it?

Thanks


True, where on earth can we get mod_auth_mysql??   
  Oct 19, 2000, 17:08:18
   Re:Re: mod_auth_mysql
You can get mod_auth_mysql from ftp.southcom.com.au/.4/mysql/, it is a gzipped file though.

Zayyid   
  Jan 2, 2001, 00:41:20
  mod_auth_mysql for Windows?
Is there a version of mod_auth_mysql for the Windows version of Apache? Please email me a link if you know of one. john@sepodaticreations.com

Thanks.

---John Holmes...   
  Oct 31, 2000, 09:42:33
  Search for mod_auth_mysql
Hello !

I tried to get mod_auth_mysql from ftp://ftp.southcom.com.au/.4/mysql/ but when I want to go in the MySQL-3.23 folder, a window appear and say that I can't connect to the ftp server with the user anonymous.

So how can I get mod_auth_mysql and what is the name of the files ?
Can you answer me, please ?

Thanks and have a good day !
  
  Jan 18, 2001, 16:54:15
   Re: Search for mod_auth_mysql
You can get mod_auth_mysql from the mysql.com website under the contributed stuff on the downloads page.

Took me a while before I thought of looking there   
  Aug 17, 2001, 22:54:19
  mod_auth_mysql enabled
I have been unable to get Apache to configure correctly using the suggested --activate-module=src/modules/auth_mysql/libauth_mysql.a option. The Makefile is always incorrect. Any suggestions would be greatly appreciated. Thanks.   
  Sep 10, 2001, 20:45:25
  Group tables
So this lets you assign a person to more than one group? Is group_name a key on group_members?

Thanks   
  Aug 7, 2000, 19:02:12
   Re: Group tables
Yes, you can then put one user_name in multiple groups. Neither field is a unique key field. You can still create indexes on them; whether or not you need to really depends on how many users and how many groups you need to have.

  
  Aug 7, 2000, 22:00:35
  using htpasswd in Windows
i am having difficulty in setting directory in windows using htpasswd and i cant password protect my site.. Most of tutorials are particular only in Unix environment. if you know a link about my case pls. email me..   
  Feb 5, 2002, 06:02:26
   Re: Re: Group tables
Thanks for all your excellent comments about group tables. For some reason, I missed the talkbacks this week. You covered things very thoroughally.

Note that you can also put the groups in the database as a comma-separated list, if that works better with your existing database schema. Of course, you'd have to be careful not to overrun your allocated space in that data column.

--Rich   
  Aug 13, 2000, 01:35:16
  problems with mod_auth_mysql
Hello!

I'll appreciate it very much if you (someone) could assist me.

I'm usisng MySQL server on a Windows plataform, PHP and Apache security
mudules for access control in a Red Hat Linux server.

I want to use the modules mod_auth_mysql, but I don't know how to
configure the httpd.conf file to specify that my auth database is in
another server. Where should I install mod_auth_mysql in my database server
(Windows), or in my Red Hat Linux Server?   
  Feb 7, 2002, 19:55:15
  Default password field
The default password field is 'passwd', not 'password'. I am using Apache 1.3.12.   
  Aug 10, 2000, 18:06:52
  encryption format
If you enter your encrypted passwords into the database using the MySQL command PASSWORD(), it will not work with Apache. You must use the Apache command htpasswd to generate the password.

Another issue with 1.3.12 is that you must explicity specify the Auth_MySQL_Groups_Table directive, or it will default to the mysql_auth table in the current database.   
  Aug 10, 2000, 18:19:55
   Re: encryption format
I used an MySQL password field type which automatically encrypts contents. I'm using Apache 1.3.12 and specifying the MySQL encryption directive. Works fine.   
  Aug 18, 2000, 18:58:10
  compilation
I ve installed apache from a source distribution quite a long time ago with DSO enabled. Unkknown problems occured when i tried to compile the mod_auth_mysql using apxs -c ... Does anybody know which is the right way to compile it as a loadable module?   
  Aug 11, 2000, 05:53:21
   Re: compilation
To be honest, I haven't been able to get the mod to compile as a dso with apache. For expediency I compiled everything statically. Apache takes longer to load, but once loaded I haven't seen any performance issues with doing it that way. Of course, I also removed a bunch of modules that don't apply to my situation :)

Seriously, I had no end of trouble trying to get that to work, but I'm sure its mostly due to my inexperience with building apache.

Now if only I could get Solaris to cooperative with me...   
  Aug 14, 2000, 13:59:44
   Re: Re: compilation
Now, do you get how to compile the mod_auth_mysql module with DSO support? Kindly let me know the procedures   
  Sep 21, 2001, 00:49:12
  Code
Would have been more helpful if a small working sample was provided as well. Otherwise the article is good.   
  Aug 18, 2000, 09:46:25
Enter your comments below.
Your Name: Your Email Address:


Subject: CC: [will also send this talkback to an E-Mail address]
Comments:

See our talkback-policy for or guidelines on talkback content.

About Triggers Media Kit Security Triggers Login


All times are recorded in UTC.
Linux is a trademark of Linus Torvalds.
Powered by Linux 2.4, Apache 1.3, and PHP 4
Copyright 2002 INT Media Group, Incorporated All Rights Reserved.
Legal Notices,  Licensing, Reprints, & Permissions,  Privacy Policy.
http://www.internet.com/