Use Technology Explorer for IBM DB2 to manage user and group authentication for DB2 for Linux, UNIX, and Windows

Controlling the db2auth plug-in from the Technology Explorer

Learn how to use the Technology Explorer (TE) for IBM DB2® to control user and group authentication to DB2 through the use of a security plug-in called db2auth. The plug-in uses a DB2 database for storing authentication information instead of an external authentication repository, such as an operating system or Kerberos. The plug-in allows for a smoother migration from other database software such as MySQL, which also stores authentication information within the database. This article also describes how the support in TE for the db2auth plug-in was implemented. [2009 Nov 13: Updated to show Linux support. --Ed.]

Share:

Misa Sakamoto (misatos@ca.ibm.com), Technology Explorer Developer, IBM

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



13 November 2009 (First published 05 November 2009)

Also available in Portuguese

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.


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.
Figure 1. The Users tab
Screen cap: Db2Auth Control Panel, Users tab, showing several user IDs, edit password, pwd expiration date, Account Status, and Failed Logins
  • 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.
Figure 2. The Groups tab
Screen cap: Users tab showing how to add or delete groups
  • 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.
Figure 3. The Group Memberships tab
Screen cap: Group Memberships tab showing several User IDs and Group IDs
  • 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.
Figure 4. The Master View tab
Screen cap: Master View tab showing several User ID, Password, Remove User, Remove Member, Group ID, and Remove Group entries

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.

Figure 5. The bottom panel
Screen cap: The bottom panel shows several authorizations for the user AUTHUSER1

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

Get products and technologies

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, Open source
ArticleID=444290
ArticleTitle=Use Technology Explorer for IBM DB2 to manage user and group authentication for DB2 for Linux, UNIX, and Windows
publish-date=11132009