Securing the system catalog view

Because the system catalog views describe every object in the database, if you have sensitive data, you might want to restrict their access.

About this task

The following authorities have SELECT privilege on all catalog tables:
  • ACCESSCTRL
  • DATAACCESS
  • DBADM
  • SECADM
  • SQLADM
In addition, the following instance level authorities have the ability to select from SYSCAT.BUFFERPOOLS, SYSCAT.DBPARTITIONGROUPS, SYSCAT.DBPARTITIONGROUPDEF, SYSCAT.PACKAGES, and SYSCAT.TABLES:
  • SYSADM
  • SYSCTRL
  • SYSMAINT
  • SYSMON
You can use the CREATE DATABASE ... RESTRICTIVE command to create a database in which no privileges are automatically granted to PUBLIC. In this case, none of the following normal default grant actions occur:
  • CREATETAB
  • BINDADD
  • CONNECT
  • IMPLICIT_SCHEMA
  • EXECUTE with GRANT on all procedures in schema SQLJ
  • EXECUTE with GRANT on all functions and procedures in schema SYSPROC
  • BIND on all packages created in the NULLID schema
  • EXECUTE on all packages created in the NULLID schema
  • CREATEIN on schema SQLJ
  • CREATEIN on schema NULLID
  • USE on table space USERSPACE1
  • SELECT access to the SYSIBM catalog tables
  • SELECT access to the SYSCAT catalog views
  • SELECT access to the SYSIBMADM administrative views
  • SELECT access to the SYSSTAT catalog views
  • UPDATE access to the SYSSTAT catalog views
If you have created a database using the RESTRICTIVE option, no permissions are granted to PUBLIC. You can run the following query to verify that no schemas are accessibly by PUBLIC: 
SELECT DISTINCT OBJECTSCHEMA FROM SYSIBMADM.PRIVILEGES WHERE AUTHID='PUBLIC'
OBJECTSCHEMA
------------

For releases earlier than Version 9.1 of the Db2® database manager, during database creation, SELECT privilege on the system catalog views is granted to PUBLIC. In most cases, this does not present any security problems. For very sensitive data, however, it may be inappropriate, as these tables describe every object in the database. If this is the case, consider revoking the SELECT privilege from PUBLIC; then grant the SELECT privilege as required to specific users. Granting and revoking SELECT on the system catalog views is done in the same way as for any view, but you must have either ACCESSCTRL or SECADM authority to do this.

At a minimum, if you don't want any user to be able to know what objects other users have access to, you should consider restricting access to the following catalog and administrative views:
  • SYSCAT.COLAUTH
  • SYSCAT.DBAUTH
  • SYSCAT.INDEXAUTH
  • SYSCAT.PACKAGEAUTH
  • SYSCAT.PASSTHRUAUTH
  • SYSCAT.ROUTINEAUTH
  • SYSCAT.SCHEMAAUTH
  • SYSCAT.SECURITYLABELACCESS
  • SYSCAT.SECURITYPOLICYEXEMPTIONS
  • SYSCAT.SEQUENCEAUTH
  • SYSCAT.SURROGATEAUTHIDS
  • SYSCAT.TABAUTH
  • SYSCAT.TBSPACEAUTH
  • SYSCAT.XSROBJECTAUTH
  • SYSIBMADM.AUTHORIZATIONIDS
  • SYSIBMADM.OBJECTOWNERS
  • SYSIBMADM.PRIVILEGES

This would prevent information about user privileges from becoming available to everyone with access to the database.

You should also examine the columns for which statistics are gathered. Some of the statistics recorded in the system catalog contain data values which could be sensitive information in your environment. If these statistics contain sensitive data, you may want to revoke SELECT privilege from PUBLIC for the SYSCAT.COLUMNS and SYSCAT.COLDIST catalog views.

If you want to limit access to the system catalog views, you could define views to let each authorization name retrieve information about its own privileges.

For example, the following view MYSELECTS includes the owner and name of every table on which a user's authorization name has been directly granted SELECT privilege:
   CREATE VIEW MYSELECTS AS
      SELECT TABSCHEMA, TABNAME FROM SYSCAT.TABAUTH
      WHERE GRANTEETYPE = 'U'
        AND GRANTEE = USER
        AND SELECTAUTH = 'Y'

The keyword USER in this statement is equal to the value of the current session authorization name.

The following statement makes the view available to every authorization name:
     GRANT SELECT ON TABLE MYSELECTS TO PUBLIC
And finally, remember to revoke SELECT privilege on the view and base table by issuing the following two statements: 
   REVOKE SELECT ON TABLE SYSCAT.TABAUTH FROM PUBLIC
   REVOKE SELECT ON TABLE SYSIBM.SYSTABAUTH FROM PUBLIC