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:
The basic steps are:
- 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).
- Set the DB2 registry parameter, and then bounce your db2 instance to make it take effect
- 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:
#%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.
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 220.127.116.11 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 ?
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)
(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)
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?
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
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.
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?
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) 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.
Hi Ember thanks for your post.
I have enabled LDAP, AD group authentication is working probably, but it seems server authentication isn’t working, so I can’t connect using db2inst1 locally anymore and got the following error.
SQL1366N A security plug-in “IBMLDAPauthclient” processing error occurred on the client. Reason code = “7”
I have set “db2 update dbm cfg using CLNT_PW_PLUGIN NULL”, but still get the same error.
Hi. So I’m looking at implementing LDAP modules but realizing that all or none of the authentication occurs in LDAP, such that all db2 userid’s including instance owner etc. needs to be set up in LDAP directory. Does Transparent LDAP alleviate that restriction?
Transparent LDAP allows you to have some IDs locally and some in LDAP. The way I most frequently see it done is that the instance owner and application ids are on the server itself. This means that even if LDAP goes down, the database and the applications can continue to work. Often personal ids (of developers and administrators) are in LDAP, and there are different groups for each Db2 environment to control who has permissions there.