Granting privileges with the GRANT statement

You can assign privileges to an ID or a role by issuing the GRANT statement.

About this task

Suppose that the Spiffy Computer Company wants to create a database to hold information that is usually posted on hallway bulletin boards. For example, the database might hold notices of upcoming holidays and bowling scores.

To create and maintain the tables and programs that are needed for this application, the Spiffy Computer Company develops the security plan shown in the following diagram.

Figure 1. Security plan for the Spiffy Computer Company
Begin figure description. Security plan for the Spiffy Computer Company. End figure description

The Spiffy Computer Company's system of privileges and authorities associates each role with an authorization ID. For example, the System Administrator role has the ADMIN authorization ID.

Begin figure description. User ID: ADMIN Authority and SYSADM Privileges. End figure description.
Begin general-use programming interface information.The system administrator uses the ADMIN authorization ID, which has the SYSADM authority, to create a storage group (SG1) and to issue the following statements:
  1. GRANT PACKADM ON COLLECTION BOWLS TO PKA01 WITH GRANT OPTION;

    This statement grants to PKA01 the CREATE IN privilege on the collection BOWLS and BIND, EXECUTE, and COPY privileges on all packages in the collection. Because ADMIN used the WITH GRANT OPTION clause, PKA01 can grant those privileges to others.

  2. GRANT CREATEDBA TO DBA01;

    This statement grants to DBA01 the privilege to create a database and to have DBADM authority over that database.

  3. GRANT USE OF STOGROUP SG1 TO DBA01 WITH GRANT OPTION;

    This statement allows DBA01 to use storage group SG1 and to grant that privilege to others.

  4. GRANT USE OF BUFFERPOOL BP0, BP1 TO DBA01 WITH GRANT OPTION;

    This statement allows DBA01 to use buffer pools BP0 and BP1 and to grant that privilege to others.

  5. GRANT CREATE IN COLLECTION DSN8CC91 TO ROLE ROLE1;

    This statement grants to ROLE1 the privilege to create new packages in collections DSN8CC91.

    End general-use programming interface information.
Begin figure description. Sample PACKADM authority. End figure description.

The package administrator, PKA01, controls the binding of packages into collections. PKA01 can use the CREATE IN privilege on the collection BOWLS and the BIND, EXECUTE, and COPY privileges on all packages in the collection. PKA01 also has the authority to grant these privileges to others.

Begin figure description. Sample DBADM authority. End figure description.

The database administrator, DBA01, using the CREATEDBA privilege, creates the database DB1. When DBA01 creates DB1, DBA01 automatically has DBADM authority over the database.

Begin figure description. Sample DBCTRL authority. End figure description.

The database administrator at Spiffy Computer Company wants help with running the COPY and RECOVER utilities. Therefore DBA01 grants DBCTRL authority over database DB1 to DBUTIL1 and DBUTIL2.

Begin general-use programming interface information.To grant DBCTRL authority, the database administrator issues the following statement: 
GRANT DBCTRL ON DATABASE DB1 TO DBUTIL1, DBUTIL2;
End general-use programming interface information.