In this section, you generate and execute GRANT and REVOKE SQL statements using row commands (RV and GR). The instructions are similar if you use primary commands (REVOKE and GRANT). The primary commands generate statements for all user IDs displayed in the window.

Statements can also be generated for a list of objects. If you execute GRANT for a list of objects, File-AID for Db2 Object Administration opens a window in which you can edit a mask. The mask allows you to assign a single grantee and levels of authorization for all objects.

If you use an external product to maintain Db2 authorizations, you can bypass sending GRANTs and REVOKEs to Db2 by editing the DBAXSQL2 skeleton. Editing instructions are included in this member. This feature is available for batch SQL execution only.

What’s in this section

This section provides step-by-step instructions for generating grant and revoke statements for user IDs. While you perform the steps, you learn how to display current privileges for any type of object. At the end of this section is a list of row commands that display privileges and a section on generating SQL for granting and revoking privileges for objects.

Step-by-step instructions

These instructions grant and revoke privileges for a table. Generated statements are placed in the SQL work buffer. The sequential steps include:

• Displaying Current Privileges 
• Granting Table Privileges 
• Revoking Table Privileges

These steps use the table you created in Generating SQL Statements. You can substitute any table for which you can grant and revoke privileges.

Displaying current privileges

This section displays the current authorizations for the table you created in Generating SQL Statements. You do not need to display authorizations to generate GRANT statements, as noted in the next section.

1. Access File-AID for Db2 Object Administration. The Main Menu must be displayed.

2. Execute fast path command MENU.TB.DB; Modify the Where clause to read:

   TB.DBNAME LIKE 'DSN8%' AND TB.NAME LIKE 'EMP%' and enter <GO> to execute.

   If you’ve completed Generating SQL Statements, you see a table named EMPTEST. You can use this table or any other. Your screen must look similar to the following figure.

3. Execute the TA (table authorizations) row command next to EMPTEST.

   The TA command displays authorizations for a table. File-AID for Db2 Object Administration opens a window that lists the authorized users (Table Authorization Display (Before Grant). This window is maximized). If you completed the step-by-step instructions in Generating SQL Statements, this window has your user ID as the grantor for the table.

   Table Display. This window is maximized.

   

   Table Authorization Display (Before Grant). This window is maximized.

   
   

Granting table privileges

This section grants privileges for the table EMPTEST to the PUBLIC ID using the GR (grant) row command. GR is executed on the table authorization you displayed in the last section. GR is also valid, however, for any Db2 catalog object. You could, for example, execute GR on the table EMPTEST shown in Table Display. This window is maximized and achieve the same results.

In this section, the GRANT statements are generated based on a grantee. Your screen must look similar to Table Authorization Display (Before Grant). This window is maximized before you continue.

1. Execute the GR (grant) row command next to the table EMPTEST.

   A window opens where you can edit the authorizations to use in generating the GRANT statements (see the following figure).

   The current privileges shown in this window are all G (grant with grant option). These values were acquired from object or authorization on which GR was executed. If you make no changes in this window other than the grantee, File-AID for Db2 Object Administration generates the SQL to grant the same privileges.

   Privileges for the Table. This window is maximized and scrolled to show the privilege columns.

   

2. Optional: Generate a vertical display to view all data for your user ID.

   1. Execute the V (vertical display) row command next to your user ID.

      The window that opens should look similar to the following figure.

      Vertical Display of Edit Authorizations Window

      

      Each column is a type of authorization associated with a table. Each field for the column is the level of authorization. Available levels are:

      G	Grant with grant option
      Y	Grant without grant option
      (blank)	No grant generated.

   2. Remove the vertical display window.
3. Make these changes to the table authorizations:

   1. Type a user ID in the GRANTEE field.

      You can use PUBLIC as the grantee.

   2. Make the following changes to the authorization levels:

       1. Change INDEX, INSERT, SELECT, UPDATE, REFERENCE, and TRIGGERAUTH to Y.

       2. Enter blanks for UPDATECOLS (update columns), ALTER, and DELETE.

      On most terminals, you must scroll to see the right-most columns.

4. Press <Go> to generate the SQL.

   File-AID for Db2 Object Administration opens the SQL work buffer window with the generated SQL (see the following figure).

5. Press <Go> to execute the SQL.

   A message window opens that indicates all statements were executed successfully (SQL Execution Messages).

   Generated SQL to Grant Privileges

   

   SQL Execution Messages

   

6. Return to the table display.

   Remove windows using <Cancel> until the table display is showing. Since your SQL statements were executed, you must either cancel the work buffer or delete the statements with row commands.

   Your screen should look like Table Display. This window is maximized before you continue.

7. Execute the TA row command next to EMPTEST again.

   When the authorizations display window opens (see the following figure), PUBLIC is now listed as authorized to perform inserts, deletes, selects, and updates without the grant option.

   An easy way to view all column data is to use the V row command to generate a vertical display.

   Table Authorizations Display (After Grant)

   
   

Revoking table privileges

In this section, you revoke the privileges you granted in the previous section by using the RV (revoke privileges) row command. RV is also valid for Db2 objects. You do not need to display authorizations for an object to use it. Your screen must look similar to Table Authorizations Display (After Grant).

1. Execute the RV (revoke privileges) row command next to PUBLIC.

   The table privileges window opens (see the following figure). File-AID for Db2 Object Administration will automatically generate SQL to revoke all privileges listed if you make no changes. If you want to keep a privilege, enter a blank in the appropriate field.

   Window to Revoke Table Privileges

   

2. Press <Go> to generate the SQL.
   The SQL work buffer window opens (see the following figure).

   Important

   If you did not cancel the SQL window in Granting Table Privileges, this window still shows the GRANT SQL. If this is the case, delete the unnecessary rows before you continue.

   SQL to Revoke Privileges

   

3. Press <Go> to execute the SQL.

   The SQL execution messages window opens (see the following figure). If it does not indicate that all commands were executed successfully, remove the message window, edit the SQL to correct the mistake, and press <Go> again.

4. Press <Cancel> to remove the SQL work buffer and delete its contents.
5. Press <Exit> to return to the Main Menu.
   Revoked Privileges Messages
   
   

More about displaying authorizations

In this section, you learned to display authorizations for a table. Authorizations for any object or any user ID can be displayed using the following row commands:

Generating GRANT or REVOKE SQL for objects

You can generate authorization SQL for one or more objects. First display the objects to include, then execute the GRANT command.

File-AID for Db2 Object Administration opens the Edit Authorization Mask window. This mask allows you to assign a single grantee and levels of authorization for all objects listed in the window. After editing the mask, press <Go> to continue.

File-AID for Db2 Object Administration opens a window in which you can override the grantee or authorizations levels for any object. After editing the authorization, press <Go> to generate the SQL.

 File-AID for Db2 Object Administration generates GRANT statements for every object, which you can then edit and execute.