AUTH_LIST_ROLES_FOR_AUTHID function - Returns the list of roles

The AUTH_LIST_ROLES_FOR_AUTHID function returns the list of roles in which the given authorization ID is a member.

Syntax

The schema is SYSPROC.

Table function parameters

authid

An input argument of type VARCHAR(128) that specifies the authorization ID being queried. The authorization ID can be a user, group or a role. If authid is NULL or an empty string, an empty result table is returned.

authidtype

An input argument of type VARCHAR(1) that specifies the authorization ID type being queried. If authidtype does not exist, is NULL or an empty string, an empty result table is returned. Possible values for authidtype are:

G: Group
R: Role
U: User

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.

Information returned

Table 1. The result sets for AUTH_LIST_ROLES_FOR_AUTHID
Column Name	Data Type	Description
GRANTOR	VARCHAR(128)	Grantor of the role.
GRANTORTYPE	CHAR(1)	Type of grantor: U = Grantor is an individual user
GRANTEE	VARCHAR(128)	User granted the role.
GRANTEETYPE	CHAR(1)	Type of grantee: G = Grantee is a group R= Grantee is a role U= Grantee is a user
ROLENAME	VARCHAR(128)	Name of the role granted to the authorization ID directly or indirectly through a group or another role.
CREATE_TIME	TIMESTAMP	Time when role was created.
ADMIN	CHAR(1)	Privilege to grant the role to, revoke the role from, or to comment on a role: N = Not held Y= Held

Example

Consider granting role INTERN to role DOCTOR and role DOCTOR to role SPECIALIST, then grant role SPECIALIST to user ALICE. ALICE belongs to group HOSPITAL and role EMPLOYEE is granted to group HOSPITAL. ALICE also belongs to special group PUBLIC and role PATIENTS is granted to PUBLIC.

Example 1: Retrieve all roles granted to user ALICE.

  SELECT GRANTOR, GRANTORTYPE, GRANTEE, GRANTEETYPE, ROLENAME, 
    CREATE_TIME, ADMIN 
  FROM TABLE (SYSPROC.AUTH_LIST_ROLES_FOR_AUTHID ('ALICE', 'U') ) AS T

The following is an example of output for this query.

GRANTOR GRANTORTYPE GRANTEE    GRANTEETYPE ROLENAME   CREATE_TIME                ADMIN
------- ----------- -------    ----------- --------   -------------------------- -----
ZURBIE  U           DOCTOR     R           INTERN     2006-08-01-15.09.58.537399 N    
ZURBIE  U           SPECIALIST R           DOCTOR     2006-08-01-15.10.04.540660 N    
ZURBIE  U           ALICE      U           SPECIALIST 2006-08-01-15.10.08.776218 N    
ZURBIE  U           HOSPITAL   G           EMPLOYEE   2006-08-01-15.10.14.277576 N    
ZURBIE  U           PUBLIC     G           PATIENTS   2006-08-01-15.10.18.878609 N    

  5 record(s) selected.

Example 2: Retrieve all roles granted to group HOSPITAL.

  SELECT GRANTOR, GRANTORTYPE, GRANTEE, GRANTEETYPE, ROLENAME, 
    CREATE_TIME, ADMIN 
  FROM TABLE (SYSPROC.AUTH_LIST_ROLES_FOR_AUTHID ('HOSPITAL', 'G') ) AS T

The following is an example of output for this query.

GRANTOR GRANTORTYPE GRANTEE    GRANTEETYPE ROLENAME   CREATE_TIME                ADMIN
------- ----------- -------    ----------- --------   -------------------------- -----
ZURBIE  U           HOSPITAL   G           EMPLOYEE   2006-08-01-15.10.14.277576 N    

  1  record(s) selected.

Example 3: Retrieve all roles granted to role SPECIALIST.

  SELECT GRANTOR, GRANTORTYPE, GRANTEE, GRANTEETYPE, ROLENAME, 
    CREATE_TIME, ADMIN 
  FROM TABLE (SYSPROC.AUTH_LIST_ROLES_FOR_AUTHID ('SPECIALIST', 'R') ) AS T

The following is an example of output for this query.

GRANTOR GRANTORTYPE GRANTEE    GRANTEETYPE ROLENAME   CREATE_TIME                ADMIN
------- ----------- -------    ----------- --------   -------------------------- -----
ZURBIE  U           DOCTOR     R           INTERN     2006-08-01-15.09.58.537399 N    
ZURBIE  U           SPECIALIST R           DOCTOR     2006-08-01-15.10.04.540660 N    

  2  record(s) selected.

Example 4: Retrieve all roles granted to group PUBLIC

  SELECT GRANTOR, GRANTORTYPE, GRANTEE, GRANTEETYPE, ROLENAME, 
    CREATE_TIME, ADMIN 
  FROM TABLE (SYSPROC.AUTH_LIST_ROLES_FOR_AUTHID ('PUBLIC', 'G') ) AS T

The following is an example of output for this query.

GRANTOR GRANTORTYPE GRANTEE    GRANTEETYPE ROLENAME   CREATE_TIME                ADMIN
------- ----------- -------    ----------- --------   -------------------------- -----
ZURBIE  U           PUBLIC     G           PATIENTS   2006-08-01-15.10.18.878609 N    

  1 record(s) selected.

Usage notes

The output of AUTH_LIST_ROLES_FOR_AUTHID table function depends on the AUTHIDTYPE:

For a user it returns the roles granted to the user directly or indirectly through another roles, groups that the user belongs to (or PUBLIC).
For a group it returns the roles granted to the group, directly or indirectly through another roles.
For a role it returns the roles granted to the role, directly or indirectly through another roles.