The DB2_GRP_LOOKUP environment variable and Db2 group enumeration (Windows)

On Windows, a user can belong to groups defined at the domain level, groups defined on the local machine, or to both.

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). Therefore, when the security administrator grants authorities and privileges, care must be taken that DB2_GRP_LOOKUP is set as intended and the correct users receive the intended authorization.

If the DB2_GRP_LOOKUP profile registry variable is not set:
  1. The Db2 database system first tries to find the user on the same machine.
  2. If the user name is defined locally, the user is authenticated locally.
  3. If the user is not found locally, the Db2 database system attempts to find the user name on it's domain, and then on trusted domains.
For example, consider the following situation where DB2_GRP_LOOKUP is not set:
  1. The domain user DUSER1 is a member of the local group, GROUP1.
  2. The security administrator (who holds SECADM authority) grants DBADM authority to group GROUP1.
    GRANT DBADM ON database TO GROUP GROUP1
  3. Because DB2_GRP_LOOKUP is not set, groups are enumerated where users are defined. So, groups for DUSER1 are enumerated at the domain level. Since DUSER1 does not belong to group GROUP1 at the domain level, DUSER1 does not receive DBADM authority.
Further, consider this more complex scenario involving the UPGRADE DATABASE command where DB2_GRP_LOOKUP is not set:
  1. The domain user DUSER2 is a member of the local Administrators group.
  2. The sysadm_group configuration parameter is not set, therefore members of the local Administrators group automatically hold SYSADM authority.
  3. User DUSER2 is able to issue the UPGRADE DATABASE command (since DUSER2 holds SYSADM authority). The UPGRADE DATABASE command grants DBADM authority on the database being upgraded to the SYSADM group, in this case, the Administrators group.
  4. Because DB2_GRP_LOOKUP is not set, groups are enumerated where users are defined. So, groups for DUSER2 are enumerated at the domain level. Since DUSER2 does not belong to the Administrators group at the domain level, DUSER2 does not receive DBADM authority.
Possible solutions for this scenario are to make one of the following changes:
  • Set DB2_GRP_LOOKUP = local
  • Add the users that should have DBADM authority to the Administrators or GROUP1 group at the Domain Controller.
You can use the SYSPROC.AUTH_LIST_AUTHORITIES_FOR_AUTHID table function to verify the authorities held by a user, as shown in the following example for DUSER1:
SELECT AUTHORITY, D_USER, D_GROUP, D_PUBLIC, ROLE_USER, ROLE_GROUP, ROLE_PUBLIC, D_ROLE 
   FROM TABLE (SYSPROC.AUTH_LIST_AUTHORITIES_FOR_AUTHID ('DUSER1', 'U') ) AS T 
   ORDER BY AUTHORITY
You can use the SYSPROC.AUTH_LIST_GROUPS_FOR_AUTHID table function to verify the groups to which the Db2 database manager has determined a user belongs, as shown in the following example for DUSER1:
SELECT * FROM TABLE (SYSPROC.AUTH_LIST_GROUPS_FOR_AUTHID ('DUSER1')) AS T
Note: If you use the same group name at both the domain level and on the local machine, because the Db2 database manager does not fully qualify the groups, this can lead to confusion.