Section 10.6. Authority and Privilege Metadata

10.6. Authority and Privilege Metadata

Up to this point we have introduced different authorities and privileges. Now we will show you where all these security information is stored and how to easily retrieve it.

Just like most of the information about a database, authorities and privileges metadata is stored in the catalog tables and views listed in Table 10.10. For a complete list of all DB2 catalog tables and descriptions, refer to Appendix D, Using the DB2 System Catalog Tables, or the DB2 UDB SQL Reference manual.

Table 10.10. System Catalog Views Containing Authority and Privilege Metadata

Catalog View	Description
SYSCAT.COLAUTH	Stores column privileges for each grantee. Column privileges are granted through table and view privileges. The two privilege types are Update and Reference.
SYSCAT.DBAUTH	Stores database authorities for each grantee.
SYSCAT.INDEXAUTH	Stores index privileges for each grantee.
SYSCAT.PACKAGEAUTH	Stores package privileges for each grantee.
SYSCAT.PASSTHRUAUTH	Stores information about authorizations to query data sources in pass-through sessions. Pass-through sessions (not discussed in this book) are used in federated database environments.
SYSCAT.ROUTINEAUTH	Stores routine privileges for each grantee.
SYSCAT.SCHEMAAUTH	Stores schema privileges for each grantee.
SYSCAT.SEQUENCEAUTH	Stores sequence privileges for each grantee.
SYSCAT.TABAUTH	Stores table privileges for each grantee.
SYSCAT.TBSPACEAUTH	Stores table space privileges for each grantee.

While querying the catalog views give you everything (and sometimes more than) you want to know, the following are a few commands and tools you will find handy.

From the DB2 CLP, you can obtain the authorities of users connected to the database in the current session with this command:

 get authorizations 

The command extracts and formats information stored in SYSCAT.DBAUTH. It lists the database authorities for the users. In addition to showing the authorities directly granted to the current user, it also shows implicit authorities inherited. Figure 10.32 shows the output of this command.

Figure 10.32. Obtaining database authorities from the Control Center

You can also retrieve the same result from the DB2 Control Center. Right-click on the database you want to know about and then select Authorities (see Figure 10.33). This displays the Database Authorities window (see Figure 10.34), where you can manage database-level authorities for existing and new users and groups.

Figure 10.34. Managing database authorities from the Control Center

NOTE

Recall that user IDs and user groups are defined outside of DB2 (e.g., the operating system of the DB2 server). The user IDs and user groups shown in the Control Center refer to existing users and groups at the external security facility level. To add an existing user to the Control Center, use the Add User button.

Figure 10.33. Output of the get authorizations command

 Administrative Authorizations for Current User  Direct SYSADM authority                      = NO  Direct SYSCTRL authority                     = NO  Direct SYSMAINT authority                    = NO  Direct DBADM authority                       = YES  Direct CREATETAB authority                   = YES  Direct BINDADD authority                     = YES  Direct CONNECT authority                     = YES  Direct CREATE_NOT_FENC authority             = YES  Direct IMPLICIT_SCHEMA authority             = YES  Direct LOAD authority                        = YES  Direct QUIESCE_CONNECT authority             = YES  Direct CREATE_EXTERNAL_ROUTINE authority     = YES  Indirect SYSADM authority                    = YES  Indirect SYSCTRL authority                   = NO  Indirect SYSMAINT authority                  = NO  Indirect DBADM authority                     = NO  Indirect CREATETAB authority                 = YES  Indirect BINDADD authority                   = YES  Indirect CONNECT authority                   = YES  Indirect CREATE_NOT_FENC authority           = NO  Indirect IMPLICIT_SCHEMA authority           = YES  Indirect LOAD authority                      = NO  Indirect QUIESCE_CONNECT authority           = NO  Indirect CREATE_EXTERNAL_ROUTINE authority   = NO 

To manage privileges for each individual database object, right-click on the target object from the Control Center and select Privileges (see Figure 10.35).

Figure 10.35. Managing database object privileges from the Control Center

Using the window shown in Figure 10.36, you can manage the privileges associated to the object. For example, you can grant or revoke particular privileges of a table for a particular user or for all users.

Figure 10.36. Managing database table privileges from the Control Center