Business Cases: Using the Centrify IBM DB2 SSO Module to Centralize Database Access in UNIX/Linux with AD

Background

Organizations can always count with the reliability of IBM hardware, operating systems and utilities for mission critical applications.  That’s why Centrify has invested in certifying the product lines with IBM infrastructure.

This post discusses the DB2 SSO Module; this plugin (like the Apache HTTP and Java plugins) leverages the Active Directory integration capabilities and robustness of the Centrify agent to provide additional value and functionality to DB2 implementations.

The DB2 plugin provides the following benefits:

• No need to keep users local to the UNIX/Linux system to support DB2:  When used natively, DB2 users need to have user accounts in the local /etc/passwd file.  The DB2 enables AD users to access DB2 so the benefits of Unified Identity, Centralized Administration, Streamlined Authentication and Policy Enforcement are organically attained.
  In practical terms:  no more getting dinged by auditors when the account of a long-gone user is found active in the /etc/passwd of a DB2 system.

• Long login names: Support for logins that are longer than 8 characters
• Single Sign-on (SSO):  Centrify enables SSO to DB2 leveraging the GSSAPI
• Active Directory Group Support:  AD group memberships can be leveraged to grant entitlements inside DB2. 

 This is one of the best Database to AD integration models out there.

This article covers setup, configuration and testing of the DB2 plugin on Linux 64 bit in a lab environment.  Like any other DBMS, a true production implementation requires planning and understanding of the current environment.

Requirements

• A Centrified Unix/Linux system running a DB2 Instance (we’ll be using DB2 10.5 on Linux)
  Setup is pretty much the same if you have an IBM AIX system.  The only caveat is that if you’re using LAM instead of PAM, you’ll need to use the GSSAPI (SSO) plugin rather than the user/password plugin.
• You need to know the DB2 Instance user name and password
• You need to have the ability to create an AD service account or have an account prepared for you that can be adopted with AD Keytab (GSSAPI SSO plugin only).

Implementation Steps

Information and requirements gathering

1.  Collect the OS version, architecture, version of Centrify adclient.uname -a, adinfo -v and adinfo -C provide that information
2.  Collect the DB2 database version, architecturethe db2level command provides this information
3. Request an AD service account OR have credentials to run adkeytab.
   $ cat /etc/redhat-release
   CentOS release 6.6 (Final)
   
   $ uname -a
   Linux engcen8.centrifyimage.vms 2.6.32-504.el6.x86_64 #1 SMP Wed Oct 15 04:27:16 UTC 2014 x86_64 x86_64 x86_64 GNU/Linux
   
   $ adinfo -v
   adinfo (CentrifyDC 5.2.0-218)
   
   $ db2level
   DB21085I  This instance or install (instance name, where applicable: "db2inst1") uses "64" bits and DB2 code release "SQL10050" with level identifier "0601010E".
   Informational tokens are "DB2 v10.5.0.0", "s130528", "LINUXAMD64105", and Fix Pack "0".
   Product is installed at "/opt/ibm/db2/V10.5".

My server64 bit CentOS with DB2 10.5 64 bit, the instance name is db2inst1.  I will download the package DirectControl for IBM DB2 running on RHEL 4, 5, 6 x86_64 " the version is 4.4.4 as of the original post in November 2014.

Installation

Unpack and install the DB2 SSO plugin

$ tar xzvf centrifydc-db2-4.4.4-rhel3-x86_64.tgz

The installation file on RHEL for this version is called centrifydc-apache-4.4.4-rhel3-x86_64.rpm, so perform a yum or rpm install.

$ dzdo rpm -Uvh centrifydc-db2-4.4.4-rhel3-x86_64.rpm

Preparing...                ########################################### [100%]

   1:CentrifyDC-web      ########################################### [100%]

Installing and Configuring the User/Password and Group Plugins

The user/password plugin allows for DB2 to use PAM to provide access to AD users.  The group plugin allows the use of AD group memberships for the purposes of entitlements inside DB2. 

The master script is called setupdb2.sh and it is on /usr/share/centrifydc/bin.  The syntax is setupdb2.sh inst=<instancename>  In my case the instance is called db2inst1.

$ dzdo /usr/share/centrifydc/bin/setupdb2.sh inst=db2inst1

Is db2inst1 a DB2 server install?

Enter y for yes, n for no [y]: y

 db2inst1 is a 64 bit instance

DB2 server and client setup will be done.

 Is this DB2 version 9.5 or later?

Enter y for yes, n for no [n]: y

  

Which DB2 auth method do you want to use?

[1] Username/Password and Single sign-on

[2] Single Sign-on only

[3] Username/Password only

[4] Skip this step

Select a number from the menu [1]: 3

Which data sent to DB2 should be encrypted?

[1] Nothing

[2] The username and their password

[3] All data going to the server

[4] Encrypt and compress all data going to the server

[5] Skip this step

Select a number from the menu [2]: 3

Use the CentrifyDC group plugin?

Enter y for yes, n for no [y]: y

In order for the username/password plugin to work, the executable:

/usr/share/centrifydc/bin/db2userpass_checkpwd must be set uid and the

instance users must be allowed to run it.

What group should be used as the group owner of this file? All DB2 instances

that you want to use the username/password plugin must be in this group.

[db2iadm1]:

Are the following PAM settings OK for the centrifydc_db2userpass PAM service?

These settings will only affect the Centrify DB2 username/password module.

No other PAM services or DB2 plugins will be affected.  If these settings are

not OK, then you will have to set up PAM yourself.

#%PAM-1.0

auth [user_unknown=ignore success=ok ignore=ignore default=bad] pam_securetty.so

auth       include      system-auth

account    required     pam_nologin.so

account    include      system-auth

Enter y for yes, n for no [y]: y

<output truncated>

The DB2 configuration will be updated to:

SRVCON_AUTH  =  DATA_ENCRYPT

SRVCON_PW_PLUGIN  =  centrifydc_db2userpass

LOCAL_GSSPLUGIN  =  NULL

SRVCON_GSSPLUGIN_LIST  =  NULL

AUTHENTICATION  =  SERVER

GROUP_PLUGIN  =  centrifydc_db2group

Continuing will stop the DB2 instance: db2inst1, update the configuration

and then start the instance.

Continue?

Enter y for yes, n for no [y]: y

Stopping instance: db2inst1

# db2stop

<output truncated>

New configuration:

 Group Plugin                             (GROUP_PLUGIN) = centrifydc_db2group

 GSS Plugin for Local Authorization    (LOCAL_GSSPLUGIN) =

 Server List of GSS Plugins      (SRVCON_GSSPLUGIN_LIST) =

 Server Userid-Password Plugin        (SRVCON_PW_PLUGIN) = centrifydc_db2userpass

 Server Connection Authentication          (SRVCON_AUTH) = DATA_ENCRYPT

 Database manager authentication        (AUTHENTICATION) = SERVER

Starting instance

# db2start

At this point DB2 has been configured for the user/password (PAM) and Group plugins and we’re ready to test.

Testing the User/Password & Group Plugins

In my environment I enabled the sample database.

Test 1:  Sign-in with any AD account that is allowed to log into the system

Open the sample database
$ db2
db2 => connect to sample user fred.thomas
Enter current password for fred.thomas:

   Database Connection Information

Database server        = DB2/LINUXX8664 10.5.0
SQL authorization ID   = FRED.THO...
Local database alias   = SAMPLE

Note that Fred’s user ID is longer than 8 characters

Test 2:  Enumerate group memberships.

First, use adquery user –A | grep MemberOf to see the group memberships in AD

$ adquery user fred.thomas -A | grep memberOf

memberOf:centrifyimage.vms/Groups/DBA Staff - Contract,centrifyimage.vms/Groups/Demo Users,centrifyimage.vms/Groups/Google Apps

Based on this output my user belongs to DBA Staff – Contract, Domain Users, Demo Users and Google Apps

Connect to DB2 again and type the query below

db2 => SELECT * FROM table (SYSPROC.AUTH_LIST_GROUPS_FOR_AUTHID (CURRENT USER)) AS ST

GROUP                                                                             
------------------------------------------------------

DBA STAFF - CONTRACT                                                                                

DEMO USERS                                                                                         

DOMAIN USERS                                                                                       

GOOGLE APPS  

At this point we have verified that DB2 can enumerate the user’s groups from Active Directory.

Where to next?

You can engage your DBAs to test this solution.  The benefits are huge for organizations that are looking to consolidate processes in AD.

A follow-up post will discuss the SSO (GSSAPI) plugin.

Video Playlist

(2 videos, 10:36 total)

Appendix

Installing DB2 Express – C on CentOS 6.x

• DB2 Setup uses the X-Window system, if you don’t have it
  

  # yum groupinstall "Desktop" "X Window System" "Fonts" "Desktop Platform"

• Disable SELinux  (this is a requirement of DB2)
  

  Set the following line in /etc/selinux/config

  
  

  SELINUX=disabled

  
  

  And reboot your system.

•  Obtain the DB2 Express – C tarball from IBM
• Instructions: 
• Download link:  https://iwm.dhe.ibm.com/sdfdl/v2/regs2/db2pmopn/db2_v105/expc/Xa.2/Xb.aA_60_-idYiE_CQBnqJLzJNYLjR0g-_KdoeQpLzhdQ/Xc.db2_v105/expc/v10.5_linuxx64_expc.tar.gz/Xd./Xf.LPr.D1vk/Xg.7824906/Xi.swg-db2expressc/XY.regsrvs/XZ.oJ6tKMnlRFf0PQrahFUZYxkXjBg/v10.5_linuxx64_expc.tar.gz
  
  $ tar xzvf v10.5_linuxx64_expc.tar.gz$ cd expc$ ./db2prereqcheck$ ./db2_install (will launch the Java-based Setup Wizard on X Window)

• Setup DB2 in graphical interface(I set up a database instance with all defaults – remember the instance user password)
• Install the sample database

          $ dzdo su –db2inst1

     $ db2sampl

• Test connectivity to the sample database
  
  
  

  $ db2 connect to sample user db2inst1

  Enter current password for db2inst1:

  
  
  

  Database Connection Information

  Database server        = DB2/LINUXX8664 10.5.0

  SQL authorization ID   = DB2INST1

  Local database alias   = SAMPLE