The AUTH_LIST_GROUPS_FOR_AUTHID table function returns
the list of groups of which the given authorization ID is a member.
Syntax
>>-AUTH_LIST_GROUPS_FOR_AUTHID--(--authid--)-------------------><
Table function parameter
- authid
- An input argument of type VARCHAR(128) that specifies the authorization
ID being queried. The authorization ID can only represent a user.
If authid does not exist, is NULL or empty
string, an empty result table is returned.
Authorization
One of the following authorities is required to execute the routine:
- EXECUTE privilege on the routine
- DATAACCESS authority
- DBADM authority
- SQLADM authority
Default PUBLIC privilege
In a non-restrictive
database, EXECUTE privilege is granted to PUBLIC when the function
is automatically created.
Example
Retrieve all groups that AMY belongs to.
SELECT * FROM TABLE (SYSPROC.AUTH_LIST_GROUPS_FOR_AUTHID('AMY')) AS T
The following is an example of output for this query.
GROUP
-------------------------...-------------
BUILD
PDXDB2
2 record(s) selected.
Usage notes
Group information returned might
be different than expected for the following reasons:
- In a Windows Active Directory
environment, the database manager:
- supports one level of group nesting within a local group, except
the nesting of a domain local group within a local group. For example,
if authid belongs to the global group G1,
and G1 belongs to the local group L1, the local group L1 is returned
as the group for authid. However, if authid belongs to the domain local group DL1,
and DL1 belongs to the local group L1, no group information is returned
for authid.
- does not support any nesting of global groups. For example, if authid belongs to the global G2, and G2 belongs
to the global G3, only G2 is returned as the group for authid.
- The registry variable DB2_GRP_LOOKUP specifies
which Windows security mechanism
is used to enumerate the groups to which a user belongs.
- For an authorization ID that belongs to a particular domain, if
the domain is not specified as part of the authid, and both a local and domain authid exist with the same name, the groups for the local authorization
ID is returned.
- If the call to AUTH_LIST_GROUPS_FOR_AUTHID is
for the same authid as the connected user, then
it will return the groups for the connected user. For example, If
AMY exists as a local user and as a domain user and the domain user
AMY has connected to the database, then AUTH_LIST_GROUPS_FOR_AUTHID
will return the groups to which the domain AMY belongs to.
Information returned
Table 1. Information returned by the AUTH_LIST_GROUPS_FOR_AUTHID table functionColumn name |
Data type |
Description |
GROUP |
VARCHAR(128) |
The group to which the authorization ID belongs. |