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.
- The Db2 database system first tries to find the user on the same machine.
- If the user name is defined locally, the user is authenticated locally.
- 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.
- The domain user DUSER1 is a member of the local group, GROUP1.
- The security administrator (who holds SECADM authority) grants
DBADM authority to group GROUP1.
GRANT DBADM ON database TO GROUP GROUP1
- 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.
- The domain user DUSER2 is a member of the local Administrators group.
- The sysadm_group configuration parameter is not set, therefore members of the local Administrators group automatically hold SYSADM authority.
- 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.
- 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.
- Set DB2_GRP_LOOKUP = local
- Add the users that should have DBADM authority to the Administrators or GROUP1 group at the Domain Controller.
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
SELECT * FROM TABLE (SYSPROC.AUTH_LIST_GROUPS_FOR_AUTHID ('DUSER1')) AS T