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

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.