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.
EXECUTE privilege on the AUTH_LIST_AUTHORITIES_FOR_AUTHID function.
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.
|
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