Introduction

The Technology Explorer for IBM DB2 (TE) is an open-source, light-weight, Web-based console for DB2 for Linux®, UNIX®, and Windows® that provides a graphical interface to many of DB2's features. This article teaches you how the Technology Explorer can control user and group authentication to DB2 through the use of a security plug-in called db2auth (see Resources for more about db2auth). This article also describes how the support in TE for the db2auth plug-in was implemented.

Learning about db2auth

The db2auth plug-in offers the following benefits and considerations:

• db2auth uses a DB2 database to store authentication information instead of an external authentication repository, such as an operating system or Kerberos. For most users, this means there is no need to create a new operating system user account. The default DB2 for Linux, UNIX, and Windows package separates authentication from authorization and privileges by relying on one of the following external authentication mechanisms:
  • The operating system
  • Lightweight Directory Access Protocol (LDAP)
  • Kerberos
• db2auth enables a smoother migration from other database software, such as MySQL, that store authentication information within the database.
  
  
• db2auth limits the security initially provided by DB2. Before installing the plug-in, consider the degree of security your database needs or consider whether you should only use this as a temporary migration step from other database software.
  
  
• For external applications that work with DB2, including TE, this plug-in provides the means to directly control user and group authentication. This facilitates the implementation of modules to control user and group authentication information.
  
  
• Maintaining users and groups is easier, because everything can be controlled within DB2 using SQL, using the stored procedures the plug-in creates.
  
  
• User and group information used in pre-packaged applications can be distributed within a DB2 database alongside the application data. Storage of authentication and membership information in tables enables easier viewing for the administrator.

How the db2auth plug-in works

The db2auth plug-in stores all user, password, and group information in the database. Upon the initial set-up, it creates a schema called DB2AUTH, as well as three tables: USERS, GROUPS, and GROUP_MEMBERSHIP.

• The USERS table stores the user ID (unique name), the password (MD5 hash), the password expiration date, whether the account is locked, and the number of failed login attempts.
  
  
• The GROUPS table stores the group ID (unique name).
  
  
• The GROUP_MEMBERSHIP table stores the group ID and the user ID, which represents a user's membership in a group.

The plug-in also creates stored procedures, which are used to add, remove, and modify user, group, and group membership information.

Why db2auth is not a standard DB2 authentication option

There is always a trade-off between ease of use and security. The consequence of eliminating the layer of separation between authentication and authorization results in decreased security. User accounts and (hashed) passwords are stored directly in the database. Furthermore, the following information is stored in a flat file called db2auth_key.txt:

• The database name where the DB2AUTH schema and its three tables are stored
• The group name that the SYSADM_GROUP configuration parameter points to
• The administrator's user name
• The administrator's password (not hashed) when using the db2auth plug-in for remote databases

The db2auth plug-in's documentation explains how to hide and encrypt the file, which offers somewhat limited security.

Back to top

Understanding the TE support for db2auth

The db2auth plug-in enables control of authentication information using the command-line interface. What the TE support offers is an easy-to-use graphical user interface. With only a few clicks, administrators can create and delete users and groups, modify group memberships, and change user passwords. The module also pulls up a graphical representation of the three tables containing user, group, and membership information.

Requirements for the module

To download the TE, refer to the TE's site on SourceForge listed in Resources.

The db2auth plug-in must be installed and running properly. See Resources for a link to the plug-in article that describes how to download the plug-in. All installation and set-up steps are covered in the db2auth's readme document.

How to load and control the plug-in support in TE

1. In the TE, connect to the authorization database (as indicated in db2auth_key.txt) as a user who has administrative privileges.
   
   
2. Go to the Tools menu, and select Db2auth Plugin Control from the drop-down menu. The module first checks that the db2auth plug-in is installed in the system by running the query: SELECT COUNT(*) FROM SYSCAT.PROCEDURES WHERE PROCSCHEMA = DB2AUTH. The module then checks for the existence of the DB2AUTH.USERS table. If the TE does not find both of these, the TE alerts the user, and the module does not load.
   
   
3. Once the module is loaded, you see that the page is split vertically into two panels. You also see four tabs, each containing a different table. The tabs are:

• Users: This tab displays all of the contents of the DB2AUTH.USERS table without the password. There are menu options to add a user, to delete a user, and to change a user's password. You see an icon in each row to delete the user in that row. You see an icon to change each user's password. Figure 1 shows the Users tab.

• Groups: This tab displays all groups listed in the DB2AUTH.GROUPS table. There are menu options to add and delete groups. You see an icon for each row to delete the group in that row. Figure 2 shows the Groups tab.

• Group Memberships: This tab displays all user-to-group mappings that result from a full outer joining of the DB2AUTH.USERS, DB2AUTH.GROUPS, and DB2AUTH.GROUP_MEMBERSHIP tables. Users who do not belong to any group show null as their group ID. Groups without any users show null as the row's user ID. There are menu options to add users, to add groups, and to edit memberships. You see an icon for each row to delete the membership that the row represents. Figure 3 shows the Group Memberships tab.

• The Master View: Similar to the Group Memberships tab, this tab displays all user-to-group mappings that result from a full outer joining of the DB2AUTH.USERS, DB2AUTH.GROUPS, and DB2AUTH.GROUP_MEMBERSHIP tables. However, on the Master View tab, the administrator can complete all the same actions as on the three other tabs, including adding users, deleting users, adding groups, deleting groups, editing memberships, and changing passwords. Figure 4 shows the Master View tab.

All four tabs contain a person-image icon for each row next to a user ID or group ID. The icon pulls up authorization information in the bottom panel if that user ID or group ID exists in the AUTHID field in the SYSIBMADM.AUTHORIZATIONS table, which means that user or group has explicitly been given authorizations or privileges. Figure 5 shows the bottom panel.

Back to top

Conclusion

This article described advantages and disadvantages of using the db2auth plug-in, as well as the design and usage of the TE module built to support the plug-in. You also learned about the user interface that the TE offers and how to navigate it.

Resources

Learn

• Refer to "DB2 Security, Part 11: Develop a security plug-in for DB2 database authentication" (developerWorks, Feb 2008) for the steps involved when developing a security plug-in, as well as how to use the db2auth plug-in, which was created by Gene Kligerman.
  
  
• Check out the DB2 for Linux, UNIX, and Windows area on developerWorks to get the resources you need to advance your skills in DB2.
  
  
• Learn more about Information Management at the developerWorks Information Management zone. Find technical documentation, how-to articles, education, downloads, product information, and more.
  
  
• Stay current with developerWorks technical events and webcasts.
  
  

Get products and technologies

• Try the Technology Explorer for IBM DB2 at SourceForge.
  
  
• Download DB2 Express-C 9.7, a no-charge version of DB2 Express database server for the community.
  
  
• Download a free trial version of DB2 9.7 for Linux, UNIX, and Windows..
  
  
• Build your next development project with IBM trial software, available for download directly from developerWorks.
  
  

Discuss

• Participate in the discussion forum.
  
  
• Check out the developerWorks blogs and get involved in the developerWorks community.
  
  

About the author

Misa Sakamoto is currently working in the DB2 Project Office at the IBM Canada, Toronto Lab.

Comments (Undergoing maintenance)

Back to top

Trademarks  |  My developerWorks terms and conditions

Table of contents

• Introduction
• Learning about db2auth
• Understanding the TE support for db2auth
• Conclusion
• Resources
• About the author
• Comments

Next steps from IBM

• Try: DB2 Enterprise
• Demo: DB2 serving ATOM feed
• Webcast: A smarter way to successful SOA - Information as a trusted strategic asset
• Briefing: Information on Demand
• Buy: DB2 Enterprise

My developerWorks community

Dig deeper into Information Management on developerWorks

DB2 pureScale

See how true transparent scaling architecture can be achieved on distributed platforms

Special offers