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 ViewDescription
SYSCAT.COLAUTHStores column privileges for each grantee. Column privileges are granted through table and view privileges. The two privilege types are Update and Reference.
SYSCAT.DBAUTHStores database authorities for each grantee.
SYSCAT.INDEXAUTHStores index privileges for each grantee.
SYSCAT.PACKAGEAUTHStores package privileges for each grantee.
SYSCAT.PASSTHRUAUTHStores 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.ROUTINEAUTHStores routine privileges for each grantee.
SYSCAT.SCHEMAAUTHStores schema privileges for each grantee.
SYSCAT.SEQUENCEAUTHStores sequence privileges for each grantee.
SYSCAT.TABAUTHStores table privileges for each grantee.
SYSCAT.TBSPACEAUTHStores 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

[View full size image]

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

[View full size image]

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

[View full size image]

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


..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset