Using Windows AD groups in DB2

DB2 has an “interesting” relationship with Windows OS, and one of the areas where that relationship comes to the surface is user authentication and authorization, particularly when one is trying to use Active Domain groups for authorization purposes.

Here are a few pointers:

1. Don’t use CLIENT authentication – this authentication method is explicitly excluded from the ability to use AD groups for authorization purposes.
2. Set DB2_GRP_LOOKUP correctly (seefor explanation of what that means).
3. Make sure your instance runs with credentials that actually CAN query the AD . Hint: LOCAL SYSTEM cannot (at least not in my experience). There might be a way to allow that – I am not an AD or Windows OS guru and cannot speak to that. However, I am quite sure that even if it is possible, there are security reasons why it is NOT done .
4. Keep in mind that domain groups are enumerated WHERE THE USER IS DEFINED. This is important in a following scenario:
   1. You have a domain DomA, and a user DomA\user1.
   2. Your DB2 server SrvB is joined to domain DomB.
   3. DomB trusts DomA for the purpose of user authentication, so that DomA\user1 can be authenticated when it connects to a database hosted on SrvB.DomB.
   4. You add DomA\user1 to a domain group DomB\GrpB, expecting that SrvB will see that group membership for user DomA\user1. Unfortunately, that is NOT true, because user1 is defined on DomA. You will; however, get all the groups in DomA to which DomA\user1 belongs. This is because when user1 is authenticated by DomA, DomA passes back an  authentication token . The token is an object that contains (among other data) the groups to which DomA\user1 belongs, and I guess that token is not (or cannot) be updated by DomB.
5. Remember that the authentication token is created/updated  only at the time of the authentication . This means that DB2 will see only those groups to which the user has belonged at the time that user connected to the database. Any subsequent changes to the group membership will not be effective in DB2 until the next time that user establishes a new connection to the database. Any existing sessions will of course not be able to see that change either.
6. Use the  AUTH_LIST_GROUPS_FOR_AUTHID table function  to tell you what groups does DB2 see for a given authorization ID. I do believe that when this table function is called from a session for any target user ID – not necessarily the same as the session user ID – DB2 will attempt to authenticate the target user ID to obtain the authentication token from AD with the enumerated groups. The catch is that this information seems to be cached at that time. This means that any subsequent calls to that table function from the same session and for the same target user ID will return the cached information, which may not be current anymore.

And what does setting DB2_GRP_LOOKUP “correctly” mean? It depends on what you want to achieve…

The DB2_GRP_LOOKUP environment variable controls whether groups are enumerated on the local machine, or where the users are defined (on the local machine if they are a local user, or at the domain level if they are a domain user).

https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.1.0/com.ibm.db2.luw.admin.sec.doc/doc/c0055077.html

Refer to Using an access token to acquire users’ group information (Windows) KC article for details, but basically you have to decide whether you will want to use only local groups, only domain groups, only groups defined where the user is defined (local for local user, domain for domain user) or a combination of the above.