PRIVILEGES administrative view - Retrieve privilege information

The PRIVILEGES administrative view returns all explicit privileges for all authorization IDs defined in the system catalogs from the currently connected database. For column privileges, see the SYSCAT.COLAUTH catalog view.

The schema is SYSIBMADM.

Authorization

One of the following authorizations is required:
  • SELECT privilege on the PRIVILEGES administrative view
  • SELECTIN privilege on the schema of the view
  • CONTROL privilege on the PRIVILEGES administrative view
  • DATAACCESS authority
  • DBADM authority
  • SQLADM authority
  • ACCESSCTRL authority
  • SECADM authority

Default PUBLIC privilege

In a non-restrictive database, SELECT privilege is granted to PUBLIC when the view is automatically created.

Example

Retrieve the privilege granted along with the object name, schema and type, for all authorization IDs. 
SELECT AUTHID, PRIVILEGE, OBJECTNAME, OBJECTSCHEMA, OBJECTTYPE
   FROM SYSIBMADM.PRIVILEGES
The following is an example of output for this query. 
AUTHID       PRIVILEGE   OBJECTNAME              OBJECTSCHEMA     OBJECTTYPE
--------...- ----------- -------------------...- ------------...- ----------...-
JESSICAE     EXECUTE     SQLE0F00                NULLID           PACKAGE
PUBLIC       EXECUTE     SYSSH201                NULLID           PACKAGE
JESSICAE     EXECUTE     SYSSH202                NULLID           PACKAGE
PUBLIC       EXECUTE     SYSSH202                NULLID           PACKAGE
DOCTOR       EXECUTE     PKG0123                 NULLID           PACKAGE
...
PUBLIC       EXECUTE     SQL051109185227800      SYSPROC          FUNCTION
JESSICAE     EXECUTE     SQL051109185227801      SYSPROC          FUNCTION
PUBLIC       EXECUTE     SQL051109185227801      SYSPROC          FUNCTION
JESSICAE     EXECUTE     SQL051109185227838      SYSPROC          FUNCTION
PUBLIC       EXECUTE     SQL051109185227838      SYSPROC          FUNCTION
...                                                               
PUBLIC       EXECUTE     LIST_SRVR_TYPES         SYSPROC          PROCEDURE
PUBLIC       EXECUTE     LIST_SRVR_VERSIONS      SYSPROC          PROCEDURE
PUBLIC       EXECUTE     LIST_WRAP_OPTIONS       SYSPROC          PROCEDURE
PUBLIC       EXECUTE     LIST_SRVR_OPTIONS       SYSPROC          PROCEDURE
...                                                               
SYSTEM                   POLICY_UNQ              SYSTOOLS         INDEX
PUBLIC       CREATEIN                            NULLID           SCHEMA
PUBLIC       UPDATE      COLUMNS                 SYSSTAT          VIEW
PUBLIC       UPDATE      COLGROUPS               SYSSTAT          VIEW
...

Example 2

Attention: The following example applies to the container-only release of Db2® Version 11.5 Mod Pack 1 or later versions as well as on premise Version 11.5 Mod Pack 4 or later versions.
Retrieve the privilege granted along with the object name, schema and type, for all authorization IDs. 
SELECT AUTHID, PRIVILEGE, OBJECTNAME, OBJECTSCHEMA, OBJECTTYPE, PARENTOBJECTTYPE FROM SYSIBMADM.PRIVILEGES
The following is an example of output for this query. 

AUTHID       PRIVILEGE   OBJECTNAME              OBJECTSCHEMA     OBJECTTYPE    PARENTOBJECTNAME     PARENTOBJECTTYPE
--------...- ----------- -------------------...- ------------...- ----------...------------------...------------------
YALEI        CONTROL     SQLUKP0C                NULLID           DB2 PACKAGE
PUBLIC       BIND        SYSSN402                NULLID           DB2 PACKAGE
...                                                                
YALEI        EXECUTE     SQL190912144036249      SYSPROC          FUNCTION
PUBLIC       EXECUTE     SQL190912144036250      SYSPROC          FUNCTION
...
PUBLIC       EXECUTE     DROP_STGMGMT_TAB        SYSPROC          PROCEDURE
PUBLIC       EXECUTE     CREATE_STGMGMT_TAB      SYSPROC          PROCEDURE
...                                                                
USER1        REFERENCE   NAME                    YALEI            COLUMN           T1                 TABLE
USER1        UPDATE      ID                      YALEI            COLUMN           T1                 TABLE
...                                                               
GROUP1       SET SESSION USER1                                    USER
USER1        SET SESSION PUBLIC                                   GROUP

Information returned

Table 1. Information returned by the PRIVILEGES administrative view
Column name Data type Description
AUTHID VARCHAR(128) Authorization ID that has been explicitly granted this privilege.
AUTHIDTYPE CHAR(1) Authorization ID type:
  • U: user
  • R: role
  • G: group
PRIVILEGE VARCHAR(11) Privilege that has been explicitly granted to this authorization ID.
GRANTABLE VARCHAR(1) Indicates if the privilege is grantable:
  • Y: Grantable
  • N: Not grantable
OBJECTNAME VARCHAR(128) object_name - Object name monitor element
OBJECTSCHEMA VARCHAR(128) object_schema - Object schema monitor element
OBJECTTYPE VARCHAR(24) Database object type.
PARENTOBJECTNAME VARCHAR(128)
Attention: This feature applies to the container-only release of Db2 Version 11.5 Mod Pack 1 or later versions.
Name of the parent object which the object belongs to. For example, if object type is a column, the parent object is the table to which the column belongs.
PARENTOBJECTTYPE VARCHAR(24)
Attention: This feature applies to the container-only release of Db2 Version 11.5 Mod Pack 1 or later versions.
Database object type of the parent object.

Updating SYSIBMADM.PRIVILEGES

Attention: This feature applies to the container-only release of Db2 Version 11.5 Mod Pack 1 or later versions.
Use Db2 service tool db2updv115 to update to the latest fix pack with this command: 
db2updv115 -d testdb