Retrieving all privileges granted to users

By making queries on the system catalog views, users can retrieve a list of the privileges they hold and a list of the privileges they have granted to other users.

About this task

You can use the PRIVILEGES and other administrative views to retrieve information about the authorization names that have been granted privileges in a database. For example, the following query retrieves all the privileges granted to the current session authorization ID:

SELECT * FROM SYSIBMADM.PRIVILEGES
 WHERE AUTHID = SESSION_USER AND AUTHIDTYPE = 'U'

The keyword SESSION_USER in this statement is a special register that is equal to the value of the current user's authorization name.

For releases earlier than Version 9.1, the following examples provide similar information. For example, the following statement retrieves a list of the database privileges that have been directly granted to the individual authorization name JAMES:

     SELECT * FROM SYSCAT.DBAUTH
      WHERE GRANTEE = 'JAMES' AND GRANTEETYPE = 'U'

The following statement retrieves a list of the table privileges that were directly granted by the user JAMES:

     SELECT * FROM SYSCAT.TABAUTH
      WHERE GRANTOR  = 'JAMES'

The following statement retrieves a list of the individual column privileges that were directly granted by the user JAMES:

   SELECT * FROM SYSCAT.COLAUTH
      WHERE GRANTOR  = 'JAMES'