Granting privileges on a table

This procedure describes how to grant table privileges from the Table List panel. The privileges that you can grant depend on your authorization level.

Related topic

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

  1. Create a list of tables that includes the table or tables for which you want to grant privileges. For information, see Generating-lists-in-Catalog-Manager.

  2. In the Cmd (C) column beside the name of the table for which you want to grant privileges, type GRANT (GR) as shown in the following figure. You can specify any number of tables.To grant privileges for every listed table, on the Command line, type GRANT ALL.

     DNK -R --------------------------  TABLE LIST  ------------------- ROW 1 OF 34
     Command ===>                                                  Scroll ===> PAGE
                                                                                 01
     CMD will show commands for this list.  Type command and press ENTER            
     Lists: ACCTB AL BMCHOBJ BMCHSTEP CA CD CK CL CO CP C2 DB DP DS DT FK IC IM IS  
     LIKE QCT.QCTT01%                                                               
     C   Table Name                     Database Tblspace ColsPK Type  Rows  Pages  
     ----v----1----v----2----v----3----v----4----v----5----v----6----v----7----v----
     GRANTCT.QCTT01_DBAYS01             QCTDBAY  QCTS01AU  17  2 T       -1     -1  
         QCT.QCTT01_DBAYS02             QCTDBAY  QCTS02AU  17  2 T       -1     -1  
         QCT.QCTT01_DEALTS1             QCTDEALT QCTS1ALT  10  1 T        0      0  
         QCT.QCTT01_DEALTS2             QCTDEALT QCTS2ALT   6  1 T        0      0  
         QCT...LTS3_QM002426359         QCTDEALT QCTS3ALT   5  1 T        0      0  
         QCT...LTS4_QM002426359         QCTDEALT QCTS4ALT   7  1 T        0      0  
         QCT.QCTT01_DESTAS01            QCTDESTA QCTSSTA1  24  0 T        0      0  
         QCT.QCTT01_DESTAS02            QCTDESTA QCTSSTA2  24  1 T        0      0  
         QCT.QCTT01_DETHAS01            QCTDETHA QCTSTHA1  24  0 H        0      0  
         QCT.QCTT01_DETHAS02            QCTDETHA QCTSTHA2  24  1 H        0      0  
         QCT.QCTT01_DE01S01             QCTDE01  QCTS0101  19  2 T        0      0  
         QCT.QCTT01_DE01S02             QCTDE01  QCTS0201  17  2 T        0      0  
         QCT.QCTT01_DE01S03             QCTDE01  QCTS0301  17  1 T        0      0  
         QCT.QCTT01_DE01S04             QCTDE01  QCTS0401  17  2 T        0      0  
         QCT.QCTT01_DE01S06             QCTDE01  QCTS0601  19  0 T        0      0  
         QCT.QCTT01_DE04S01             QCTDE04  QCTS0104  85  3 T        0      0  
         QCT.QCTT01_DE04S01L01P         QCTDE04  QCT0101P   3  0 X        0     -1  
         QCT.QCTT01_DE04S02             QCTDE04  QCTS0204   7  4 T        0      0  
         QCT.QCTT01_DE04S03             QCTDE04  QCTS0304  13  0 T        0      0  
         QCT.QCTT01_DE04S03L11B         QCTDE04  QCT0311B   3  0 X        0     -1  
         QCT.QCTT01_DE04S03L11C         QCTDE04  QCT0311C   3  0 X        0     -1  
         QCT.QCTT01_DE04S04             QCTDE04  QCTS0404  20  1 T        0      0  
         QCT.QCTT01_DE04S18             QCTDE04  QCTS1804   7  4 T        0      0  
         QCT.QCTT01_DE05S01             QCTDE05  QCTS0105   7  0 H        0      0  
         QCT.QCTT01_DE05S02             QCTDE05  QCTS0205   7  0 T        0      0  
         QCT.QCTT01_DE05S03             QCTDE05  QCTS0305  20  1 T        0      0  
         QCT..._DE05S04_ARCHTB1         QCTDE05  QCTS0405  16  1 R        0      0  
         QCT...E05S05_ARCHENTB1         QCTDE05  QCTS0505  16  1 T        0      0  
         QCT.QCTT01_DE07S04             QCTDE07  QCTS0407  17  1 T        0      0  
         QCT.QCTT01_HS01S01             QCTHS01  QCTS0101  20  2 T      397     59  
         QCT.QCTT01_HS01S02             QCTHS01  QCTS0201  20  0 T      384     56  
         QCT.QCTT01_HS01S03             QCTHS01  QCTS0301  19  1 T      548      7  
         QCT.QCTT01_HS01S04             QCTHS01  QCTS0401  18  2 T      215     24  
         QCT.QCTT01_HS01S09             QCTHS01  QCTT01RH  17  1 T      362     64  
     ******************************  BOTTOM OF DATA  *******************************

  3. Press Enter.The Grant Table Privileges panel is displayed:

     

     DNK -R                    Grant Table Privileges                    1 to 1 of 1
     Command ===>                                                  Scroll ===> PAGE
                                                                                   
     Generate SQL . N                                                               
                                                                                   
     Grant privileges to . . .                                                      
                                                                                   
      AUTHIDs . . .           ,            ,            ,            ,            ,
      . . . . . . .           ,            ,            ,            ,              
                                                                                   
      ROLEs . . . .           ,            ,            ,            ,              
                                                                                   
     With Grant . . N                                                               
                                                                                   
     Table Privileges:                                                              
     All. . . . . . . . . N   Index. . . . . . . . N   Select . . . . . . . N       
     Alter. . . . . . . . N   Insert . . . . . . . N   Trigger. . . . . . . N       
     Delete . . . . . . . N   References . . . . . N   Unload . . . . . . . N       
     Update . . . . . . . N                                                         
                                                                                   
     Cmd  Creator.TBname                                                            
     -------------------------------------------------------------------------------
          QCT.QCTT01_DBAYS01                                                        
     ******************************* Bottom of data ********************************

    Important

    Authorizations that are granted on multiple objects by using a wildcard character are not automatically available to objects that are created subsequently, even if those objects match the wildcard specification. To issue the same set of authorizations for a new object, use the 

    Catalog Manager

     COPYAUTHS command.
    For more detail, see Granting-privileges-by-issuing-the-COPYAUTHS-command.

  4. Specify the authorization IDs and the roles for which you want the privilege granted:
    • To identify the grantees, in the AUTHIDs field, enter a maximum of 10 authorization IDs.
    • To identify the roles, in the ROLEs field, enter a maximum of 5 roles.
  5. In the With Grant field, type Y to enable the grantees to grant their table privileges to other users.
  6. Specify which table privileges to grant, as follows:
    • In the All field, enter Y to grant all of the table privileges listed. If you used the GRANT ALL command on the object list panel, the default value in the All field is Y.

    • In the Privileges fields, type Y beside the appropriate privileges to grant individual table privileges.

      Important

      Press HELP to display a brief description of the privileges.

  7. After setting all of the panel values, in the Generate SQL field, type Y and press Enter.The Confirm SQL panel is displayed:

     DNK -R                          Confirm SQL                         1 to 6 of 6
     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. . . . .  20220228_072236                                  
     Save in PDS. . . . . . . . .  N         Y/N Save in PDS                        
      PDS(member) . . . . . . . .                                                   
                                                                                   
     Execute SQL. . . . . . . . .  N         Remote Db2 . NONE      Enter ? for list
     -------------------------------------  SQL  -----------------------------------
        GRANT ALL PRIVILEGES                                                        
           ON TABLE                                                                 
              QCT.QCTT01_DBAYS01                                                    
           TO MVSSXS2,                                                              
              ROLE ROLE1                                                            
         WITH GRANT OPTION ;                                                        
     ******************************* Bottom of data ********************************
  8. 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.

      Important

      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.

    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*