Understanding DB2: Learning Visually with Examples (2nd Edition)

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

Related

Категории