Generating GRANT statements for authorizations on a DB2 table using RC/Migrator

Products

• RC/Migrator for DB2 for z/OS

Issue/Introduction

Requirement to generate GRANT statements for all the authorizations that are on a Db2 table using RC/Migrator for Db2 for z/OS (RCM).

Resolution

Generating GRANT statements can be accomplished by creating a Migration strategy on the Db2 tables and selecting the following Analysis options:

Exclusive Options:
BND/DAT/STA/SQL/GRNT/RI ==> G

Control Options:
SECURITY ==> Y

Generates only .AUTH and GRANT statements for objects selected in the analysis, enter G in this field and Y in the Security field.

 

This is the display from RC/Query report T/UA showing the existing grants on the table.
There are grants to two other authids giving them SELECT.

RQTUA  20.0   ------ RC/Q Table User Authorization Inq ------ yyyy/mm/dd hh:mm
COMMAND ===>                                                  SCROLL ===> CSR

 DB2 Object ===> T                       Option  ===> UA   Where => N
 Table Name ===> table                > Creator ===> authid1                >
  Qualifier ===> *                     > Grantor ===> *                      >
Loc: LOCAL ---------- SSID: ssid ----------authid1 -          LINE 1 OF 4  <
CMD          TABLE/USER      DEL INDX INS SEL UPD UPDC REF REFC TRIG  UNLD
________ table
________       authid1        G   G    G   G   G        G        G     G
________       authid2                     Y
________       authid3                     Y

This migration strategy is using authid1.table as the primary object.
Sample Analysis output snippet. 

.SYSTEM SQLDDL
.CONNECT ssid

.AUTH authid1

    SET CURRENT SQLID = 'authid1';

    GRANT SELECT
                    ON TABLE authid1.table
                      TO      authid2;

.SYNC 5         'GRANT TABLE authid1.table'

.AUTH authid1

    SET CURRENT SQLID = 'authid1';

    GRANT SELECT
                    ON TABLE authid1.table
                      TO      authid3;

.SYNC 10        'GRANT TABLE authid1.table'

    SET CURRENT SQLID = USER;

Additional Information

Analysis Options