DB2 Security, Part 11: Develop a security plug-in for DB2 database authentication

How to design and develop a DB2 security plug-in that authenticates user and group information stored in a DB2 database

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 ported to Windows, Linux, and AIX operating systems. This article is a continuation of the series of articles about DB2 security.

This article has been updated with additional information on the administrator user interface and support for Linux and AIX.

Share:

Gene Kligerman (gene_kligerman@ca.ibm.com), DB2 Project Office, IBM

Author Photo: Gene KligermanGene 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.



25 November 2009 (First published 28 February 2008)

Also available in Chinese

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 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:

  1. Operating system (default)
  2. Lightweight Directory Access Protocol (LDAP)
  3. 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 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 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 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 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 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
Windows Explorer showing 'Encrypt contents' selected on advanced attributes properties of db2auth_key.txt 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 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 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. 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 (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
Architecture components: DB2 database, CLI connection, daemon processes named pipe, plug-ins db2sync engine, db2auth_key.txt

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. 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 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). 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
USERS table points to USERID in GROUP_MEMBERSHIP table. GROUPS table points to GROUPID in GROUP_MEMBERSHIP table.

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.

Administrator interface

You can invoke the db2auth_admin application either in command-line batch mode or as an interactive application. Issuing command db2auth_admin -help 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
User is prompted for authentication then shown supported actions. In example that is shown, a user account is created.

User 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
Small pop-up window that prompts the user for ID/password and optionally change password.

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.


Conclusion

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.

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

Downloads

DescriptionNameSize
The security plug-in for 32-bit Windowsplugin_Windows_x86.zip1.06MB
The security plug-in for 64-bit Windowsplugin_Windows_x64.zip1.13MB
The security plug-in for Linuxplugin_Linux.tar720KB
The security plug-in for AIXplugin_AIX.tar710KB

Resources

Learn

Get products and technologies

  • Build your next development project with IBM trial software, available for download directly from developerWorks.

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=291983
ArticleTitle=DB2 Security, Part 11: Develop a security plug-in for DB2 database authentication
publish-date=11252009