MIGRATE_GROUP_AUTHZ stored procedure in Data Virtualization

Data Virtualization authorizations are assigned to role and group names. If a group is renamed, you must migrate the group-level authorizations by running the MIGRATE_GROUP_AUTHZ stored procedure in Data Virtualization.

This procedure migrates the following authorizations:
  • Database-level authorizations (DBAUTH)
  • Roles that are assigned to groups (ROLEAUTH)
  • Schema-level authorizations (SCHEMAAUTH)
  • Table-, view-, and nickname-level authorizations (TABAUTH)
  • Routines (ROUTINEAUTH)
  • Data source-level authorizations
After you migrate the authorizations, revoke the Data Virtualization service role from the old group name and assign it to the new group.
Note: Column-level privileges and row and column access control (RCAC) rules are not migrated.
Important: The user who runs this procedure must have the GRANT and REVOKE privileges for the authorization types that this procedure migrates. Typically, this procedure is run by a user with the Admin role, or by the instance owner (db2inst1) if you are running the procedure by using the Db2 command-line interface.

All GRANT and REVOKE statements are run as a single transaction. If any of the steps fail, the entire transaction rolls back.

If the Db2 authentication cache is enabled in your environment, you might also want to flush the cache after you run this procedure. For more information, see Authentication and group cache.

Input parameters

OLD_GRP_NAME
The type of this required parameter is VARCHAR(128). Specifies the group name in all upper-case characters to migrate the authorizations from.
NEW_GRP_NAME
The type of this required parameter is VARCHAR(128). Specifies the group name in all upper-case characters to migrate the authorizations to.

Example

Use the following command to run the MIGRATE_GROUP_AUTHZ stored procedure:

CALL DVSYS.MIGRATE_GROUP_AUTHZ('OLD', 'NEW')

Successful output might look like the following text:

Return Status = 0

If you are running this procedure by using the Db2 command-line interface, you can enable detailed server output to see which GRANT and REVOKE statements the procedure is performing. To enable the detailed output, run the following command before you run the MIGRATE_GROUP_AUTHZ stored procedure:

db2 set serveroutput on

With the detailed server output, you see the specific GRANT and REVOKE statements, as shown in the following example:

[db2inst1@c-db2u-dv-db2u-0 - Db2U ~]$ db2 set serveroutput on
DB20000I  The SET SERVEROUTPUT command completed successfully.

[db2inst1@c-db2u-dv-db2u-0 - Db2U ~]$ db2 "CALL DVSYS.MIGRATE_GROUP_AUTHZ('OLD', 'NEW')"

  Return Status = 0

REVOKE ROLE "DV_ENGINEER" FROM GROUP "OLD"
GRANT ROLE "DV_ENGINEER" TO GROUP "NEW"
REVOKE SELECT ON TABLE "DV_ENG2"."BANK_CLIENTS_5ROWS" FROM GROUP "OLD"
GRANT SELECT ON TABLE "DV_ENG2"."BANK_CLIENTS_5ROWS" TO GROUP "NEW" WITH GRANT OPTION
Updating authorizations on CID "BIGSQ100020" as per {
                            "GRANT": {
                                "G": { "NEW": [ "DATAACCESS", "REF" ] }
                            },
                            "REVOKE": {
                                "G": { "OLD": [ "DATAACCESS", "REF" ] }
                            }
                        }