DB2 10.5 for Linux, UNIX, and Windows

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