DB2 10.5 for Linux, UNIX, and Windows

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.

The schema is SYSIBMADM.

Authorization

One of the following authorizations is required:

SELECT privilege on the PRIVILEGES administrative view
CONTROL privilege on the PRIVILEGES administrative view
DATAACCESS authority
DBADM authority
SQLADM 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.