Retrieving privilege information through views
An ID with the SQLADM, system DBADM, DATAACCESS, ACCESSCTRL, SECADM, SYSADM, or SYSCTRL authority automatically has the privilege of retrieving data from catalog tables. If you do not want to grant the SELECT privilege on all catalog tables to PUBLIC, consider using views to let each ID retrieve information about its own privileges.
About this task
The
following view includes the owner and the name of every table on which
a user's primary authorization ID has the SELECT privilege:
CREATE VIEW MYSELECTS AS
SELECT TCREATOR, TTNAME FROM SYSIBM.SYSTABAUTH
WHERE SELECTAUTH <> ' ' AND
GRANTEETYPE = ' ' AND
GRANTEE IN (USER, 'PUBLIC', CURRENT SQLID);The keyword USER in that statement is equal to the value of the primary authorization ID. To include tables that can be read by a secondary ID, set the current SQLID to that secondary ID before querying the view.
To make the view available to every ID, issue the following GRANT statement:
GRANT SELECT ON MYSELECTS TO PUBLIC;Similar views can show other privileges. This view shows privileges over columns:
CREATE VIEW MYCOLS (OWNER, TNAME, CNAME, REMARKS, LABEL)
AS SELECT DISTINCT TBCREATOR, TBNAME, NAME, REMARKS, LABEL
FROM SYSIBM.SYSCOLUMNS, SYSIBM.SYSTABAUTH
WHERE TCREATOR = TBCREATOR AND
TTNAME = TBNAME AND
GRANTEETYPE = ' ' AND
GRANTEE IN (USER,'PUBLIC',CURRENT SQLID);