 | Level: Intermediate Gene Kligerman (gene_kligerman@ca.ibm.com), DB2 Project Office, IBM
28 Feb 2008 This article describes the design and development of a DB2® for Linux®, UNIX®, and Windows® security plug-in that retrieves
user ID, group, and group membership information from a local or remote DB2 database.
Use of this plug-in allows for the development of database applications that do not
rely on an external authentication mechanism, such as an operating system.
Accompanying this article is a working implementation of the security plug-in for Windows
operating system. This article is a continuation of the series of articles about
DB2 security.
Introduction
Part 2 of this series on DB2 security introduced the DB2 security plug-in
infrastructure. Part 10 of this series described how customized
security plug-ins can be deployed. This article describes the design
and development process of a specific type of security plug-in, one that
uses a DB2 database to store authentication information.
This article describes some of the technical questions that may arise
during the plug-in development process and describes how these questions can
be answered. The DB2 security plug-in that is the result of this
development process is available in the Download section, in
both the binary and the source code formats,
so that it can be learned from and extended by interested developers.
Background on storing authentication information in a DB2 database
By default, DB2 ships with security plug-ins that perform authentication using the
following authentication mechanisms:
- Operating system (default)
- Lightweight Directory Access Protocol (LDAP)
- Kerberos
While the above authentication mechanisms are fine for most DB2 deployment
scenarios, some DB2 users and application developers have long been interested
in the capability of a DB2 system to perform authentication tasks solely within
DB2. That is, storing all information about DB2 users and groups within a DB2
database. The resulting implementation, along with this article that describes
the design process, has to deliver on the following set of requirements:
- All authentication information (users, passwords, and group information) should
be stored in the database.
- Support the storage of authentication in a local DB2 database (a database
that is part of a DB2 instance that uses the security plug-in) or in a remote
DB2 database (a database in another instance that may be on a different
system). Support for a local database would allow the development of DB2 databases
and surrounding applications that are entirely self-contained (ship
predefined lists of users and groups). Support for remote databases would allow
centralized management of authentication information for several DB2 databases
located on separate servers without requiring the deployment of an external
service, such as Kerberos or LDAP.
- All authentication tasks should be done through SQL, so that external DB2
applications can be written to manage this information either locally or remotely.
- Authentication information should be stored in a reasonably secure fashion that is
consistent with the DB2 security philosophy,
- The implementation should not use internal APIs and be available in
source code along with design information, so that it can be readily extended
by interested parties who are facing the task of building a similar solution.
 |
Plug-in Installation and configuration overview
A good way to understand the functionality of the DB2 security plug-in described
in this article is by reviewing the highlights of the plug-in
installation and configuration.
The first step is to specify in the plug-in configuration file the name
of the authentication database and the user connection information. The
plug-in uses this information to connect to the database for all subsequent
authentication requests. The authentication database can be either local or
remote. If the database is remote, for improved security it is recommended
to configure the remote
database connection to use data encryption — that is, turn on the data_encrypt
configuration parameter.
The second step is to move the plug-in configuration file along with
the plug-in binaries to the DB2 server plug-in installation directories where
the DB2 engine is configured to find them.
The third step is to create, in the authentication database, a schema and
the related database objects within that schema. These objects are tables
containing authentication information and stored procedures encapsulating the
server logic to operate on the database tables.
The fourth step is to populate the tables created above with the
authentication information about the first user, meaning the user ID and password
information that corresponds to the user ID specified in the plug-in
configuration file.
With the steps above completed, all that remains is for the DB2 server
administrator to change the database manager configuration parameters to
indicate that the DB2 authentication security plug-in should be used instead
of the default operating system plug-in.
After re-starting the DB2 database instance, the new plug-in is used.
Of course, having a single authentication user ID in the database is not enough.
At this point, the administrator can create new user accounts in the
authentication database, create new groups, and assign users to be members
of groups.
The administrator may also allow additional users to be administrators
of the authentication information. This is done simply by using SQL to grant
appropriate privileges on the database tables and stored procedures.
The complete documentation on installation, configuration, and usage of the
plug-in is available as part of the plug-in download.
DB2 authentication plug-in process model
The starting point for the new DB2 security plug-in is a sample DB2 security
plug-in called combined.c that is shipped in the sqllib/samples/security
directory of the installed DB2. combined.c is a very simple plug-in that
reads authentication information (user ID, clear-text password, and group membership)
from a predefined flat file. So if you were to trivialize the task ahead,
it could be summarized as follows: Replace file access calls by an SQL interface
that would access information in a DB2 database instead of a flat file.
Realization of the plug-in development is of course not trivial.
The key plug-in design influencer is the fact that the plug-in operates as part
of the DB2 engine process db2syscs.exe. It is not possible to
load the DB2 application support library within the context of the DB2 engine
process, and consequently all SQL interactions must be done in a process that
is separate from db2syscs.exe.
Consequently the new db2auth plug-in was implemented using separate
processes. The file db2auth.c contains the code that is linked with the
DB2 engine process. As part of plug-in initialization, db2auth.c creates
a bi-directional named pipe. It then creates and launches a separate daemon
process that performs all of the SQL interactions with a DB2 database.
The code for the process is implemented in the file db2authDaemon.c.
The daemon process receives authentication requests from the DB2 engine
through the named pipe, queries the DB2 database using DB2 call-level interface (CLI)
API, and then returns
the result back to the db2auth plug-in through the same pipe mechanism.
That works fine for a single request. However, DB2 for Windows is a
multi-threaded engine (so is DB2 for Unix and Linux starting with Version 9.5).
Consequently the plug-in must correctly handle the execution of multiple
concurrent engine threads that are making authentication requests.
The solution is to serialize all interactions
between the plug-in and the daemon so that only one authentication request
is handled at a time. On the Windows platform this is accomplished by
using the CreateMutex API.
What about the performance of serializing authentication requests over two named
pipes (one for all user authentication requests and another one for all group
authentication requests)? An unscientific
performance analysis of the resulting implementation demonstrated that the
plug-in is capable of handling over 1,000 connection requests per minute,
with a potential for even higher performance. This test was performed
on a Windows XP dual-core laptop, using DB2 V9.1 code base, simulating
several DB2 command line processor job streams that consisted exclusively of
connect statements.
The db2authDaemon process is activated as part of plug-in
initialization (for example, during db2start processing). The daemon
process is terminated either during normal plug-in termination
(message in a named pipe to terminate) or if it is signaled that the parent
process has terminated.
Having covered the process model, you are now ready to delve deeper
into plug-in functionality.
Plug-in operation
When the first authentication request comes in, the
DB2 plug-in establishes a connection to the DB2 database that
contains the authentication information. In order to eliminate the
possibility of recursion, the plug-in must have sufficient information
for connection, such as the name (database alias) of the DB2 database, the user ID
of authorized database user, and (optionally) the connection password.
The DB2 instance administrator has to provide this information in a flat file called
db2auth_key.txt. If the DB2 authentication database is located
within the same DB2 instance as the plug-in, then password should not be
specified so that an implicit connection takes place. However, if the database
is in a different instance (most likely on a different server) then a connection
password must be provided. Storing the password in clear text in a file
is a potential security exposure that the DB2 administrator should guard against.
There are easy ways to safeguard the contents of this file on a Windows platform. For
example, you can change file property so that it is hidden and encrypted while being
accessible to the DB2 instance owner processes, as shown in Figure 1.
Figure 1. Securing the connection information file
The only other information specified in this
flat file is the name of the configured DB2 System Administration group
(through the DB2 Database Manager SYSADM_GROUP configuration parameter). Doing
so ensures that the DB2 instance owning ID has SYSADM privileges.
Since only one group name can be specified in the file, this means
that with plug-in enabled, group membership checks for the DB2 instance owner
ID returns only the name of this one group. As currently implemented,
the deployment of the plug-in configures SYSADM_GROUP configuration
parameter to a value specified during the plug-in deployment phase. Doing
so provides a benefit over the default O/S-based plug-in shipped with DB2
on Windows platform. The SYSADM group does not need to be defined in the operating
system and does not need to have Windows administrator privileges.
The connections from the plug-in daemon to the DB2 authentication
database are persistent, that is, they are maintained for the duration of
the daemon operation. It is conceivable that a connection may unexpectedly
terminate that would not allow authentication requests to proceed. In order
to minimize the impact of this possibility, the daemon attempts to
re-establish the connection to the authentication database during subsequent
processing. Until the connection is re-established, the end users receives
error codes indicating that authentication requests failed. The diagnostic
information for these events during plug-in
processing are written to the %db2instprof%\DB2instanceName\db2diag.log
file. Some plug-in error messages show up in the Windows event log.
In a case where the authentication database is local, issuing the db2stop
command to shut down the instance fails with a message that databases
are still active because of database connections from the plug-in that have
not been terminated. Consequently the
deployment of this plug-in with a local authentication database means that the
db2stop force command should be used to shut down the instance.
Another alternative is for the administrator to use the
db2 list applications command to check for active applications and
subsequently use the db2 force applications command to terminate the
database connections established by the group and security plug-ins.
These database connections can be identified by their name: db2AuthDaemon.exe.
This is the only notable operational difference between the DB2 authentication
plug-in and the default O/S plug-in.
The db2auth plug-in implementation follows the default DB2 philosophy
of assuming that it is running under an operating system in a trusted environment.
This means that if a user attempts to connect to a local database
(meaning, on the same server and part of the same DB2 instance) without specifying
a password, then this connection is allowed on the presumption that
the user was already authenticated by the operating system. This facility
is called "DB2 implicit local logon." The plug-in code could, of course, be
modified to require a password in the case of local user connections as well, but
doing so would disable the operation of some functions, such as the DB2 Health
Monitor.
Figure 2 illustrates the architecture of the DB2 security plug-in:
Figure 2. Plug-in architecture
Database structures
Now turn your attention to the implementation of the DB2 database objects
(such as, schema, tables, indexes, and stored procedures) and the application interface to these objects.
All the database objects that are created for the security plug-in are
encapsulated within a DB2AUTH schema. Using a dedicated schema
for the plug-in allows the security information to be segregated from the
rest of the data, making it practical to use a single database to contain
both authentication information as well as the rest of the information.
Three tables are created to maintain authentication information:
- The USERS table contains the user account information that
consists of the DB2 authid (authentication identifier), password,
password expiry date, account status, and the
number of failed logins.
In this implementation,
passwords are never stored in the clear text in the database, and they are not
sent in the clear by the plug-in applications. Instead, the clear-text
passwords are hashed using an MD5 hash algorithm by the plug-in applications and
are stored in this hashed form in the database. Password validation is
done by comparing the hash signatures of the password provided by the user with
the signature stored in the database.
The password expiry date is used by the plug-in to determine if the password
is current or not. The implementation sets the password expiration date to 90 days
from the time that the password is initially created or changed. After 90
days, password validation will not succeed. The only way to enable the user
to authenticate to that account is for the administrator to reset this user's
password (which resets the 90-day clock).
The account status flag can be used by an administrator to temporarily
enable or disable the use of that account. The default account status
is 'N'ormal, which allows the authentication to succeed if a valid password is provided.
However, the administrator can change the account status flag to 'L'ocked.
By doing so, authentication requests are rejected even if the correct password
is specified.
The failed logins counter keeps count of how many times an authentication
request failed due to an incorrect password. If the user provides an incorrect
password more than four times, the account is locked until the administrator
resets it. However, if the user does provide the correct password on the
second, third, or fourth try, then authentication request succeeds and the
counter is reset to 0.
- The GROUPS table contains the list of DB2 groups that have
been created by the administrator.
- The GROUP_MEMBERSHIP table contains information on which
user IDs are members of what group. Each row of this table is a tuple
consisting of the group name and user ID. This table uses referential integrity
constraints to ensure data validity. Therefore, if a user ID is deleted from the
USERS table, then corresponding entries are deleted from the
GROUP_MEMBERSHIP table. Same is true if a group ID is deleted from the
GROUPS table.
Internally, DB2 processes user ID and group names in a case-insensitive
fashion. This means that the plug-in may receive user ID and group names
either in lowercase (for example, if the user entered the password in an application)
or in uppercase (for example, if DB2 references internal privilege checking mechanisms).
Consequently all user IDs and group names are stored in the database in
uppercase.
Figure 3 is a graphical illustration of the database objects
described above:
Figure 3. Design of the authentication database
SQL application interface
Now that the plug-in architecture has been designed and database design
has also been finalized, take a look at the application interface.
The plug-in daemon is the primary application communicating with the
authorization database. The plug-in uses the CLI
because it is very easy to use. Note that the daemon's interactions
with the database must not trigger additional authentication attempts by the
database. An authentication request would be routed by the database engine to
the plug-in, which would cause a deadlock during the processing of the previous
authentication request. Thus, for example, the plug-in daemon cannot invoke
SQL stored procedures.
The plug-in daemon interactions with the authentication database are very
simple. The following are some of the SQL statements that the daemon
issues.
Listing 1. Resetting user's password
UPDATE db2auth.users SET failed_logins = 0, password = ?,
pw_expire_date = (current date + 90 days)
WHERE USERID = UPPER(?)
|
Listing 2. Obtain a list of groups of which the user is a member
SELECT groupid FROM db2auth.group_membership WHERE userid = ?
|
Listing 3. Lock the account after too many invalid authentication attempts
UPDATE db2auth.users SET account_status = 'L', failed_logins = failed_logins + 1
WHERE userid = UPPER(?)
|
The above code takes care of the security plug-in. However, the complete
solution needs to provide an interface to the security administrator who
owns the information in the database. The administration tools
may be developed in C/C++, Java, Perl, PHP, or other interfaces.
Using SQL stored procedures is the best way to encapsulate
the business logic of administrative applications, minimizing the
amount of code that developers need to write.
Below is a list of SQL stored procedures that
have been written and presented in the format that can be directly executed by
the DB2 CLP:
- Reset the user's account:
db2 call db2auth.change_password_admin('kohlmann', '<old password hash>', '<new password hash>', ?, ?, ?)
- Add a new user:
db2 call db2auth.add_user('gene', '<MD5 hash signature of password>', ?, ?, ?)
- Add a new group:
db2 call db2auth.add_group ('db2users', ?, ?, ?)
- Delete a user:
db2 call db2auth.del_user('gene', ?, ?, ?))
- Delete a group:
db2 call db2auth.del_group('db2users', ?, ?, ?)
- Make a user a member of a group:
db2 call db2auth.add_groupmember('db2admns', 'gene', ?, ?, ?)
- Remove a user from being a member of a group:
db2 call db2auth.del_groupmember ('db2users', 'gene', ?, ?, ?)
Listing 4 is a listing of one of the stored procedures:
Listing 4. Source code for change_password_admin SQL stored procedure
CREATE PROCEDURE CHANGE_PASSWORD_ADMIN (IN userid_in VARCHAR(255),
IN newpassword_in VARCHAR(16) FOR BIT DATA,
OUT sqlstate_out CHAR(5),
OUT sqlreason_out INTEGER,
OUT message_out VARCHAR(70))
DYNAMIC RESULT SETS 1
------------------------------------------------------------------------
-- SQL Stored Procedure
-- userid_in
-- newpassword_in (hashed using MD5 algorithm)
-- sqlstate_out: Security processing error will result in '08001'
-- sqlreason_out: Error reason code. See reason codes associated with
SQLSTATE 08001 in DB2 doc
-- message_out: Error message text
------------------------------------------------------------------------
P1: BEGIN
-- Declare variables
DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
DECLARE SQLCODE INT DEFAULT 0;
DECLARE valid_userid SMALLINT DEFAULT 1;
-- Declare handler
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET valid_userid = 0;
SET sqlstate_out = '00000';
SET sqlreason_out = 0;
SET message_out = '';
IF userid_in = '' THEN
SET sqlstate_out = '08001';
SET sqlreason_out = 6;
SET message_out = 'Security processing failed: Invalid userid.';
ELSE -- try to change the password
UPDATE DB2AUTH.USERS SET failed_logins = 0, account_status = 'N',
PASSWORD = newpassword_in
WHERE USERID = UPPER(userid_in);
END IF;
IF valid_userid = 0 THEN -- userid not found in user table, return error
SET sqlstate_out = '08001';
SET sqlreason_out = 24;
SET message_out = 'Security processing failed: Invalid userid.';
END IF;
END P1
|
|
User interface
With the above infrastructure in place, the only missing elements are the
mechanisms to allow DB2 users to interact with the system for authentication and
administrative purposes.
Non-administrative users need to be able to perform only two basic tasks:
Authenticate user ID/password combination and change their passwords. Both of
these capabilities are already part of DB2 product available in the
CONNECT API. For example, the following DB2 CLP
command attempts a connection to the database DB2AUTH while at the same time
changing the existing password for the user GENE:
Listing 5. User changing password using DB2 CLP
C:\>db2 connect to DB2AUTH user GENE change password
Enter current password for GENE:
Enter new password for GENE:
Confirm new password for GENE:
Database Connection Information
Database server = DB2/NT 9.5.0
SQL authorization ID = GENE
Local database alias = DB2AUTH
|
Of course, this functionality is not limited just to CLP. Any user-written
application written in any of the APIs supported by DB2 can do the same thing.
For example, C or C++ applications running on Windows using the CLI API would invoke
the SQLDriverConnect API as follows:
SQLDriverConnect (hdbc, (SQLHWND)NULL, "DSN=;UID=;PWD=;", SQL_NTS,
NULL, 0, NULL, SQL_DRIVER_PROMPT) |
.
The execution of an application containing
this code launches the following GUI interface:
Figure 4. Database connection interface
The administrator of the authentication data needs an application
interface to manage user accounts and group information. This application interface
does not need to be very complicated or elaborate. Aside from developing the
presentation layer of the administrative interface (for example, a GUI interface developed
using Java or PHP, or Windows native APIs), the SQL layer of the interface can be
extremely simple. Its primary task is to invoke the SQL stored procedures described above.
Potential future enhancements
There are several directions in which the current plug-in implementation
can be extended by interested developers.
The initial plug-in implementation is shipped in binary form only for a
32-bit Windows platform. Making the plug-in work on a 64-bit Windows platform
should not involve much more work than re-compiling the plug-in C code to generate
64-bit code. Porting the plug-in code to Linux and Unix platforms is more challenging
because of the use of Windows-specific APIs for inter-process communications
and generating MD5 hash signatures.
Another area for enhancements is to implement additional rules on what the
user passwords should look like. For example, additional logic can be
added to the plug-in to enforce minimum password length and password
complexity. It would also be fairly simple to add another table to the
solution that would store the hash signatures of the last N passwords that have
been specified for a particular user ID. When the user asks that his password
be changed, the table can be checked to see that a new password is not the same
as one of the N most recent passwords used.
Although the use of hashing ensures that passwords are not sent in the clear
over the network or stored in the database by the plug-in, there are additional
techniques that can be implemented to reduce the possibility of a hostile
attacker guessing password values. One way to do so is to use salt.
Salt is a way to season the passwords before hashing them, making the attacker's
precomputed dictionary useless.
In the usability area, development of a GUI interface to administer
the authentication information database would be highly desirable. The current
implementation of the plug-in ships with an interactive character-based
interface, but it is not as user-friendly as what a well-developed GUI would
look like. One possible approach would be to develop a stand-alone administrative
interface written in Java. Another approach would be to develop a new building
block to be plugged into the DB2 Monitoring Console infrastructure written
in PHP. The DB2 Monitoring Console is an open source project composed
of building blocks for rapidly developing your own console or PHP Web-based
application.
Conclusion
This article described the design of a DB2 security plug-in that uses an SQL interface
in order to access authentication information stored in a DB2 database.
To download the code described in this article, which is available in both source and binary
formats, refer to the Download section.
Acknowledgements
This project would not have seen the light of day without assistance from the
following individuals:
- Peter Kohlmann, who encouraged and supported this project from its conception
through its development
- Scott Logan, who shared his insights into how DB2 security plug-ins are
architected
- Garfield Lewis, for his tips on Windows application development
- Kevin See, Henry Chan, Walid Rjaibi, and others in the DB2 development
organization, who provided valuable feedback on the resulting solution
Download | Description | Name | Size | Download method |
|---|
| Implementation of the security plug-in for Windows | db2auth.zip | 60KB | HTTP |
|---|
Resources Learn
Get products and technologies
-
Build your next development project with
IBM
trial software, available for download directly from developerWorks.
Discuss
About the author | 
| | Gene Kligerman has been with IBM since 1987. He has had assignments in product development, technical marketing, and release management. Currently Gene works in the DB2 Project Office in IBM Canada, Toronto Lab. |
Rate this page
| |
