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 Downloads section. The plug-in is provided 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)
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 manage this information either locally or remotely.
- Authentication information should be stored within DB2 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 to review 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
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 that encapsulate 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 in the README.pdf file included in each of the plug-in download files (see Downloads section).
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, so 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
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.
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 a password should not be specified, which causes an implicit connection to take 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 the
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 and established during the first attempted connection to any
database owned by the DB2 instance. These connections are maintained for the duration of
daemon operation (that is, until a
db2stop command is issued).
It is possible that a connection could unexpectedly
terminate, which would result in further authentication requests not being allowed to proceed.
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
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 (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
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. This makes 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. The same is true if a group ID is deleted from the
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). Therefore, 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, let's 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. You can develop the administration tools 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 because it minimizes the amount of code that developers need to write.
Below is a list of SQL stored procedures. They are shown 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
Administrator and user interfaces
With the above infrastructure in place, the only missing elements are the easy to use mechanisms that operate the DB2 authentication system. Two interface methods have been developed at this time:
- db2auth_admin is an executable program you can invoke using command line arguments in a batch script or interactively from a command prompt (Windows) or terminal window (Unix and Linux). This executable is included in each of the plug-in downloads (see Downloads section).
- DB2 Technology Explorer (TE) is a light-weight, Web-based console with a GUI to administer DB2 security plug-in users and groups, along with many other features. See the Resources section at the end of this document for more information about TE.
You can invoke the db2auth_admin application either in command-line batch mode or
as an interactive application.
displays the syntax of the supported operations.
The screenshot in
Figure 4 shows an example of running the
application in interactive mode using the
db2auth_admin admin command .
Figure 4. Security administration interface
Non-administrative users need to be able to perform only two basic tasks: authenticate user ID/password combinations and change their passwords. Both of these capabilities are already available as part of the DB2 product through CONNECT API. For example, the following DB2 CLP command attempts to connect to the DB2AUTH database while at the same time changing the existing password for the user GENE:
Listing 5. User changing a 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 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 could 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 5. Database connection interface
The above code takes care of the security plug-in. But what about applications that need to interface with the security plug-in? Using SQL stored procedures is the best way to encapsulate the business logic of administrative applications because it minimizes the amount of code that developers need to write. The two administrative applications to the security plug-in (db2auth_admin and DB2 Technology Explorer described further in this article) invoke the SQL stored procedures developed for the plug-in.
Potential future enhancements
There are several directions in which the current plug-in implementation can be extended by interested developers.
One possible area of enhancement would be to implement additional rules regarding requirements and restrictions for user passwords. For example, you could add logic 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 a user asks that his password be changed, the solution could check the table to ensure that the 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 you could implement 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 an attacker's precomputed dictionary useless.
This article described the design of a DB2 security plug-in that uses an SQL interface to access authentication information stored in a DB2 database. The code described in the article is available in both source and binary formats in the Downloads section.
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
|The security plug-in for 32-bit Windows||plugin_Windows_x86.zip||1.06MB|
|The security plug-in for 64-bit Windows||plugin_Windows_x64.zip||1.13MB|
|The security plug-in for Linux||plugin_Linux.tar||720KB|
|The security plug-in for AIX||plugin_AIX.tar||710KB|
- Refer to Use Technology Explorer for IBM DB2 to manage user and group authentication for DB2 (developerWorks, November 2009) authored by Misa Sakamoto.
- developerWorks resource page for DB2 for Linux, UNIX, and Windows: Read articles and tutorials and connect to other resources to expand your DB2 skills.
- Learn about DB2 Express-C, the no-charge version of DB2 Express Edition for the community.
- "Understanding DB2 9 Security" (ISBN: 0131345907): Get a wealth of security information that isn't available anywhere else, direct from a DB2 security deployment expert and the IBM DB2 development team.
- developerWorks Information Management zone: Learn more about DB2. Find technical documentation, how-to articles, education, downloads, product information, and more.
- Stay current with developerWorks technical events and webcasts.
Get products and technologies
- Build your next development project with IBM trial software, available for download directly from developerWorks.
- Participate in the discussion forum.
- Participate in developerWorks blogs and get involved in the developerWorks community.