The AUTH_LIST_ROLES_FOR_AUTHID function returns the list of roles in which the given authorization ID is a member.
In a non-restrictive database, EXECUTE privilege is granted to PUBLIC when the function is automatically created.
Column Name | Data Type | Description |
---|---|---|
GRANTOR | VARCHAR(128) | Grantor of the role. |
GRANTORTYPE | CHAR(1) | Type of grantor:
|
GRANTEE | VARCHAR(128) | User granted the role. |
GRANTEETYPE | CHAR(1) | Type of grantee:
|
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:
|
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.
SELECT GRANTOR, GRANTORTYPE, GRANTEE, GRANTEETYPE, ROLENAME,
CREATE_TIME, ADMIN
FROM TABLE (SYSPROC.AUTH_LIST_ROLES_FOR_AUTHID ('ALICE', 'U') ) AS T
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.
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
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.
SELECT GRANTOR, GRANTORTYPE, GRANTEE, GRANTEETYPE, ROLENAME,
CREATE_TIME, ADMIN
FROM TABLE (SYSPROC.AUTH_LIST_ROLES_FOR_AUTHID ('PUBLIC', 'G') ) AS T
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.
The output of AUTH_LIST_ROLES_FOR_AUTHID table function depends on the AUTHIDTYPE: