Level: Intermediate Helmut K. C. Tessarek (tessarek@ca.ibm.com), Accredited IT specialist, IBM
08 Jul 2004
Updated 15 Jan 2008 This article shows you step by step how to compile and configure mod_auth(nz)_ibmdb2 (an Apache authentication module) using IBM® DB2® Universal Database™ (UDB) as the database for storing user and group information. It explains the creation and use of the User Defined Functions (UDFs) for generating passwords in DB2. The UDFs provided are compatible to the functions that are used in Apache's htpasswd utility.
In this article, you will:
- Compile the module
- Configure and use the module: the new directives, a practical sample, caching
- Compile the UDFs
Introduction
Since the first release of this article, the Apache webserver has changed quite a bit and so has the module. Apache 2.2 introduced a new authentication backend provider scheme which explains the change of the module name for Apache versions >= 2.2. Although Apache 2.2 provides a SQL database API, called the DBD framework, it makes sense to use mod_authnz_ibmdb2, which allows you to cache the user and group information and it validates not only the hashes used by Apache but also regular MD5 hashes and plain text passwords. Regular "not seeded" MD5 hashes are very often used in web applications.
Active development of mod_auth_ibmdb2 for Apache 1.x has been stopped, but you can find the latest version for Apache 1.x in the new package 'mod_auth_ibmdb2 (Apache 1.x)' on sourceforge.net.
To begin, you will need access to a DB2 system. It does not matter if DB2 is installed on the same machine as Apache or if the database is located on another machine. If the database is located on another machine, make sure that the application development client is installed and configured on the machine that is running Apache. For information on installing DB2, please refer to Speed-start your Linux app 2003: Installing DB2 Version 8.1.
The module and the UDFs are written in C and should compile and run on all platforms that have the proper include files and libraries installed.
This article focuses on the Linux platform. The modules have been developed on Linux.
Before you begin
Download the latest source codes from the mod_auth(nz)_ibmdb2 Web site, which are at the time of this writing, mod_authnz_ibmdb2-1.26.tar.gz (Apache 2.2), mod_auth_ibmdb2-1.10.tar.gz (Apache 2.0) and db2-auth-udfs-1.3.tar.gz.
Compile the module
Open a terminal and log in as root. Next, change to a directory where you can extract the mod_authnz_ibmdb2-1.26.tar.gz. (in this example I will use the directory /ext).
[root@atvie01s ext]# tar -xzf mod_authnz_ibmdb2-1.26.tar.gz
[root@atvie01s ext]# cd mod_authnz_ibmdb2
[root@atvie01s mod_authnz_ibmdb2]# ls -l
total 80
-rw-r--r-- 1 tessus tessus 13521 2007-03-21 22:39 caching.h
-rw-r--r-- 1 tessus tessus 778 2008-01-08 05:47 CHANGES
-rw-r--r-- 1 tessus tessus 1514 2008-01-08 05:47 INSTALL
-rwxr-xr-x 1 tessus tessus 940 2007-03-21 06:50 makemod
-rwxr-xr-x 1 tessus tessus 993 2008-01-08 05:16 makemod.bat
drwxr-xr-x 3 tessus tessus 16 2008-01-08 05:49 man
-rw-r--r-- 1 tessus tessus 36003 2008-01-08 03:40 mod_authnz_ibmdb2.c
-rw-r--r-- 1 tessus tessus 4022 2008-01-07 00:44 mod_authnz_ibmdb2.h
-rw-r--r-- 1 tessus tessus 4218 2008-01-08 05:47 README
|
You will find a makemod script within this directory that we will use to build the module (shared object) for Apache. For this you have to check some parameters in the script. Use your favorite editor for changing the following parameters to the appropriate values for your environment.
DB2PATH=/home/db2inst1/sqllib
APXS=/usr/local/apache/bin/apxs
Set DB2PATH to the directory where DB2 UDB is accessed. This is usually the instance home directory. Set APXS to the path that points to your apxs binary. The apxs binary is usually installed in the /<your apache home>/bin directory.
After changing the above settings, run the following script:
[root@atvie01s mod_authnz_ibmdb2]# ./makemod
|
Next, add the following line to your httpd.conf:
Apache >= 2.2:
LoadModule authnz_ibmdb2_module modules/mod_authnz_ibmdb2.so
|
Apache 2.0.x and Apache 1.3.x:
LoadModule ibmdb2_auth_module modules/mod_auth_ibmdb2.so
|
You may also need to add the following line when using Apache 1.3.x:
AddModule mod_auth_ibmdb2.c
|
If the sqlcli1.h header file or the DB2 library libdb2.so cannot be found, it is likely that you have not set the DB2PATH correctly. In this situation search your system for the two above-mentioned files.
Configure and use the module
The new directives
With the module comes several new directives for Apache to control the behavior of the authentication process. Here is a list of the new directives, their default values and a description.
Table 1. Listing of directives
Directive
|
Default Value
|
Description
|
|
|
| | AuthIBMDB2User |
| User for connecting to the DB2
database
| | AuthIBMDB2Password |
| Password for connecting to the
DB2 database
| AuthIBMDB2Database
|
| Database name
| AuthIBMDB2UserTable
|
| Name of the user table. If
not fully qualified, the user that was used to connect to the database
is used as the schema name.
| AuthIBMDB2GroupTable
|
| Name of the group table.
If not fully qualified, the user that was used to connect to the
database is used as the schema name. | AuthIBMDB2NameField
| username
| Name of the username column
within the user and group table
| AuthIBMDB2GroupField
| groupname
| Name of the groupname column
within the group table | AuthIBMDB2PasswordField
| password
| Name of the password column
within the user table | AuthIBMDB2CryptedPasswords
| on
| If AuthIBMDB2CryptedPasswords is
"On," then the passwords are stored encrypted. You do not have to
specify what kind of encryption was used. Valid encryption methods are:
normal md5 hash (32 chars as in php), seeded md5 value (as generated
with Apache's htpasswd utility or as in /etc/shadow), crypt. If "Off," plain text passwords are used. For generating passwords within DB2, see
the UDFs section in this article.
| AuthIBMDB2KeepAlive
| on
| If AuthIBMDB2KeepAlive is "On,"
then the server instance will keep the IBMDB2 server connection open.
| AuthIBMDB2Authoritative
| on
| If AuthIBMDB2Authoritative is
"Off," then iff the user is not found in the database, let other auth
modules try to find the user.
| AuthIBMDB2NoPasswd
| off
| If AuthIBMDB2NoPasswd is "On,"
then any password the user enters will be accepted as long as the user
exists in the database.
| AuthIBMDB2UserCondition
|
| Can be used to restrict queries
made against the user table. The value should be a string that you want
to be added to the end of the where-clause when querying the table. For
example, if your user table has an "active" field and you only want
users to be able to login if that field is 1, you could use a directive
like this: AuthIBMDB2UserCondition active=1
| AuthIBMDB2GroupCondition
|
| Can be used to restrict queries
made against the group table. The value
should be a string that you want to be added to the end of the
where-clause when querying the table. For example, if your group table has an
"active" field and you only want users to be able to login if that
field is 1, you could use a directive like this:
AuthIBMDB2GroupCondition active=1 | AuthIBMDB2UserProc
|
| Name of the stored procedure for user authentication
| AuthIBMDB2GroupProc
|
| Name of the stored procedure for group authentication
| AuthIBMDB2Caching
| off
| If AuthIBMDB2Caching is "On,"
the user credentials are cached in a file.
| AuthIBMDB2GroupCaching
| off
| If AuthIBMDB2GroupCaching is "On,"
group information is cached in a file.
| AuthIBMDB2CacheFile
| /tmp/auth_cred_cache
| Defines the location and name of
the cache file.
| AuthIBMDB2CacheLifetime
| 300
| Sets the expiration timeout in
seconds of the cached elements.
|
A practical sample
In this example, we will use /var/www as the Web root and /data/private for the /private alias. To connect to the database, enter the user name tessarek and the password helmut. The name of the database is auth.
If you need to protect the Web location http://yourserver/private using the new installed mod_auth(nz)_ibmdb2 module:
First, you need to create the two tables in DB2 UDB that will be used for storing the users and the groups.
db2 => connect to auth user tessarek using helmut
Database Connection Information
Database server = DB2/LINUX 9.5.0
SQL authorization ID = TESSAREK
Local database alias = AUTH
db2 => CREATE TABLE USERS ( USERNAME VARCHAR(40) NOT NULL, PASSWORD VARCHAR(40) )
db2 => ALTER TABLE USERS ADD PRIMARY KEY (USERNAME)
db2 => CREATE TABLE GROUPS ( USERNAME VARCHAR(40) NOT NULL, GROUPNAME VARCHAR(40) NOT NULL )
db2 => ALTER TABLE GROUPS ADD PRIMARY KEY (USERNAME,GROUPNAME)
|
The tables do not need to be created as in the example above. They could be views or they could have additional columns. The point that has to be taken into account is that there must be a username and a password column in the usertable and the username column must be unique. Furthermore, there must be a username and a groupname column in the grouptable and the username and groupname columns must be unique.
You can choose whatever names you want for the username, groupname, and password columns.
Next, you need to insert user and group information in the newly created tables. If you don't want to do any of the following:
- Use clear text passwords
- Use Apache's htpasswd to create a new encrypted password
- Copy the encrypted password from an existing userfile
You will have to install the UDFs as described in the next section.
db2 => insert into users (username,password) values ('test', apr_md5('testpwd'))
db2 => insert into groups (username,groupname) values ('test','admin')
db2 => select * from users
USERNAME PASSWORD
---------------------------------------- ----------------------------------------
test $apr1$bZGrA...$Ic7E68bhwdDnpX6XG/w8i/
1 record(s) selected.
db2 => select * from groups
USERNAME GROUPNAME
---------------------------------------- ----------------------------------------
test admin
1 record(s) selected.
|
Note: apr_md5 is a User Defined Function that is explained in the UDFs section of this article.
Finally, you need to edit the httpd.conf file and add the following lines. For the changes to be effective, the Web server has to be restarted.
Alias /private "/data/private/"
<Directory "/data/private">
AuthName "DB2 Authentication for private"
AuthType Basic
AuthBasicProvider ibmdb2 # Apache >= 2.2.x only
AuthIBMDB2User tessarek
AuthIBMDB2Password helmut
AuthIBMDB2Database auth
AuthIBMDB2UserTable users
AuthIBMDB2NameField username
AuthIBMDB2PasswordField password
AuthIBMDB2CryptedPasswords On
AuthIBMDB2KeepAlive On
AuthIBMDB2Authoritative On
AuthIBMDB2NoPasswd Off
AuthIBMDB2GroupTable groups
AuthIBMDB2GroupField groupname
require group admin
AllowOverride None
</Directory>
|
You also could have omitted the parameters AuthIBMDB2NameField, AuthIBMDB2PasswordField, AuthIBMDB2CryptedPasswords, AuthIBMDB2KeepAlive, AuthIBMDB2Authoritative, AuthIBMDB2NoPasswd because we are using the default values.
If the tables are declared without a schema name, the username that is used for connecting to the database is taken for the schema name.
Point your Web browser to http://yourserver/private and see what happens. If everything works, you should see something like in Figure 1.
Figure 1. Protected area
Caching
For sites with a large number of requests it makes sense to cache the user credentials so that the database requests are minimized. The caching is turned off by default so you have to enable caching with the directives AuthIBMDB2Caching On and AuthIBMDB2GroupCaching On. The cache file is created in the /tmp filesystem, since usually everybody has write access to that filesystem. You can change the path to the cache file with AuthIBMDB2CacheFile. The lifetime of the user credentials within the cache file is set with AuthIBMDB2CacheLifetime. By default the lifetime is set to 300 seconds (5 minutes).
If you want to use caching in RAM, create a RAM disk and point AuthIBMDB2CacheFile to that new filesystem. Make sure that the user, which is used to run Apache, has read and write access to that filesystem.
You can create a RAM disk in Linux with the following command (this RAM disk will be 5 MB in size):
mount -t tmpfs -o size=5M,nr_inodes=2k,mode=770 tmpfs /mnt/ramfs
|
Stored Procedures
The module supports stored procedures to retrieve the user and group information from the database. The reasons for using stored procedures are ease of administration and performance. They must follow a certain parameter format.
The stored procedure for user authentication expects one input parameter, the username, and one output value, the password for that user:
PROCEDURE user_procedure_name ( IN VARCHAR, OUT VARCHAR )
The stored procedure for group authentication expects one input parameter, the user, and must return an open cursor to the resultset:
PROCEDURE group_procedure_name ( IN VARCHAR )
Here is an example of two stored procedures which are modelled on the table configuration in the practical sample.
File 1: sp.sql
CREATE PROCEDURE user_sp (IN v_username VARCHAR(40), OUT v_password VARCHAR(40))
LANGUAGE SQL
BEGIN
SELECT password INTO v_password FROM users WHERE username = v_username;
END@
CREATE PROCEDURE group_sp (IN v_username VARCHAR(40))
LANGUAGE SQL
DYNAMIC RESULT SETS 1
BEGIN
DECLARE res CURSOR WITH RETURN FOR
SELECT groupname FROM groups WHERE username = v_username;
OPEN res;
END@
|
You can install the stored procedures by running the following commands as the instance user:
[db2inst1@atvie01s ~]$ db2 connect to auth user tessarek using helmut
[db2inst1@atvie01s ~]$ db2 -td@ -f sp.sql
|
If you want to use the stored procedures, you have to edit the httpd.conf file and to restart the Web server again.
Alias /private "/data/private/"
<Directory "/data/private">
AuthName "DB2 Authentication for private"
AuthType Basic
AuthBasicProvider ibmdb2 # Apache >= 2.2.x only
AuthIBMDB2User tessarek
AuthIBMDB2Password helmut
AuthIBMDB2Database auth
AuthIBMDB2UserProc user_sp
AuthIBMDB2GroupProc group_sp
require group admin
AllowOverride None
</Directory>
|
Compiling the UDFs
Open a terminal and log in as DB2 UDB instance user. Then change to a directory where you are going to extract the db2-auth-udfs-1.3.tar.gz. (in this example I will use the directory /ext).
[db2inst1@atvie01s ~]$ cd /ext
[db2inst1@atvie01s ext]$ tar -xzf db2-auth-udfs-1.3.tar.gz
[db2inst1@atvie01s ext]$ db2-auth-udfs
[db2inst1@atvie01s db2-auth-udfs]$ ls -l
total 56
-rw-rw-r-- 1 db2inst1 db2grp1 596 2008-01-04 09:03 CHANGES
-rw-rw-r-- 1 db2inst1 db2grp1 102 2006-02-17 11:03 drop_udfs.ddl
-rw-rw-r-- 1 db2inst1 db2grp1 4407 2008-01-04 08:58 hash.c
-rw-rw-r-- 1 db2inst1 db2grp1 55 2007-03-06 03:52 hash.def
-rw-rw-r-- 1 db2inst1 db2grp1 3550 2007-02-27 03:36 hash.h
-rw-rw-r-- 1 db2inst1 db2grp1 892 2007-03-06 03:52 INSTALL
-rwxrwxr-x 1 db2inst1 db2grp1 740 2007-03-06 03:52 maketest
-rw-rw-r-- 1 db2inst1 db2grp1 377 2007-03-06 03:45 maketest.bat
-rwxrwxr-x 1 db2inst1 db2grp1 1218 2007-03-27 18:27 makeudf
-rw-rw-r-- 1 db2inst1 db2grp1 532 2007-03-06 03:45 makeudf.bat
-rw-rw-r-- 1 db2inst1 db2grp1 1131 2007-03-06 03:52 README
-rw-rw-r-- 1 db2inst1 db2grp1 1020 2008-01-04 08:57 reg_udfs.ddl
-rw-rw-r-- 1 db2inst1 db2grp1 595 2007-02-27 03:40 test_hash.c
|
You will find a makeudf script within this directory that we will use to build the UDFs. For this you have to change a parameter in the script. Use an editor for changing the following parameter to the appropriate value for your environment.
DB2PATH=/home/db2inst1/sqllib
Set DB2PATH to the directory where DB2 is accessed. This is usually the instance home directory.
After changing the above parameter, run the script:
[db2inst1@atvie01s db2-auth-udfs]$ ./makeudf
|
To register the UDFs, connect to your database and run the script to register the UDFs:
[db2inst1@atvie01s db2-auth-udfs]$ db2 connect to auth user tessarek using helmut
Database Connection Information
Database server = DB2/LINUX 9.5.0
SQL authorization ID = TESSAREK
Local database alias = AUTH
[db2inst1@atvie01s db2-auth-udfs]$ db2 -tvf reg_udfs.ddl
CREATE FUNCTION md5(VARCHAR(120))
RETURNS VARCHAR(32)
NOT FENCED
DETERMINISTIC
NO SQL
NO EXTERNAL ACTION
LANGUAGE C
RETURNS NULL ON NULL INPUT
PARAMETER STYLE SQL
EXTERNAL NAME 'hash!md5'
DB20000I The SQL command completed successfully.
CREATE FUNCTION apr_md5(VARCHAR(120))
RETURNS VARCHAR(37)
NOT FENCED
NO SQL
NO EXTERNAL ACTION
LANGUAGE C
RETURNS NULL ON NULL INPUT
PARAMETER STYLE SQL
EXTERNAL NAME 'hash!aprmd5'
DB20000I The SQL command completed successfully.
CREATE FUNCTION apr_crypt(VARCHAR(120))
RETURNS VARCHAR(13)
NOT FENCED
NO SQL
NO EXTERNAL ACTION
LANGUAGE C
RETURNS NULL ON NULL INPUT
PARAMETER STYLE SQL
EXTERNAL NAME 'hash!aprcrypt'
DB20000I The SQL command completed successfully.
CREATE FUNCTION apr_sha1(VARCHAR(120))
RETURNS VARCHAR(33)
NOT FENCED
DETERMINISTIC
NO SQL
NO EXTERNAL ACTION
LANGUAGE C
RETURNS NULL ON NULL INPUT
PARAMETER STYLE SQL
EXTERNAL NAME 'hash!aprsha1'
DB20000I The SQL command completed successfully.
[db2inst1@atvie01s db2-auth-udfs]$ db2 connect reset
|
The UDFs are written in ANSI C and are using the APR libraries. They should compile and run on all platforms. You can either use the bldrtn script in your sqllib/samples/c directory as a good start, or you can modify the makeudf script.
Description of the UDFs
Here is a list of the UDFs, an example output, and a description.
Table 2. Listing of the UDFs
UDF name
|
Example Output
|
Description
|
|
|
| | md5 | 342df5b036b2f28184536820af6d1caf | MD5 hash. The MD5 function is compatible to the PHP MD5 function. | | apr_md5 | $apr1$bhX6Z/..$kAxtFQozYsZhgNgqVnJd0. | Seeded MD5 hash. The apr_md5 is compatible to the Apache function that is used in the htpasswd utility. | | apr_crypt | bhqWHYBAJAQ2w | UNIX® crypt. The apr_crypt is compatible to the Apache function that is used in the htpasswd utility. | | apr_sha1 | {SHA}mO8HWOaqxvmp4Rl1SMgZC3LJWB0= | SHA1 algorithm. The apr_sha1 is compatible to the Apache function that is used in the htpasswd utility. |
Debugging
If you set LogLevel debug in the httpd.conf file, you will get detailed information about what is happening during the authentication process. Here are two samples of the debugging text that was generated during the practical sample.
Sample 1. Debugging the authentication process with caching turned off
[notice] Apache/2.2.6 (Unix) mod_ssl/2.2.6 PHP/5.2.5 mod_authnz_ibmdb2/1.26 configured --
resuming normal operations
[info] Server built: Sep 8 2007 11:57:34
[debug] prefork.c(991): AcceptMutex: sysvsem (default: sysvsem)
[debug] mod_authnz_ibmdb2.c(1134): begin authenticate for user=[test], uri=[/private/]
[debug] mod_authnz_ibmdb2.c(523): begin get_ibmdb2_pw()
[debug] mod_authnz_ibmdb2.c(232): DB connection is dead or nonexistent;
create connection
[debug] mod_authnz_ibmdb2.c(235): allocate an environment handle
[debug] mod_authnz_ibmdb2.c(690): query=[SELECT rtrim(password) FROM users
WHERE username='test']
[debug] mod_authnz_ibmdb2.c(692): allocate a statement handle
[debug] mod_authnz_ibmdb2.c(698): prepare the statement
[debug] mod_authnz_ibmdb2.c(715): execute the statement
[debug] mod_authnz_ibmdb2.c(772): fetch each row, and display
[debug] mod_authnz_ibmdb2.c(788): get data from query resultset
[debug] mod_authnz_ibmdb2.c(795): password from database=
[$apr1$bZGrA...$Ic7E68bhwdDnpX6XG/w8i/]
[debug] mod_authnz_ibmdb2.c(797): call SQLFetch() (point to next row)
[debug] mod_authnz_ibmdb2.c(803): free statement handle
[debug] mod_authnz_ibmdb2.c(309): keepalive on; do not disconnect from database
[debug] mod_authnz_ibmdb2.c(813): end get_ibmdb2_pw()
[debug] mod_authnz_ibmdb2.c(877): begin get_ibmdb2_groups()
[debug] mod_authnz_ibmdb2.c(227): DB connection is alive; re-using
[debug] mod_authnz_ibmdb2.c(913): query=[SELECT rtrim(groupname) FROM groups
WHERE username='test']
[debug] mod_authnz_ibmdb2.c(915): allocate a statement handle
[debug] mod_authnz_ibmdb2.c(921): prepare the statement
[debug] mod_authnz_ibmdb2.c(938): execute the statement
[debug] mod_authnz_ibmdb2.c(1010): fetch each row, and display
[debug] mod_authnz_ibmdb2.c(1035): get data from query resultset
[debug] mod_authnz_ibmdb2.c(1050): group #1 from database=[admin]
[debug] mod_authnz_ibmdb2.c(1052): call SQLFetch() (point to next row)
[debug] mod_authnz_ibmdb2.c(1058): free statement handle
[debug] mod_authnz_ibmdb2.c(309): keepalive on; do not disconnect from database
[debug] mod_authnz_ibmdb2.c(1068): end get_ibmdb2_groups()
|
Sample 2. Debugging the authentication process with caching turned on
[notice] Apache/2.2.6 (Unix) mod_ssl/2.2.6 PHP/5.2.5 mod_authnz_ibmdb2/1.26 configured --
resuming normal operations
[info] Server built: Sep 8 2007 11:57:34
[debug] prefork.c(991): AcceptMutex: sysvsem (default: sysvsem)
[debug] mod_authnz_ibmdb2.c(1134): begin authenticate for user=[test], uri=[/private/]
[debug] caching.h(211): user [test] - [$apr1$bZGrA...$Ic7E68bhwdDnpX6XG/w8i/]
found in cache
[debug] caching.h(502): groups for user [test] found in cache
|
Binary releases
Although there are scripts for building the modules and the UDFs on Windows, most Windows users don't have a development environment installed to compile the packages.
You can download the latest binaries for Windows 32bit in the binary releases package section on the website of mod_auth(nz)_ibmdb2.
Resources Learn
Get products and technologies
- Download the source code for mod_authnz_ibmdb2 used in this article.
- Download the source code for mod_auth_ibmdb2.
- Download the source code for the UDFs used in this article.
- Download the documentation as a PDF file.
- Download the binaries for Windows 32bit.
-
Build your next development project with
IBM
trial software, available for download directly from developerWorks.
Discuss
About the author  | 
| | Helmut K. C. Tessarek works at the IBM Toronto Lab in the area of DB2 performance and development. Before that he worked as an IBM DB2 specialist in Software Group Services at IBM Austria. He is the author of mod_auth(nz)_ibmdb2. You can reach Helmut at tessarek@ca.ibm.com. |
Rate this page
| 
