Db2 Grant Read Only Access to Database

Administration Guide

---

Using the System Catalog

Information about each database is automatically maintained in a set of views called the system catalog, which is created when the database is generated. This system catalog describes tables, columns, indexes, programs, privileges, and other objects.

Six of these views list the privileges held by users and the identity of the user granting each privilege:

SYSCAT.DBAUTH

Lists the database privileges

SYSCAT.TABAUTH

Lists the table and view privileges

SYSCAT.COLAUTH

Lists the column privileges

SYSCAT.PACKAGEAUTH

Lists the package privileges

SYSCAT.INDEXAUTH

Lists the index privileges

SYSCAT.SCHEMAAUTH

Lists the schema privileges

Privileges granted to users by the system will have SYSIBM as the grantor. SYSADM, SYSMAINT and SYSCTRL are not listed in the system catalog.

The CREATE and GRANT statements place privileges in the system catalog. Users with SYSADM and DBADM authorities can grant and revoke SELECT privilege on the system catalog views. The following examples show how to extract information about privileges by using these SQL queries:

• "Retrieving Authorization Names with Granted Privileges"
• "Retrieving All Names with DBADM Authority"
• "Retrieving Names Authorized to Access a Table"
• "Retrieving All Privileges Granted to Users"
• "Securing the System Catalog Views"

Retrieving Authorization Names with Granted Privileges

No single system catalog view contains information about all privileges. 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    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 FROM SYSCAT.DBAUTH       WHERE DBADMAUTH = 'Y'      

Retrieving Names Authorized to Access a Table

The following statement retrieves all authorization names that are directly authorized to access the table EMPLOYEE with the qualifier JAMES:

        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   = 'Y' OR  UPDATEAUTH   = 'G')    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. However, it will not return the authorization names of users who only hold SYSADM authority.

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. For example, the following statement retrieves a list of the database privileges that have been directly granted to an individual authorization name:

        SELECT * FROM SYSCAT.DBAUTH       WHERE GRANTEE = USER AND GRANTEETYPE = 'U'      

The following statement retrieves a list of the table privileges that were directly granted by a specific user:

        SELECT * FROM SYSCAT.TABAUTH       WHERE GRANTOR  = USER      

The following statement retrieves a list of the individual column privileges that were directly granted by a specific user:

        SELECT * FROM SYSCAT.COLAUTH       WHERE GRANTOR  = USER      

The keyword USER in these statements is always equal to the value of a user's authorization name. USER is a read-only special register. See the SQL Referencefor more information on special registers.

Securing the System Catalog Views

During database creation, SELECT privilege on the system catalog views is granted to PUBLIC. (See "Database Privileges" for other privileges that are automatically 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 SYSADM or DBADM authority to do this.

At a minimum, you should consider restricting access to the SYSCAT.DBAUTH, SYSCAT.TABAUTH, SYSCAT.PACKAGEAUTH, SYSCAT.INDEXAUTH, SYSCAT.COLAUTH, and SYSCAT.SCHEMAAUTH catalog views. This would prevent information on user privileges, which could be used to target an authorization name for break-in, becoming available to everyone with access to the database.

You should also examine the columns for which statistics are gathered (see Chapter 12. "System Catalog Statistics"). 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 always equal to the value of the 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 base table:

        REVOKE SELECT ON TABLE SYSCAT.TABAUTH FROM PUBLIC      

---

[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]

[ DB2 List of Books | Search the DB2 Books ]

Db2 Grant Read Only Access to Database

Source: https://www1.columbia.edu/sec/acis/db2/db2d0/db2d039.htm

Share this post