Keep Server Online
If you find the Apache Lounge, the downloads and overall help useful, please express your satisfaction with a donation.
or
A donation makes a contribution towards the costs, the time and effort that's going in this site and building.
Thank You! Steffen
Your donations will help to keep this site alive and well, and continuing building binaries. Apache Lounge is not sponsored.
| |
|
 Topic: Database (DBD) for (MySQL) authentication, vhosts, & log |
|
| Author |
|
tdonovan
Moderator
Joined: 17 Dec 2005
Posts: 611
Location: Milford, MA, USA
|
 Posted: Sat 26 Jan '08 19:40 Post subject: Database (DBD) for (MySQL) authentication, vhosts, & log |
 |
|
Updated the ODBC driver for Apache 2.2.8+, along with two modules which allow using SQL for virtual hosts and for access logging.
Apache already comes with a module for user authentication using SQL: mod_authn_dbd, so there are now three useful things you can do with Apache and a database: authentication, virtual hosts, and logging.
21 jun '08 see www.apachelounge.com/viewtopic.php?t=2545 !!
The Apache 2.2.8+ download from Apache Lounge now has DBD enabled, but note that the Apache 2.2.8+ Windows download from httpd.apache.org does not.
You can only use these with Apache 2.2.8+ from Apache Lounge, or else you must re-compile Apache 2.2.8+ yourself from source code per the instructions here.
A .zip file with the necessary binary files (ODBC driver, vhost module, logging module) is available at www.apachelounge.com/download .
They are compiled and ready to use with the Apache Lounge version of 2.2.8+.
Using ODBC to access a database actually requires two drivers. One from the database vendor (for example: the MySQL Connector/ODBC driver), and the Apache ODBC driver in the .zip file (apr_dbd_odbc.dll).
Here's a quick overview of how to set this all up with MySQL 5.0. You could use any other database which supports ODBC, but you would need to change the configuration and SQL statements accordingly.
-tom-
General DBD setup for MySQL 5.0 on Windows (mod_dbd)
1. You must have a working MySQL 5.0 database and you must install the MySQL ODBC driver (which MySQL calls "MySQL Connector/ODBC").
The MySQL ODBC driver can be downloaded here.
2.Unzip dbd_modules-1.0.x-w32.zip from www.apachelounge.com/download into your Apache 2.2.8+ directory. This will add the file apr_dbd_odbc.dll to your Apache \bin directory
and the two files mod_log_dbd.so and mod_vhost_dbd.so to your Apache \modules directory.
3. In [Start] [Administrative Tools] [DataSources (ODBC)] go to the [System DSN] tab and add a new datasource for your database.
If you run into trouble, the detailed docs are here.
4. Edit your httpd.conf file to enable DBD access. This example presumes you named your ODBC datasource "mySQLodbc" in step 3,
but you can use any name for your datasource.
| Code: | LoadModule dbd_module modules/mod_dbd.so
DBDriver odbc
DBDParams "DATASOURCE=mySQLodbc"
DBDKeep 10
DBDMax 10
DBDMin 10 |
See the mod_dbd docs and the ODBC driver docs for more details.
You can skip the Compiling and Installing apr_dbd_odbc section in the ODBC driver docs - it's already done.
Using DBD for authentication (mod_authn_dbd)
1. Create a MySQL table with usernames and passwords. The column names can be anything you like (we use 'User_ID' and 'Password' in this example).
The passwords can be either plain-text, SHA-encrypted (starting with {SHA}) , or MD5-encrypted (starting with $apr1$).
See the Apache docs here for details if you do not want to store plain-text passwords in your database.
| Code: | mysql> CREATE TABLE Apache_Users (User_ID VARCHAR(64), Password VARCHAR(64), PRIMARY KEY (User_ID));
mysql> INSERT INTO Apache_Users (User_ID, Password) VALUES ('tom', 'mYsEcReTpAsSwOrD');
mysql> INSERT INTO Apache_Users (User_ID, Password) VALUES ('mary', '{SHA}rAu31qbrao6MnY6E968FtBy5kis=') |
2. Edit httpd.conf to use DBD for user authentication via an SQL statement. Your SQL statement must contain exactly one ? where the actual username is to be substituted. Don't put the ? inside single-quotes, and don't put a semicolon at the end of your SQL statement
| Code: | LoadModule authn_dbd_module modules/mod_authn_dbd.so
<Directory "C:/Apache2/htdocs/private">
AuthType basic
AuthName "private area"
AuthBasicProvider dbd
AuthDBDUserPWQuery "SELECT Password FROM Apache_Users WHERE User_ID = ?"
Require valid-user
</Directory> |
See the Apache docs for mod_authn_dbd for more details.
Using DBD for Virtual Hosts (mod_vhost_dbd)
1. Create a MySQL table to describe your hostnames and their corresponding document root directories:
| Code: | mysql> CREATE TABLE Apache_roots (Hostname VARCHAR(64), Root VARCHAR(128), PRIMARY KEY (Hostname));
mysql> INSERT INTO Apache_roots (Hostname, Root) VALUES ('www.acme.com', 'C:/Apache2/htdocs/acme');
mysql> INSERT INTO Apache_roots (Hostname, Root) VALUES ('www.gadget.com', 'C:/Apache2/htdocs/gadget');
|
2. Edit httpd.conf to look up the document root using an SQL statement. Note that any hostname which is not in the table will still use the original directory set by your DocumentRoot directive in httpd.conf.
| Code: | LoadModule vhost_dbd_module modules/mod_vhost_dbd.so
DBDocRoot "SELECT Root FROM Apache_roots WHERE Hostname = ?" HOSTNAME
|
See the docs here for more details.
Using DBD for access logging (mod_log_dbd)
1. Examine the table of format codes in the Apache mod_log_config docs and decide which ones you want in your log. An example of 8 items is: %V - Server Name
%t - Time the request was received
%m - The request method
%>s - status of the original request
%u - Remote user
%a - Remote IP-address
%r - First line of request
%B - Size of response in bytes 2. Pay particular attention to date and time fields. They must be in a format acceptable to MySQL. %t won't work by itself, but the format code %{%Y-%m-%d %H:%M:%S}t will produce a timestamp acceptable to MySQL.
3. Create a MySQL table with the eight fields. You can name the columns as you prefer.
| Code: | mysql> CREATE TABLE Apache_log (Server VARCHAR(64), Tstmp TIMESTAMP, Method VARCHAR(16), Status CHAR(3), User VARCHAR(64), Ip VARCHAR(30), Request VARCHAR(2048), Bytes INTEGER);
|
4. Edit httpd.conf to use DBD for logging. This requires a CustomLog directive immediately followed by a matching DBDLog directive to the same filename. The CustomLog directive must have a comma-separated list of format codes. The SQL statement must have one ? for each format code. There are eight format-codes and eight column names and eight ?s in this example:
| Code: | LoadModule log_dbd_module modules/mod_log_dbd.so
CustomLog logs/access.sql "%V, %{%Y-%m-%d %H:%M:%S}t, %m, %>s, %u, %a, %r, %B"
DBDLog logs/access.sql "INSERT INTO Apache_log \
(Server,Tstmp,Method,Status,User,Ip,Request,Bytes) \
VALUES (?, ?, ?, ?, ?, ?, ?, ?)"
|
5. Remove any other CustomLog directives from your httpd.conf file (unless you also want access logging to a file in addition to logging to the database).
See the docs here for more details. |
|
| Back to top |
|
Mitron
Joined: 04 Jan 2006
Posts: 63
|
| Posted: Mon 28 Jan '08 2:44 Post subject: |
|
|
Is there a way to do this without ODBC, i.e. via the method in the ASF documentation substituting MySQL for the PostgreSQL example or is using the apr_dbd_mysql still considered taboo since MySQL is GPL?
| Code: |
#Database Management
#Use the MySQL driver
DBDriver mysql
#Connection string: database name and login credentials
DBDParams "dbname=htpasswd user=apache password=xxxxxx"
#Parameters for Connection Pool Management
DBDMin 1
DBDKeep 2
DBDMax 10
DBDExptime 60
#Authentication Section
<Directory /usr/www/myhost/private>
#mod_auth configuration for authn_dbd
AuthType Basic
AuthName "My Server"
AuthBasicProvider dbd
#authz configuration
Require valid-user
#SQL query to verify a user
#(note: DBD drivers recognise both stdio-like %s and native syntax)
AuthDBDUserPWQuery "select password from authn where username = %s"
</Directory>
|
It would be nice to have the direct MySQL support using mod_dbd instead of going through ODBC, although I can think of a few uses for that as well, i.e. Excel support.  |
|
| Back to top |
|
tdonovan
Moderator
Joined: 17 Dec 2005
Posts: 611
Location: Milford, MA, USA
|
| Posted: Mon 28 Jan '08 6:09 Post subject: |
|
|
Yes.
It's been a few months since I tried the three modules (mod_authn_dbd, mod_vhost_dbd, and mod_log_dbd) with the MySQL, PostgreSQL, and SQLite DBD drivers - but they worked OK with them when I tried. SQLite is inappropriate for logging (it locks the whole table on writes), but was excellent for authentication and vhost.
Using apr_dbd_mysql, you will give up a few things: no SQL NULL values, less error reporting, and slightly less performance - but nevertheless it did function OK when I tried it. I didn't run it for long, so I may have missed something. A few additional bugs may also have been fixed since I last tried it.
The source files for apr_dbd_mysql, apr_dbd_pgsql, apr_dbd_sqlite2, and apr_dbd_sqlite3 are all in the httpd-2.2.8\srclib\apr-util\dbd\ directory if you download the Apache 2.2.8 source. I remember that they were a real pain to build correctly on Windows because there are no Windows Makefiles, but I could probably dig up my notes if you want to try apr_dbd_mysql.
I don't think there are any remaining license issues with the MySQL driver. There are certainly none if you just plan to run it, rather than distribute it.
I'm curious - why would you want to avoid ODBC? Especially on Windows where it is always available. Excel doesn't seem too useful in an Apache environment. MSAccess surprised me. I expected very little, but its performance was not bad (as long as I stayed out of the database while Apache was running...)
-tom- |
|
| Back to top |
|
Mitron
Joined: 04 Jan 2006
Posts: 63
|
| Posted: Mon 28 Jan '08 13:04 Post subject: |
|
|
| tdonovan wrote: |
I don't think there are any remaining license issues with the MySQL driver. There are certainly none if you just plan to run it, rather than distribute it.
|
Yeah, this is just for my own setup, although I may write a couple mods for phpbb that may use mod_dbd. I'm mainly interested in mod_dbd simply for authentication using the database instead of the .htpasswd text files.
| tdonovan wrote: |
I'm curious - why would you want to avoid ODBC? Especially on Windows where it is always available. Excel doesn't seem too useful in an Apache environment. MSAccess surprised me. I expected very little, but its performance was not bad (as long as I stayed out of the database while Apache was running...)
-tom- |
Simply because I'm not currently using it for anything on my servers. I know its there, I've just never had to configure it for anything other than Access and Excel support. I stopped using Access once I moved to MySQL.
I would never use Excel in an Apache environment, but I had a client that wants to pull the user database up in Excel via an ODBC connection but I may be talking apples and oranges here. |
|
| Back to top |
|
admin
Site Admin
Joined: 15 Oct 2005
Posts: 692
|
| Posted: Tue 11 Mar '08 0:02 Post subject: |
|
|
Wow, this is a winner. I use MySQL Authentication without any issues.
Thanks! Tom
Steffen |
|
| Back to top |
|
sandwichmixto
Joined: 06 Jun 2009
Posts: 1
|
| Posted: Sat 06 Jun '09 0:05 Post subject: complete setup documented |
|
|
I've tried to condense the problems I had and posted my solutions here on http://www.seungpyo.com/weblog/?p=93. Tremendous amount of thanks to tdonovan for being one of the few folks who have posted stuff on this.
I have to say it has been an extreme pain to try and get mod_dbd working right on apache. |
|
| Back to top |
|
dreuzel
Joined: 30 Jan 2006
Posts: 16
|
| Posted: Fri 19 Mar '10 12:59 Post subject: |
|
|
What about groups ???
We are always talking about Authentication-Authorisation
Authentication is just the first step ????
Where to find the rest ???
or do we skip SQL for a 1000 users ??? or handle all individualy by name ? |
|
| Back to top |
|
tdonovan
Moderator
Joined: 17 Dec 2005
Posts: 611
Location: Milford, MA, USA
|
| Posted: Sun 21 Mar '10 21:13 Post subject: |
|
|
It is usually handled in SQL. For example, if you have two secure areas, and two tables in your databse (one table for users & passwords, the other table for users & groups):
| Code: |
<Directory F:/Secure1 >
...
AuthDBDUserPWQuery "SELECT password FROM User_table U JOIN Group_table G \
WHERE U.user = %s AND U.user = G.user AND G.group = 'secure1'"
...
</Directory>
<Directory F:/Secure2 >
...
AuthDBDUserPWQuery "SELECT password FROM User_table U JOIN Group_table G \
WHERE U.user = %s AND U.user = G.user AND G.group = 'secure2'"
...
</Directory>
|
Of course, since it is done in SQL - there are many different ways to do it. This is just one example.
-tom-
p.s. You might want to read Steven Raymond's classic article How To Ask Questions The Smart Way |
|
| Back to top |
|
|
|
|
|
|
