AUTH_LIST_AUTHORITIES_FOR_AUTHID table function
The AUTH_LIST_AUTHORITIES_FOR_AUTHID table function returns all authorities held by the authorization ID, either found in the database configuration file or granted to an authorization ID directly or indirectly through a group or a role.
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
| Column Name | Data Type | Description |
|---|---|---|
| AUTHORITY | VARCHAR(128) | Authority held by the authorization ID |
| D_USER | CHAR(1) | Authority granted directly to the authid,
when the authidtype is a user (U). If the authidtype is
a group (G) or a role (R), then the value is not applicable ('*').
|
| D_GROUP | CHAR(1) | Authority granted directly to the authid when
the authidtype is a group (G), or to the group
to which the authid belongs when the authidtype is
a user (U). If the authidtype is a role (R), then
the value is not applicable ('*').
|
| D_PUBLIC | CHAR(1) | Authority granted directly to the authid called
PUBLIC when the authidtype is a user (U) or a group
(G). If the authidtype is a role (R), then the
value is not applicable ('*').
|
| ROLE_USER | CHAR(1) | Authority
granted directly to a role granted to the authid,
when the authidtype is a user (U). If the authidtype is
a group (G) or a role (R), then the value is not applicable ('*').
The role could be part of a role hierarchy.
|
| ROLE_GROUP | CHAR(1) | Authority granted directly to a role granted
to the authid when the authidtype is
a group (G). If the authidtype is a user (U) or
a role (R), then the value is not applicable ('*'). The role could
be part of a role hierarchy.
|
| ROLE_PUBLIC | CHAR(1) | Authority granted directly to a role granted
to the authid called PUBLIC when the authidtype is
a user (U) or a group (G). If the authidtype is
a role (R), then the value is not applicable ('*'). The role could
be part of a role hierarchy.
|
| D_ROLE | CHAR(1) | Authority granted to a role or to a role granted
to the role. If the authidtype is a user (U) or
a group (G), then the value is not applicable ('*'). The role could
be part of a role hierarchy.
|
Example
Consider user ALICE who by default holds BIND, CONNECT, CREATETAB and IMPLICIT_SCHEMA privileges through special group PUBLIC. ALICE is a member of a group ADMIN1 who has the following system authorities: SYSADM, SYSCTRL and SYSMAINT. She is also a member of group ADMIN2 who has DBADM authority. Also, ALICE has been granted DBADM and SECADM database authorities. Role R1 was granted to ALICE. LOAD authority was granted to role R1. Role R2 was granted to group ADMIN1. CREATE_NOT_FENCED_ROUTINE authority was granted to role R2.
Example 1: Retrieve all authorities user ALICE has granted either directly to her or indirectly through a group, PUBLIC or a role.
SELECT AUTHORITY, D_USER, D_GROUP, D_PUBLIC, ROLE_USER, ROLE_GROUP, ROLE_PUBLIC, D_ROLE
FROM TABLE (SYSPROC.AUTH_LIST_AUTHORITIES_FOR_AUTHID ('ALICE', 'U') ) AS T
ORDER BY AUTHORITY
AUTHORITY D_USER D_GROUP D_PUBLIC ROLE_USER ROLE_GROUP ROLE_PUBLIC D_ROLE
------------------------- ------ ------- -------- --------- ---------- ----------- ------
ACCESSCTRL N N N N N N *
BINDADD N N Y N N N *
CONNECT N N Y N N N *
CREATE_EXTERNAL_ROUTINE N N N N N N *
CREATE_NOT_FENCED_ROUTINE N N N N Y N *
CREATETAB N N Y N N N *
DATAACCESS N N N N N N *
DBADM Y Y N N N N *
EXPLAIN N N N N N N *
IMPLICIT_SCHEMA N N Y N N N *
LOAD N N N Y N N *
QUIESCE_CONNECT N N N N N N *
SECADM Y N N N N N *
SQLADM N N N N N N *
SYSADM * Y * * * * *
SYSCTRL * Y * * * * *
SYSMAINT * Y * * * * *
SYSMON * N * * * * *
WLMADM N N N N N N *
Example 2: Retrieve all authorities group ADMIN1 has granted either directly to it or indirectly through PUBLIC or a role.
SELECT AUTHORITY, D_USER, D_GROUP, D_PUBLIC, ROLE_USER, ROLE_GROUP, ROLE_PUBLIC, D_ROLE
FROM TABLE (SYSPROC.AUTH_LIST_AUTHORITIES_FOR_AUTHID ('ADMIN1', 'G') ) AS T
ORDER BY AUTHORITY
AUTHORITY D_USER D_GROUP D_PUBLIC ROLE_USER ROLE_GROUP ROLE_PUBLIC D_ROLE
------------------------- ------ ------- -------- --------- ---------- ----------- ------
ACCESSCTRL * N * * N * *
BINDADD * N * * N * *
CONNECT * N * * N * *
CREATE_EXTERNAL_ROUTINE * N * * N * *
CREATE_NOT_FENCED_ROUTINE * N * * Y * *
CREATETAB * N * * N * *
DATAACCESS * N * * N * *
DBADM * N * * N * *
EXPLAIN * N * * N * *
IMPLICIT_SCHEMA * N * * N * *
LOAD * N * * N * *
QUIESCE_CONNECT * N * * N * *
SECADM * N * * N * *
SQLADM * N * * N * *
SYSADM * Y * * * * *
SYSCTRL * Y * * * * *
SYSMAINT * Y * * * * *
SYSMON * N * * * * *
WLMADM * N * * N * *
Example 3: Retrieve all authorities special group PUBLIC has granted either directly to it or indirectly through a role
SELECT AUTHORITY, D_USER, D_GROUP, D_PUBLIC, ROLE_USER, ROLE_GROUP, ROLE_PUBLIC, D_ROLE
FROM TABLE (SYSPROC.AUTH_LIST_AUTHORITIES_FOR_AUTHID ('PUBLIC', 'G') ) AS T
ORDER BY AUTHORITY
1 D_USER D_GROUP D_PUBLIC ROLE_USER ROLE_GROUP ROLE_PUBLIC D_ROLE
------------------------- ------ ------- -------- --------- ---------- ----------- ------
ACCESSCTRL * * N * * N *
BINDADD * * Y * * N *
CONNECT * * Y * * N *
CREATE_EXTERNAL_ROUTINE * * N * * N *
CREATE_NOT_FENCED_ROUTINE * * N * * N *
CREATETAB * * Y * * N *
DATAACCESS * * N * * N *
DBADM * * N * * N *
EXPLAIN * * N * * N *
IMPLICIT_SCHEMA * * Y * * N *
LOAD * * N * * N *
QUIESCE_CONNECT * * N * * N *
SECADM * * N * * N *
SQLADM * * N * * N *
SYSADM * * * * * * *
SYSCTRL * * * * * * *
SYSMAINT * * * * * * *
SYSMON * * * * * * *
WLMADM * * N * * N * Example 4: Retrieve all authorities role R1 has granted either directly to it or indirectly through a role. Consider in this case that role R2 was also granted to role R1.
SELECT AUTHORITY, D_USER, D_GROUP, D_PUBLIC, ROLE_USER, ROLE_GROUP, ROLE_PUBLIC, D_ROLE
FROM TABLE (SYSPROC.AUTH_LIST_AUTHORITIES_FOR_AUTHID ('R1', 'R') ) AS T
ORDER BY AUTHORITY
AUTHORITY D_USER D_GROUP D_PUBLIC ROLE_USER ROLE_GROUP ROLE_PUBLIC D_ROLE
------------------------- ------ ------- -------- --------- ---------- ----------- ------
ACCESSCTRL * * * * * * N
BINDADD * * * * * * N
CONNECT * * * * * * N
CREATE_EXTERNAL_ROUTINE * * * * * * N
CREATE_NOT_FENCED_ROUTINE * * * * * * Y
CREATETAB * * * * * * N
DATAACCESS * * * * * * N
DBADM * * * * * * N
EXPLAIN * * * * * * N
IMPLICIT_SCHEMA * * * * * * N
LOAD * * * * * * Y
QUIESCE_CONNECT * * * * * * N
SECADM * * * * * * N
SYSADM * * * * * * *
SQLADM * * * * * * N
SYSCTRL * * * * * * *
SYSMAINT * * * * * * *
SYSMON * * * * * * *
WLMADM * * * * * * N
Usage notes
The output of AUTH_LIST_AUTHORITIES_FOR_AUTHID
table function depends on the authidtype. For example,
for an authidtype of USER, it returns all authorities
that authid holds through any means:
- granted directly to the authid
- granted to any group (or roles granted to the group) to which authid belongs
- granted to any role (or roles granted to the role) granted to authid
- granted to PUBLIC (or roles granted to PUBLIC)