Cannot grant users access to a view on a virtual table in Data Virtualization

Last updated: Nov 26, 2024

A user who is named userB has the Data Virtualization Engineer role and creates a view based on virtual tables. However, userB cannot grant users access to this view.

Symptoms

A user who is named userA creates a virtual table named VIRTUAL_A. A user who is named userB creates a view that is named VIEW_B from table VIRTUAL_A. Permission to access table VIRTUAL_A is granted to all Data Virtualization users by using the option All data virtualization users on the Manage access page.

The user who is named userB tries to grant access to userC on view VIEW_B, but gets an error that is similar to the following example.

Code 500: SQLExecute: {42501} [IBM][CLI Driver][DB2/LINUXX8664]
SQL0551N The statement failed because the authorization ID does not have the required authorization or privilege to perform the operation.
Authorization ID: "USERB". Operation: "GRANT". Object: "USERB.VIEW_B". SQLSTATE=42501

Causes

Even if userB is the creator of view VIEW_B, userB does not have the required permissions on table VIRTUAL_A to grant access to users on a view dependent on VIRTUAL_A.

Resolving the problem

To solve this issue, userA must enter the following statement in the SQL editor to grant userB access to table VIRTUAL_A.

GRANT SELECT ON TABLE "schema".VIRTUAL_A TO "userB" WITH GRANT OPTION

This workaround is only necessary when a user (userB in the example) has been granted access permissions to a virtual table (VIRTUAL_A in the example) by using the option All data virtualization users.