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'