DB2 and Transparent LDAP

Posted by

Ok, so I know I’m in the middle of a multi-part post on Data Movement between Commerce databases (and I will get back to that), but I found this and had to share it because I’m so excited DB2 has finally added support for it.

As of DB2 9.7 FixPack 1, DB2 finally supports transparent LDAP on Unix and Linux! Previously you had to deal with plugins that were frequently pay-for-use or code-it-yourself (or just give up and keep separate OS-level ids for DB2). Now with just three steps, you can get DB2 to use PAM. Here’s the link to the info center:

http://www.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.admin.sec.doc/doc//t0056291.html

The basic steps are:

  1. Have your Sysadmin set up LDAP at the OS level (or do it yourself). I generally recommend keeping the instance owner id as a system-level id, so that if your LDAP goes down, you can still do the basics (yes, even if you have highly redundant LDAP).
  2. Set the DB2 registry parameter, and then bounce your db2 instance to make it take effect
  3. db2set DB2AUTH=OSAUTHDB
  4. Edit or create /etc/pam.d/db2 as appropriate for your OS(the info center gives some details on it). For RHEL, it looks like:
  5. #%PAM-1.0 
    
    auth    required    /lib/security/$ISA/pam_env.so
    auth    sufficient  /lib/security/$ISA/pam_unix.so likeauth nullok
    auth    sufficient  /lib/security/$ISA/pam_ldap.so use_first_pass
    auth    required    /lib/security/$ISA/pam_deny.so 
    
    account  required   /lib/security/$ISA/pam_unix.so
    account  sufficient /lib/security/$ISA/pam_succeed_if.so uid < 100 quiet
    account  sufficient /lib/security/$ISA/pam_ldap.so 
    account  required   /lib/security/$ISA/pam_permit.so 
    
    password requisite  /lib/security/$ISA/pam_cracklib.so retry=3 dcredit=-1 ucredit=-1
    password sufficient /lib/security/$ISA/pam_unix.so nullok use_authtok md5 shadowremember=3
    password sufficient /lib/security/$ISA/pam_ldap.so  use_first_pass
    password required   /lib/security/$ISA/pam_deny.so 
    
    session  required   /lib/security/$ISA/pam_limits.so
    session  required   /lib/security/$ISA/pam_unix.so

    And that’s really all there is to it. Keep in mind that older versions of DB2 do not support this (AIX only came in an earlier version), and that you should, of course, test this thoroughly to see both that it works and what happens when your LDAP server is down.

Lead Db2 Database Engineer and Service Delivery Manager , XTIVIA
Ember is always curious and thrives on change. Working in IT provides a lot of that change, but after 17 years developing a top-level expertise on Db2 for mid-range servers and more than 7 years blogging about it, Ember is hungry for new challenges and looks to expand her skill set to the Data Engineering role for Data Science. With in-depth SQL and RDBMS knowledge, Ember shares both posts about her core skill set and her journey into Data Science. Ember lives in Denver and work from home for XTIVIA, leading a team of Db2 DBAs.

9 comments

  1. TIP (and Question) : How to use the newer sssd instead of pam-ldap ?
    When setting up Transparent LDAP authentication for DB2 on RHEL 6.x or 7.x using sssd instead of pam_ldap.
    Create the file “/etc/pam.d/db2” according to the IBM directions, but change the pam_ldap lines to pam_sss:
    Tested with DB2 11.1.2.2 on RedHat 7.3 server, a domain member of RedHat’s “IdM”
    (Identity Management, LDAP server – incl DC , DNS, NTP services ).

    One note said though: On Linux level, both domain Users and domain Groups are working.
    For DB2 on this server, so far, sadly only domain Users …..
    (and yes, our ” nsswitch.conf” file in “/etc/” also has ‘files sss’ instead of ‘files ldap’ )
    Somehow DB2 does not accept using domain Groups.
    Anyone an idea ?

  2. From a point-of-view “is SSSD (System Security Services Daemon) supported or not” :
    The Knowledge Center mentions:
    “DB2 supports PAM configurations that use pam_ldap.so, pam_unix.so, and pam_unix2.so. Configurations that use other PAM modules might work, but are not supported” ….
    (see https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.1.0/com.ibm.db2.luw.admin.sec.doc/doc/t0056291.html )
    However , Technote swg21976128 mentions:
    “SSSD is supported via Transparent LDAP on Linux”
    (see http://www-01.ibm.com/support/docview.wss?uid=swg21976128 )

    Sadly, so far (at least for us), DB2:
    accepts “grant … to user @” (DB2 knows “@” is in fact a special character; the domain is the remainder part)
    does not (properly) accept “grant ….. to group @” ; (DB2 treats it as full string, isn’t aware the “@” should be treated as special character for the domain)

  3. (correction to previous comment)
    so far (at least for us), DB2:
    accepts “grant … to user username@domainname” (DB2 knows “@” is in fact a special character; the domain is the remainder part)
    does not (properly) accept “grant ….. to group groupname@domainname” ; (DB2 treats it as full string, isn’t aware the “@” should be treated as special character for the domain)

    1. First of all thanks Ember for this helpful post. I’m also testing the use of pam_sss.so instead of pam_ldap.so on Red Hat Linux, which works very nice, like Erwin described it. Following solution works for me to grant privileges to domain groups with @ sign (please note the quotes ;)).

      db2 ‘GRANT SELECT ON TEST1.T1 TO GROUP “MY_DB2_AD_GROUP@DOMAINNAME”‘
      db2 ‘REVOKE SELECT ON TEST1.T1 FROM GROUP “MY_DB2_AD_GROUP@DOMAINNAME”‘

      @Erwin: It would be interesting if this works for you as well?

  4. Hi Ember thanks for your post. I am trying to setup LDAP using plug-ins (not transparent ldap) however IBM KC does not give much information about how to configure the users/groups. It says even the db2 instance userid and all groups need to be defined on LDAP server. Does this mean they must not be defined in the unix server?. So how do you assign ownership of all the filesystems the database is going to use. I haven’t found real good documentation so I was wondering if you are aware of such. Thanks very much

    1. I have never used the plugins for precisely this reason. All IDs must be defined at the LDAP level. This means that if LDAP is not available, Db2 is essentially down. I don’t have details on how to use this method. I thought there were some white papers out there on it.

  5. hey ember, two questions in regards to transparent LDAP and SUSE Linux.

    1. ) do you know if it’s prereq to have the domain admins extend ldap in your environment to support Db2 authentication?

    reference below
    https://www.ibm.com/support/knowledgecenter/en/SSEPGG_9.7.0/com.ibm.db2.luw.qb.server.doc/doc/t0006857.html

    have not been successful yet and our Linux admin says OS setup to use PAM and winbind is correct.

    2.) If LDAP is enabled, does server based authentication still work? In example if I have a user defined to local groups on the server and not in LDAP (Active Directory). Will those credentials still be able to authenticate.

    1. 1) I think that’s only for Windows, specifically for 2003. I think that also relates to LDAP details of cataloging of databases, not authentication.

      2) If you enable LDAP using the transparent LDAP methodology, both server and LDAP ids and groups work. If you instead use a plugin, the ID may not work either place, depending on the plugin. Usually, it’s a better choice to use a role instead of a group in any kind of complicated authenitcation setup – that way, you know the permissions stay in the database. If you’re on an earlier version of Db2 (I notice you referenced the 9.7 Knowledge Center), then your LDAP ids will either need to be 8 characters or less, all lower case, or you’ll need to define a nickname for them at the LDAP level that meets Db2’s ID standards.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.