Limited support BMC provides limited support for this version of the product. As a result, BMC no longer accepts comments in this space. If you encounter problems with the product version or the space, contact BMC Support.BMC recommends upgrading to the latest version of the product. To see documentation for that version, see BMC AMI Catalog Manager for Db2 13.1.

Granting privileges on a hierarchy of Db2 objects

The following procedure describes how to create the SQL to grant the privileges held by users on specified objects and their dependents.

Related topic

Granting-privileges-by-using-the-GRANT-commands

  1. Access the Db2 subsystem that contains the Db2 object hierarchy with authorizations.
  2. Create a list of databases, table spaces, or tables. For information, see Generating-lists-in-Catalog-Manager.

  3. Enter the HGRANT command by using one of the following options:

    • In the Cmd column of the list, enter HGRANT beside the object that you want to use as a model. Then, press Enter.

    • On the Command line, type the command using the following syntax and press Enter: HGRANT objectType objectName BATCH

      Replace the variables as follows:

      • objectType represents one of the following values DB, TS, TB, or VW.
      • objectName represents the fully qualified name of an existing database, table space, table, or view (for example, ACGRNT1.N1).
      • Add BATCH if you want to save the command for inclusion in a batch job.

    The SQL Progress Indicator panel is displayed. The indicator shows the execution of SQL that makes a list of dependents for the object specified.

    When SQL creation is complete, the Confirm SQL panel is displayed in the following figure. This panel contains the generated GRANT statements for the source object and its dependents.

     AUBDYN                         Confirm SQL                        1 to 37 of 50
     Command ===>                                                  Scroll ===> PAGE
                                                                                   
     Current SQLID. . . . . . . .  MVSSXS2                                          
     Edit Options . . . . . . . .  N         Y/N Modify SQL processing options      
     Edit SQL . . . . . . . . . .  N         Y/N Edit SQL before executing          
     Save in SQL table. . . . . .  N         A/Y/R/N A/Y-Append, R-Replace          
      Name of saved data. . . . .  20220222_094815                                  
     Save in PDS. . . . . . . . .  N         Y/N Save in PDS                        
      PDS(member) . . . . . . . .  ACT.V12.DATABASE(TEST)                           
                                                                                   
     Execute SQL. . . . . . . . .  N         Remote Db2 .  NONE     Enter ? for list
     -------------------------------------  SQL  -----------------------------------
      GRANT DBADM ON DATABASE ACTGRNT1 TO RDAPXB2 WITH GRANT OPTION;                
      GRANT CREATETAB ON DATABASE ACTGRNT1 TO CATVW;                                
      GRANT ALTER, DELETE, INDEX, INSERT, SELECT, UPDATE, TRIGGER,                  
           REFERENCES ON TABLE ACT.INVENTORY TO PXB1;                               
      GRANT ALTER, DELETE, INDEX, INSERT, SELECT, UPDATE, TRIGGER,                  
           REFERENCES ON TABLE ACT.INVENTORY TO PXB2;                               
      GRANT ALTER, DELETE, INDEX, INSERT, SELECT, UPDATE, TRIGGER,                  
           REFERENCES ON TABLE ACT.INVENTORY TO PXB3;                               
      GRANT ALTER, DELETE, INDEX, INSERT, SELECT, UPDATE, TRIGGER,                  
           REFERENCES ON TABLE ACT.INVENTORY TO PXB4;                               
      GRANT ALTER, DELETE, INDEX, INSERT, SELECT, UPDATE, TRIGGER,                  
           REFERENCES ON TABLE ACT.INVENTORY TO PXB5;                               
      GRANT ALTER, DELETE, INDEX, INSERT, SELECT, UPDATE, TRIGGER,                  
           REFERENCES ON TABLE ACT.INVENTORY TO PXB6;                               
      GRANT ALTER, DELETE, INDEX, INSERT, SELECT, UPDATE, TRIGGER,                  
           REFERENCES ON TABLE ACT.INVENTORY TO PXB7;                               
      GRANT ALTER, DELETE, INDEX, INSERT, SELECT, UPDATE, TRIGGER,                  
           REFERENCES ON TABLE ACT.INVENTORY TO PXB8;                               
      GRANT ALTER, DELETE, INDEX, INSERT, SELECT, UPDATE, TRIGGER,                  
           REFERENCES ON TABLE ACT.INVENTORY TO PXB9;                               
      GRANT ALTER, DELETE, INDEX, INSERT, SELECT, UPDATE, TRIGGER,                  
           REFERENCES ON TABLE ACT.INVENTORY TO PXB10;                              
      GRANT ALTER, DELETE, INDEX, INSERT, SELECT, UPDATE, TRIGGER,                  
           REFERENCES ON TABLE ACT.INVENTORY TO PXB11;                              
      GRANT DELETE, INSERT, SELECT, UPDATE ON TABLE ACT.INVENTORY TO ATTICUS;       
      GRANT DELETE, INSERT, SELECT, UPDATE ON TABLE JAS3.INVALID_PGS TO             
           ATTICUS;                                                                 
      GRANT SELECT ON TABLE JAS3.SDFSDFSDF TO RDATLF6;                              
      GRANT SELECT ON TABLE JAS3.SDFSDFSDF TO RDATLF7;                              
      GRANT DELETE, INSERT, SELECT, UPDATE ON TABLE JAS3.SDFSDFSDF TO               
           ATTICUS;                                                                 
      GRANT SELECT ON TABLE RDAKJT.ACTGRNT TO RDATLF6;                              
      GRANT SELECT ON TABLE RDAKJT.ACTGRNT TO RDATLF7;                              
      GRANT ALTER, DELETE, INDEX, INSERT, SELECT, UPDATE, TRIGGER,                  
           REFERENCES ON TABLE RDAKJT.ACTGRNT TO RDAPXB3;                           
      GRANT ALTER, DELETE, INDEX, INSERT, SELECT, UPDATE, TRIGGER,                  
           REFERENCES ON TABLE RDAKJT.ACTGRNT TO RDAPXB4;                           
  4. On the Confirm SQL panel, you can edit and save the SQL and then execute it:

    1. (Optional) From the Command line, issue the SET sqlid command to change the value of the current SQLID.

      Note

      The ID shown in the Current SQLID field must have the proper authority to perform the specified SQL GRANT statement. If you hold a primary- or secondary-authorization ID that has the proper authority, you can change the Current SQLID to that authorization ID and complete the CREATE. To change the Current SQLID, use the SET command.

    2. (Optional) In the Edit options field, type Y to modify the default values for the options on the Confirm SQL panel and then press Enter.The Options panel is displayed. In the Edit SQL and Confirm options field, type Y to display the options for the Confirm SQL panel. Press END to return to the Confirm SQL panel.
    3. (Optional) In the Edit SQL field, type Y to invoke an ISPF edit session to edit the SQL and then press Enter.
    4. Press END to save the SQL and return to the Confirm SQL panel.

    5. (Optional) In the Save in SQL table field, type A, Y, R, or N to specify whether to save the SQL in the Catalog Manager SQL_Table.

      Purpose

      Action

      Append the SQL to the SQL in the SQL_Table

      Enter A

      Save the SQL in the SQL_Table

      Enter Y

      Replace the SQL in the SQL_Table

      Enter R

      Discard the SQL

      Enter N

    6. (Optional) In the Name of saved SQL field, type a name for the SQL.
    7. (Optional) In the Save in PDS field, type Y to save the SQL in a member of a partitioned data set (PDS).The saved SQL uses the ID displayed in the Current SQLID field as the object qualifier. If the SQL is not saved, the ID in the Current SQLID is used only to identify Db2 authority.
    8. (Optional) In the PDS(member) field, type the name of the PDS and member.To import the PDS member to another subsystem as an entry in the SQL_Table, see Importing-the-SQL-in-another-subsystem .

    9. (BMC.DB2.SPE2210)

       (Optional) In the Remote Db2 field, perform one of the following steps to execute the SQL displayed on a different Db2 subsystem:

      • Enter a Db2 subsystem ID.
      • To select a Db2 subsystem from a list, type ? and then press Enter.
    10. (Optional) In the Execute SQL field, type Y to execute the SQL displayed on the Confirm SQL panel and then press Enter.The SQL Progress Indicator panel is displayed. The panel automatically refreshes to display the status of the SQL that is being executed.

 

Tip: For faster searching, add an asterisk to the end of your partial query. Example: cert*