Information about each database is automatically maintained in a set of views called the system catalog, which is created when the database is created. This system catalog describes tables, columns, indexes, programs, privileges, and other objects.
The following views and table functions list information about privileges held by users, identities of users granting privileges, and object ownership:
SYSCAT.COLAUTH
Lists the column privileges
SYSCAT.DBAUTH
Lists the database privileges
SYSCAT.INDEXAUTH
Lists the index privileges
SYSCAT.MODULEAUTH
Lists the module privileges
SYSCAT.PACKAGEAUTH
Lists the package privileges
SYSCAT.PASSTHRUAUTH
Lists the server privilege
SYSCAT.ROLEAUTH
Lists the role privileges
SYSCAT.ROUTINEAUTH
Lists the routine (functions, methods, and stored procedures) privileges
SYSCAT.SCHEMAAUTH
Lists the schema privileges
SYSCAT.SEQUENCEAUTH
Lists the sequence privileges
SYSCAT.SURROGATEAUTHIDS
Lists the authorization IDs for which another authorization ID can act as a surrogate.
SYSCAT.TABAUTH
Lists the table and view privileges
SYSCAT.TBSPACEAUTH
Lists the table space privileges
SYSCAT.VARIABLEAUTH
Lists the variable privileges
SYSCAT.WORKLOADAUTH
Lists the workload privileges
SYSCAT.XSROBJECTAUTH
Privileges granted to users by the system will have SYSIBM as the grantor. SYSADM, SYSMAINT SYSCTRL, and SYSMON are not listed in the system catalog.
The CREATE and GRANT statements place privileges in the system catalog. Users with ACCESSCTRL and SECADM authority can grant and revoke SELECT
privilege on the system catalog views.
Retrieving authorization names with granted privileges
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 explicit privileges and the authorization IDs to which they were granted, plus other information, from the PRIVILEGES administrative view:
SELECT AUTHID, PRIVILEGE, OBJECTNAME, OBJECTSCHEMA, OBJECTTYPE FROM SYSIBMADM.PRIVILEGES
The following query uses the AUTHORIZATIONIDS administrative view to find all the authorization IDs that have been granted privileges or authorities, and to show their types:
SELECT AUTHID, AUTHIDTYPE FROM SYSIBMADM.AUTHORIZATIONIDS
You can also use the SYSIBMADM.OBJECTOWNERS administrative view and the SYSPROC.AUTH_LIST_GROUPS_FOR_AUTHID table function to find
security-related information.
Prior to Version 9.1, no single system catalog view contained information about all privileges. For releases earlier than version 9.1, the following statement retrieves all authorization names with privileges:
SELECT DISTINCT GRANTEE, GRANTEETYPE, 'DATABASE' FROM SYSCAT.DBAUTH UNION
SELECT DISTINCT GRANTEE, GRANTEETYPE, 'TABLE ' FROM SYSCAT.TABAUTH UNION
SELECT DISTINCT GRANTEE, GRANTEETYPE, 'PACKAGE ' FROM SYSCAT.PACKAGEAUTH UNION
SELECT DISTINCT GRANTEE, GRANTEETYPE, 'INDEX ' FROM SYSCAT.INDEXAUTH UNION
SELECT DISTINCT GRANTEE, GRANTEETYPE, 'COLUMN ' FROM SYSCAT.COLAUTH UNION
SELECT DISTINCT GRANTEE, GRANTEETYPE, 'SCHEMA ' FROM SYSCAT.SCHEMAAUTH UNION
SELECT DISTINCT GRANTEE, GRANTEETYPE, 'SERVER ' FROM SYSCAT.PASSTHRUAUTH ORDER BY GRANTEE, GRANTEETYPE, 3
Periodically, the list retrieved by this statement should be compared with lists of user and group names defined in the system security facility. You can then identify those authorization names that are no longer valid.
Note: If you are supporting remote database clients, it is possible that the authorization name is defined at the remote client only and not on your database server machine.
Retrieving all names with DBADM authority
The following statement retrieves all authorization names that have been directly granted DBADM authority:
SELECT DISTINCT GRANTEE, GRANTEETYPE FROM SYSCAT.DBAUTH WHERE DBADMAUTH = 'Y'
Retrieving names authorized to access a table
You can use the PRIVILEGES and other administrative views to retrieve
information about the authorization names that have been granted privileges in a database.
The following statement retrieves all authorization names (and their types) that are directly authorized to access the table EMPLOYEE with the qualifier JAMES: SELECT DISTINCT AUTHID, AUTHIDTYPE FROM SYSIBMADM.PRIVILEGES
WHERE OBJECTNAME = 'EMPLOYEE' AND OBJECTSCHEMA = 'JAMES'
For releases earlier than Version 9.1, the following query retrieves the same information:
SELECT DISTINCT GRANTEETYPE, GRANTEE FROM SYSCAT.TABAUTH WHERE TABNAME = 'EMPLOYEE'
AND TABSCHEMA = 'JAMES' UNION
SELECT DISTINCT GRANTEETYPE, GRANTEE FROM SYSCAT.COLAUTH WHERE TABNAME = 'EMPLOYEE'
AND TABSCHEMA = 'JAMES'
To find out who can update the table EMPLOYEE with the qualifier JAMES, issue the following statement:
SELECT DISTINCT GRANTEETYPE, GRANTEE FROM SYSCAT.TABAUTH WHERE TABNAME = 'EMPLOYEE' AND TABSCHEMA = 'JAMES' AND
(CONTROLAUTH = 'Y' OR UPDATEAUTH IN ('G','Y')) UNION
SELECT DISTINCT GRANTEETYPE, GRANTEE FROM SYSCAT.DBAUTH WHERE DBADMAUTH = 'Y'
UNION
SELECT DISTINCT GRANTEETYPE, GRANTEE FROM SYSCAT.COLAUTH WHERE TABNAME = 'EMPLOYEE' AND TABSCHEMA = 'JAMES' AND PRIVTYPE = 'U'
This retrieves any authorization names with DBADM authority, as well as those names to which CONTROL or UPDATE privileges have been directly granted. Remember that some of the authorization names may be groups, not just individual users.
Retrieving all privileges granted to users
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. 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'
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.
The following authorities have SELECT privilege on all catalog tables:
v ACCESSCTRL
v DATAACCESS
v DBADM
v SECADM
v 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:
v SYSADM
v SYSCTRL v SYSMAINT
v 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:
v CREATETAB
v BINDADD
v CONNECT
v IMPLICIT_SCHEMA
v EXECUTE with GRANT on all procedures in schema SQLJ
v BIND on all packages created in the NULLID schema v EXECUTE on all packages created in the NULLID schema v CREATEIN on schema SQLJ
v CREATEIN on schema NULLID v USE on table space USERSPACE1
v SELECT access to the SYSIBM catalog tables v SELECT access to the SYSCAT catalog views
v SELECT access to the SYSIBMADM administrative views v SELECT access to the SYSSTAT catalog views
v UPDATE access to the SYSSTAT catalog views
If you have created a database using the RESTRICTIVE option, and you want to check that the permissions granted to PUBLIC are limited, you can issue the following query to verify which schemas PUBLIC can access:
SELECT DISTINCT OBJECTSCHEMA FROM SYSIBMADM.PRIVILEGES WHERE AUTHID='PUBLIC' OBJECTSCHEMA
--- SYSFUN SYSIBM SYSPROC
To see what access PUBLIC still has to SYSIBM, you can issue the following query to check what privileges are granted on SYSIBM. The results show that only EXECUTE on certain procedures and functions is granted.
SELECT * FROM SYSIBMADM.PRIVILEGES WHERE OBJECTSCHEMA = 'SYSIBM'
AUTHID AUTHIDTYPE PRIVILEGE GRANTABLE OBJECTNAME OBJECTSCHEMA OBJECTTYPE ---... --- --- --- ---... ---... --- PUBLIC G EXECUTE N SQL060207192129400 SYSPROC FUNCTION PUBLIC G EXECUTE N SQL060207192129700 SYSPROC FUNCTION PUBLIC G EXECUTE N SQL060207192129701 SYSPROC
...
PUBLIC G EXECUTE Y TABLES SYSIBM PROCEDURE
PUBLIC G EXECUTE Y TABLEPRIVILEGES SYSIBM PROCEDURE
PUBLIC G EXECUTE Y STATISTICS SYSIBM PROCEDURE
PUBLIC G EXECUTE Y SPECIALCOLUMNS SYSIBM PROCEDURE
PUBLIC G EXECUTE Y PROCEDURES SYSIBM PROCEDURE
PUBLIC G EXECUTE Y PROCEDURECOLS SYSIBM PROCEDURE
PUBLIC G EXECUTE Y PRIMARYKEYS SYSIBM PROCEDURE
PUBLIC G EXECUTE Y FOREIGNKEYS SYSIBM PROCEDURE
PUBLIC G EXECUTE Y COLUMNS SYSIBM PROCEDURE
PUBLIC G EXECUTE Y COLPRIVILEGES SYSIBM PROCEDURE
PUBLIC G EXECUTE Y UDTS SYSIBM PROCEDURE
PUBLIC G EXECUTE Y GETTYPEINFO SYSIBM PROCEDURE
PUBLIC G EXECUTE Y SQLCAMESSAGE SYSIBM PROCEDURE
PUBLIC G EXECUTE Y SQLCAMESSAGECCSID SYSIBM PROCEDURE
Note: The SYSIBMADM.PRIVILEGES administrative view is available starting with Version 9.1 of the DB2 database manager.
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:
v SYSCAT.COLAUTH v SYSCAT.DBAUTH v SYSCAT.INDEXAUTH v SYSCAT.PACKAGEAUTH v SYSCAT.PASSTHRUAUTH v SYSCAT.ROUTINEAUTH v SYSCAT.SCHEMAAUTH v SYSCAT.SECURITYLABELACCESS v SYSCAT.SECURITYPOLICYEXEMPTIONS v SYSCAT.SEQUENCEAUTH v SYSCAT.SURROGATEAUTHIDS v SYSCAT.TABAUTH v SYSCAT.TBSPACEAUTH v SYSCAT.XSROBJECTAUTH v SYSIBMADM.AUTHORIZATIONIDS v SYSIBMADM.OBJECTOWNERS v SYSIBMADM.PRIVILEGES
This would prevent information on 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 wish to revoke SELECT privilege from PUBLIC for the
SYSCAT.COLUMNS and SYSCAT.COLDIST catalog views.
If you wish 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