DB2 Universal Database V8.1 Certification Exam 700 Study Guide

There are three different ways that users (and groups) can obtain database-level authorities and database/object privileges. They are:

Implicitly. When a user creates a database, they implicitly receive DBADM authority for that database, along with several database privileges. Likewise, when a user creates a database object, they implicitly receive all privileges available for that object along with the ability to grant any combination of those privileges (with the exception of the CONTROL privilege), to other users and groups. Privileges can also be implicitly given whenever a higher-level privilege is explicitly granted to a user (for example, if a user is explicitly given CONTROL privilege for a tablespace, they will implicitly receive the USE privilege for that tablespace as well). Keep in mind that such implicitly assigned privileges are not automatically revoked when the higher-level privilege that caused them to be granted is revoked .

Indirectly. Indirectly assigned privileges are usually associated with packages; when a user executes a package that requires privileges to execute that the user does not have (for example, a package that deletes a row of data from a table requires the DELETE privilege on that table), the user is indirectly given those privileges for the express purpose of executing the package. Indirectly granted privileges are temporary and do not exist outside the scope in which they are granted.

Explicitly. Database-level authorities, database privileges, and object privileges can be explicitly given to or taken from an individual user or a group of users by any user that has the authority to do so. To explicitly grant privileges on most database objects, a user must have SYSADM authority, DBADM authority, or CONTROL privilege on that object. Alternately, a user can explicitly grant any privilege they were assigned with the WITH GRANT OPTION specified. To grant CONTROL privilege for any object, a user must have SYSADM or DBADM authority; to grant DBADM authority, a user must have SYSADM authority.

Granting and Revoking Authorities and Privileges from the Control Center

One way to explicitly grant and revoke database-level authorities, as well as several available privileges, is by using the various authorities and privileges management dialogs that are provided with the Control Center. These dialogs are activated by highlighting the appropriate database or object name shown in the Control Center panes and selecting either Authorities or Privileges from the corresponding database or object menu. Figure 3-15 shows the menu items that must be selected in the Control Center in order to activate the Table Privileges dialog for a particular table. Figure 3-16 shows how the Table Privileges dialog might look immediately after a table is first created. (A single check mark under a privilege means that the individual or group shown has been granted that privilege; a double check mark means the individual or group has also been granted the ability to grant that privilege to other users and groups.)

To assign privileges to an individual user from the Table Privileges dialog (or a similar authorities/privileges dialog), you simply identify a particular user by highlighting their entry in the recognized users list ”if the desired user is not in the list, they can be added by selecting the "Add User" push button ”and assign the appropriate privileges (or authorities) using the "Privileges" (or "Authorities") drop-down list or the "Grant All" or "Revoke All" push buttons . To assign privileges to a group of users, you select the "Group" tab to display a list of recognized groups and repeat the process (using the "Add Group" push button instead of the "Add User" push button to add a desired group to the list if they are not already there).

Granting Authorities and Privileges with the GRANT SQL Statement

Not all privileges can be explicitly given to users/groups with the privileges management dialogs available. However, in situations where no privileges dialog exists (and in situations where you elect not to use the Control Center), database-level authorities and database/object privileges can be explicitly given to users and/or groups by executing the appropriate form of the GRANT SQL statement. The syntax for the GRANT SQL statement varies according to the authority or privilege being granted ”the following sections show the syntax used to grant each database-level authority and database/object privilege available.

Database-level authorities and privileges

GRANT [DBADM Privilege, ... ] ON DATABASE TO [ Recipient , ...]

where:

Schema privileges

GRANT [ Privilege, ... ] ON SCHEMA [ SchemaName ] TO [ Recipient , ...] <WITH GRANT OPTION>

where:

Tablespace privilege

GRANT USE OF TABLESPACE [ TablespaceName ] TO [ Recipient , ...] <WITH GRANT OPTION>

where:

Table privileges

GRANT [ALL <PRIVILEGES> Privilege <( ColumnName, ... )> , ... ] ON TABLE [ TableName ] TO [ Recipient , ...] <WITH GRANT OPTION>

where:

Index privilege

GRANT CONTROL ON INDEX [ IndexName ] TO [ Recipient , ...]

where:

View privileges

GRANT [ALL <PRIVILEGES> Privilege <( ColumnName, ... )> , ... ] ON [ ViewName ] TO [ Recipient , ...] <WITH GRANT OPTION>

where:

Package privileges

[View full width]

[View full width]

where:

Routine privileges

GRANT EXECUTE ON [ RoutineName FUNCTION < SchemaName .> * METHOD * FOR [ TypeName ] METHOD * FOR < SchemaName .> * PROCEDURE < SchemaName .> *] TO [ Recipient , ...] <WITH GRANT OPTION>

where:

Sequence privilege

GRANT USAGE ON SEQUENCE [ SequenceName ] TO PUBLIC

where:

Server privilege

GRANT PASSTHRU ON SERVER [ ServerName ] TO [ Recipient , ...]

where:

Nickname privileges

[View full width]

[View full width]

where:

If the WITH GRANT OPTION clause is specified with the GRANT statement, the user and/or group receiving the privileges specified is given the ability to grant the privilege received (except for the CONTROL privilege) to other users. In all cases, the value specified for the Recipient parameter can be any combination of the following:

GRANT SQL statement examples

Now that we've seen the basic syntax for the various forms of the GRANT SQL statement, let's take a look at some examples.

Example 1. A server has both a user and a group named TESTER. Give the group TESTER the ability to bind applications to the database SAMPLE:

CONNECT TO SAMPLE GRANT BINDADD ON DATABASE TO GROUP tester

Example 2. Give all table privileges available (except CONTROL privilege) for the table PAYROLL.EMPLOYEE to the group PUBLIC:

GRANT ALL PRIVILEGES ON TABLE payroll.employee TO PUBLIC

Example 3. Give user USER1 and user USER2 the privileges needed to perform just DML operations on the table DEPARTMENT using the view DEPTVIEW:

GRANT SELECT, INSERT, UPDATE, DELETE ON deptview TO USER user1, USER user2

Example 4. Give user JOHN_DOE the privilege needed to query the table INVENTORY, along with the ability to grant this privilege to other users whenever appropriate:

GRANT SELECT ON TABLE inventory TO john_doe WITH GRANT OPTION

Example 5. Give user USER1 the ability to run an embedded SQL application that requires package GET_INVENTORY:

GRANT EXECUTE ON PACKAGE get_inventory TO USER user1

Example 6. Give user USER1 the ability to use a user-defined function named PAYROLL.CALC_SALARY that has an input parameter of type CHAR(5) in a query:

GRANT EXECUTE ON FUNCTION payroll.calc_salary(CHAR(5)) TO USER user1

Example 7. Give user USER1 the ability to define a referential constraint between the tables EMPLOYEE and DEPARTMENT using column EMPID in table EMPLOYEE as the parent key:

GRANT REFERENCES(empid) ON TABLE employee TO USER user1

Example 8. Give the group PUBLIC the ability to modify information stored in the ADDRESS and HOME_PHONE columns of the table EMP_INFO:

GRANT UPDATE(address, home_phone) ON TABLE emp_info TO PUBLIC

Revoking Authorities and Privileges with the REVOKE SQL Statement

Just as there is an SQL statement that can be used to grant database-level authorities and database/object privileges, there is an SQL statement that can be used to revoke database-level authorities and database/object privileges. This statement is the REVOKE SQL statement, and like the GRANT statement, the syntax for the REVOKE statement varies according to the authority or privilege being revoked ”the following sections show the syntax used to revoke each database-level authority and database/object privilege available.

Database-level authorities and privileges

REVOKE [DBADM Privilege, ... ] ON DATABASE FROM [ Forfeiter , ...] <BY ALL>

where:

Schema privileges

REVOKE [ Privilege, ... ] ON SCHEMA [ SchemaName ] FROM [ Forfeiter , ...] <BY ALL>

where:

Tablespace privilege

REVOKE USE OF TABLESPACE [ TablespaceName ] FROM [ Forfeiter , ...] <BY ALL>

where:

Table privileges

REVOKE [ALL <PRIVILEGES> Privilege, ... ] ON TABLE [ TableName ] FROM [ Forfeiter , ...] <BY ALL>

where:

Index privilege

REVOKE CONTROL ON INDEX [ IndexName ] FROM [ Forfeiter , ...] <BY ALL>

where:

View privileges

REVOKE [ALL <PRIVILEGES> Privilege, ... ] ON [ ViewName ] FROM [ Forfeiter , ...] <BY ALL>

where:

Package privileges

REVOKE [ Privilege, ... ] ON PACKAGE < SchemaName .> [ PackageID ] FROM [ Forfeiter , ...] <BY ALL>

where:

Routine privileges

REVOKE EXECUTE ON [ RoutineName FUNCTION < SchemaName .> * METHOD * FOR [ TypeName ] METHOD * FOR < SchemaName .> * PROCEDURE < SchemaName .> *] FROM [ Forfeiter , ...] <BY ALL> RESTRICT

where:

Sequence privilege

REVOKE USAGE ON SEQUENCE [ SequenceName ] FROM PUBLIC

where:

Server privilege

REVOKE PASSTHRU ON SERVER [ ServerName ] FROM [ Forfeiter , ...] <BY ALL>

where:

Nickname privileges

REVOKE [ALL <PRIVILEGES> Privilege, ... ] ON [ Nickname ] FROM [ Forfeiter , ...] <BY ALL>

where:

The BY ALL syntax is optional and is provided as a courtesy for administrators who are familiar with the syntax of the DB2 for OS/390 REVOKE SQL statement. Whether it is included or not, the results will always be the same ”the privilege(s) specified will be revoked from all users and/or groups specified, regardless of who granted it originally.

In all cases, the value specified for the Forfeiter parameter can be any combination of the following:

REVOKE SQL statement examples

Now that we've seen the basic syntax for the various forms of the REVOKE SQL statement, let's take a look at some examples.

Example 1. A server has both a user and a group named Q045. Remove the ability to connect to the database named SAMPLE from the group Q045:

CONNECT TO SAMPLE REVOKE CONNECT ON DATABASE FROM GROUP q045

Example 2. Revoke all table privileges available (except CONTROL privilege) for the table DEPARTMENT from the user USER1 and the group PUBLIC:

REVOKE ALL PRIVILEGES ON TABLE department FROM user1, PUBLIC

Example 3. Take away user USER1's ability to use a user-defined function named CALC_BONUS:

REVOKE EXECUTE ON FUNCTION calc_bonus FROM USER user1

Example 4. Take away user USER1's ability to modify information stored in the ADDRESS and HOME_PHONE columns of the table EMP_INFO, regardless of who granted it:

REVOKE UPDATE(address, home_phone) ON TABLE emp_info FROM user1 BY ALL