Learning Visually with Examples [Electronic resources] نسخه متنی

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 Appendix D, Using the DB2 System Catalog Tables, or the

DB2 UDB SQL Reference manual.

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.

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.

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).

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.