Unable to access VDS even with the granted Privileges (v21+) – Dremio Support

On accessing a VDS the user gets the below error even with the SELECT privilege on that VDS:

Error:

Error while expanding view "<view name>" - User does not have access to VDS [VDS.Context.Path]

The above is a generic access error. Before we proceed, it's important to note that in Dremio, each object MUST have an owner, and may have only one owner. Ownership is automatically granted to the user who initially creates the object.

Ownership for PDS and VDS was introduced in v21. Prior to v21 VDS's had implicit ownership:

In Dremio 21.0.0 and subsequent versions, view owners can be changed only using SQL commands; editing the SQL in a view does not change the view’s ownership.
In Dremio 20.x and previous versions, the view owner is the user who most recently edited the view’s SQL. Any user with access to the underlying table can edit the view’s SQL to become the view’s owner.
Ref[1]: Ownership

POSSIBLE CAUSES:

1. "Owner" of the VDS doesn't have select privilege on the referenced Datasets:

We need to review the Privilege Delegation and should verify the required Privileges are granted for the Owner of the concerned VDS on all the underlying/referenced datasets. Please refer below reference examples for more details on Privilege Delegation:
Ref[2]: https://docs.dremio.com/software/security/rbac/rbac-structure/#privilege-delegation

Starting from v21, when a VDS gets queried, it is expanded to all datasets referenced by the VDS. During expansion, Dremio checks if the VDS owner (not the querying user!) has the SELECT privilege on those referenced datasets. Prior to v21, VDS expansion used the querying user who invoked the query to resolve privileges to the underlying datasets.

If the Owner of the VDS has missing SELECT Privilege on the underlying Dataset, then the query fails for all the users even if they have SELECT Privilege on that VDS.

2. Unowned VDS (missing owner or deleted owner) :

This is mostly observed post upgrade to v21 or newer versions. As shared in the above section, the ownership for PDS and VDS (introduced in v21) and prior to v21 VDS had implicit ownership - the owner was the last user to update the SQL query for the view.

If a dataset has no owner, the owner_id value is $deleted$ (for datasets created in Dremio 21.0.0 and subsequent versions) or $unowned$ (for datasets created before Dremio 21.0.0).

If the owner of any referenced VDSs is not found in the Dremio database or external provider (LDAP, Azure AD), then the query will fail. This scenario is documented below:
Ref[3]: https://docs.dremio.com/software/security/rbac/rbac-structure/#dataset-ownership

SUGGESTIONS:

First, we can identify the problem VDSs ownership with the below query for filtering with VDSs with missing ownership:

SELECT * FROM sys."views" v left join
sys."users" u on v.owner_id =u.user_id
WHERE v.owner_id in ('$deleted$','$unowned$', 1 ,NULL)
ORDER BY CREATED

For datasets with no owner, use the admin account with GRANT OWNERSHIP sql command to grant ownership to a user or role that has access to the table (or the underlying table, for a view).

Also, if there are many VDSs with missing ownership, we can also consider creating Script with something like the following example:

select concat('GRANT OWNERSHIP ON VDS "' || replace(replace(replace(path, ', ' , '"."'), '[' , ''), ']' , '') || '" to USER "<username>" ' ) from sys.views where owner_id in ('$deleted$','$unowned$', 1 ,NULL)

* Please Note: In order to avoid this issue, it is recommended to use a service account as the Owner (permanent user/role), thereby ensuring it wouldn't be removed.

Related articles