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:
- 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.
For example, consider the following situation where
DB2_GRP_LOOKUP is
not set:
- 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.
Further, consider this more complex scenario involving the
UPGRADE
DATABASE command where
DB2_GRP_LOOKUP is
not set:
- 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.
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.