Generate GRANT statements for a table or tables using RC/Migrator

Products

• RC/Migrator for DB2 for z/OS

Issue/Introduction

Generate GRANT statements for a table or tables using RC/Migrator for Db2 for z/OS (RCM).

Resolution

RC/Query can display the User Authorities that a table has been given.

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: dsn1 ----------authid1 -          LINE 1 OF 4    >
CMD          TABLE/USER      GTE TYP  CTR/GRNTOR     DATE      TIME   AUT ALT  DEL INDX INS SEL UPD UPDC REF REFC TRIG  UNLD
________ table                        authid1
________       authid2       AUTHID     authid1   2018/06/22 02:00:14      G    G   G    G   G   G        G        G     G

To generate the GRANT statements an RC/Migrator Migration strategy can be used.

In order to generate only GRANT statements, the Analysis Options are used.

"G (generate only .AUTH and GRANT statements for the selected objects). Enter G in this field and Y in the Security field."

Set BND/DAT/STA/SQL/GRNT/RI ==>  to "G" for Grants
Set SECURITY          ==>  to "Y" to signify that security statements are to be generated.

20.0                    RC/M Migrate Strategy Analysis Options
COMMAND ===>


 Enter SAVE to save settings. END to return. "?" in field for field level help
      Exclusive Options          Utility Options            Output Options
NO .AUTHS               ==> Y  STATS (Y,N,S,A)==> Y      ANALYSIS HEADER ==> Y
BND/DAT/STA/SQL/GRNT/RI ==> G  CHECK DATA     ==> Y      IMPACT ANALYSIS ==> Y
(B,D,R,S,G,I/N)                COPY  (Y,P,N)  ==> Y      RPT IN DDLFILE  ==> Y
COMMIT ASAP (SQL ONLY)  ==> N  (B)IND/(R)EBND ==> B
      Control Options          REBUILD INDEX  ==> Y
VIEW (IMP/EXP)    ==> N             Model Options            IDCAMS Options
AUX IMPLODE       ==> N        MOD ID      ==> authid1   VSAM DEFINES    ==> N
MQT IMPLODE       ==> N        MOD CREATOR ==> authid1   VSAM DELETES    ==> N
RI (foreign keys) ==> N        UPDATE MOD  ==> N
SECURITY          ==> Y
DROP OBJECTS      ==> Y           Data Unload Options     Dataset Del Options
DEFAULT SQLID     ==> authid1  ALL ROWS    ==> Y         UNLOAD          ==> Y
ALIAS SQLID       ==>          NUM ROWS    ==>           TEMPORARY       ==> Y
LOB OBJECTS       ==> Y        DATA STATS  ==> Y
TABLE OBID        ==> N        TRUNCATE    ==> N
SEQ IMPLODE       ==>   (T,R,A,blank,S)
RTN IMP/EXP       ==> A (N,T,V,A,*,blank)
RTN INTERACTION   ==> Y (Y,N,A,O,E,M,S)
MQT REFRESH       ==> Y (Y,N,B,U)
SET TERMINATOR    ==> ; (blank defaults to ';')

When the Analysis is generated for the table selected in the migration strategy the GRANT statements reflecting the authorities set in the catalog are generated.

Additional Information

Exclusive Options

BND/DAT/STAT/SQL/GRNT/RI
During analysis of a migration strategy, you can specify:

G (generate only .AUTH and GRANT statements for the selected objects). Enter G in this field and Y in the Security field.

RC/Query Table Reports

UA (User Authorization)
The Table User Authorization report displays all the authorized users for each table selected. This report can be used to determine the effect on the user community of dropping a table and to verify the table security.
The UPDC column that is displayed on this report denotes whether the user holds specific column update privileges. If you want to view specific columns for which the user holds the update privilege, use the User Table Authorization report.