AUTH_LIST_GROUPS_FOR_AUTHID table function - Retrieve group membership list for a given authorization ID

The AUTH_LIST_GROUPS_FOR_AUTHID table function returns the list of groups of which the given authorization ID is a member.

Syntax

Read syntax diagramSkip visual syntax diagramAUTH_LIST_GROUPS_FOR_AUTHID(authid)

The schema is SYSPROC.

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 function
Column name Data type Description
GROUP VARCHAR(128) The group to which the authorization ID belongs.