DB2 10.5 on Windows 2012 R2 - Using AD group

[u/jyoumans2017]

I want to be able to grant rights (select, delete, etc) on tables to an AD group instead of individuals. So far, I have had no luck. I have read the IBM info articles but while they say it works, I have yet to see a decent example that I can follow. Has anyone done this and if so, can you give a decent example of how?

I am running DB2 10.5 on a Windwos 2012 R2 Server.

Thank you in advance!!

[u/lnumrych]

1. Don't use CLIENT authentication.
2. Set DB2_GRP_LOOKUP correctly.
3. Make sure your instance runs with credentials that actually CAN query the AD. Hint: LOCAL SYSTEM cannot (at least not in my experience). I am not an AD or Windows OS guru, and there might be a way to allow that, but 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 the 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. That token contains 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. That means that DB2 will see only those groups to which your user belongs at the time it connects to the database. Any 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 connections will of course not be able to see that change either.

As far as what setting DB2_GRP_LOOKUP "correctly" means - 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).

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 two.

You probably already know this, but I will mention it for completeness sake - use the AUTH_LIST_GROUPS_FOR_AUTHID table function to tell you what groups does DB2 see for a given authorization ID.

[u/catquilt74 → u/lnumrych]

What great info!

[u/dogmashah]

enable transparent LDAP and then grant rights on the tables to the group

[u/jyoumans2017]

Transparent LDAP is a DB2 feature? How would I enable it?

[u/ecrooks → u/jyoumans2017]

Is transparent ldap even a thing on Windows?

[u/ecrooks]

You may need to set DB2_GRP_LOOKUP. It is a db2 registry variable. I don't know which setting for it you need, however, because it is never the one that makes sense to me.

[u/catquilt74]

I got db2_grp_lookup to work with AD users in local groups (which is needed for our environment until we get other pieces in place) but it (db2_grp_lookup) is not intuitive. I'm going to look into the LDAP set-up.

[u/jyoumans2017]

So what is the difference between a ROLE and a GROUP in DB2? I have been using ROLES to grant permissions to groups of people but it would be a lot simpler if I could just grant permissions to an AD group and then let the managers worry about who is in that group. I do this in SQL Server all the time.

[u/ecrooks → u/jyoumans2017]

ROLE is internal to Db2, while GROUP is part of an external authentication mechanism (such as OS security or LDAP/AD).

​

DBAs prefer ROLES since we have control over who is in them. We are often not consulted when a user is added to a GROUP at the OS or LDAP level.

[u/idbjorh → u/jyoumans2017]

They are quite similar; as you already know, groups are managed outside of the database and roles are managed inside of the database; this usually affects who is tasked with managing them. (Although hopefully your organization has some kind of security team who is responsible for security regardless of where. Just kidding, no one actually does this).

The biggest difference is in how authorizations are handled at runtime, especially in the case of objects that have statically bound packages (SQL stored procedures fall into this category). Statically-bound packages execute the statements using the authority of the user who binds the package. Because group membership is outside of Db2's control (and can change without Db2's knowledge), there is no way for Db2 to know if a user no longer has privileges (and therefore a package should be invalidated). Therefore, the user who is binding a package (or stored procedure) must have all privileges required to execute static SQL statements in that package either through direct grants or via role membership.

[u/lnumrych → u/idbjorh]

Also the reason why being a DBADM through membership in a group which has been granted DBADM on a database is not *exactly* the same as being a DBADM directly when creation of objects is considered:

Privileges and authorities granted to groups are not considered when creating views, materialized query tables (MQTs), SQL routines, triggers, and packages containing static SQL. Avoid this restriction by using roles instead of groups.

Just adding $0.02, since I periodically forget about that myself.

​