Determining which tables you have access to

You can ask Db2 to list the tables that a specific authorization ID has access to.

About this task

The contents of the Db2 catalog tables can be a useful reference tool when you begin to develop an SQL statement or an application program.

The catalog table, SYSIBM.SYSTABAUTH, lists table privileges that are granted to authorization IDs. To display the tables that you have authority to access (by privileges granted either to your authorization ID or to PUBLIC), you can execute an SQL statement similar to the one shown in the following example. To do this, you must have the SELECT privilege on SYSIBM.SYSTABAUTH.

Procedure

To display the tables that you have authority to access (by privileges granted either to your authorization ID or to PUBLIC):

Issue a SELECT statement similar to the following example. To do this, you must have the SELECT privilege on SYSIBM.SYSTABAUTH.

SELECT DISTINCT TCREATOR, TTNAME
  FROM SYSIBM.SYSTABAUTH
  WHERE GRANTEE IN (USER, 'PUBLIC', 'PUBLIC*') AND GRANTEETYPE = ' ';

In this query, the predicate GRANTEETYPE = ' ' selects authorization IDs.

Exception: If your Db2 subsystem uses an exit routine for access control authorization, you cannot rely on catalog queries to tell you the tables that you can access. When such an exit routine is installed, both RACF® and Db2 control table access.