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.

Examples

The following example shows the command syntax for retrieving the granted privilege and object name, schema, and type values for all authorization IDs.

Attention: This example applies to Db2® 11.5.3 and earlier on-premises releases.

SELECT AUTHID, PRIVILEGE, OBJECTNAME, OBJECTSCHEMA, OBJECTTYPE
   FROM SYSIBMADM.PRIVILEGES

The following example shows the output from the SELECT statement.

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
...

The following example shows the command syntax for retrieving the granted privilege and object name, schema, and type values for all authorization IDs.

SELECT AUTHID, PRIVILEGE, OBJECTNAME, OBJECTSCHEMA, OBJECTTYPE, PARENTOBJECTNAME, PARENTOBJECTTYPE FROM SYSIBMADM.PRIVILEGES

The following example shows the output from the SELECT statement.


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)	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)	Database object type of the parent object.

Updating SYSIBMADM.PRIVILEGES

Use Db2 service tool db2updv115 to update to the latest fix pack with this command:

db2updv115 -d testdb