topic Re: Cannot read from view if no access to underlying table in Data Engineering

Cannot read from view if no access to underlying table

tomph — Tue, 23 Jul 2024 09:46:47 GMT

Hi,

I created a view my_view in a schema project_schema in Unity catalog catalog_dev that is a select * from a table my_table in my common_schema in the same catalog.

I gave a service principal full grants on the project_schema. It is a owner of the schema and has full privileges on it. But when it tries to select from catalog_dev.project_schema.my_view (from a job cluster with Unity Catalog enabled) it gets the following exception:

Insufficient privileges: User does not have SELECT on Table 'catalog_dev.common_schema.my_table'.

It was my understanding that users do not need read access on all underlying tables in the view definition. What could be the issue?

Thank you,

Tommaso

Re: Cannot read from view if no access to underlying table

daniel_sahal — Tue, 23 Jul 2024 10:56:34 GMT

@tomph
That's how views work in the most of the engines.
You need to grant the permissions to the underlying tables.

Re: Cannot read from view if no access to underlying table

tomph — Thu, 25 Jul 2024 09:36:13 GMT

FYI this is not true. In serverless mode or with Shared clusters you can give access to the view without giving access to the underlying table. The problem arises with Single User clusters.

Does anyone know a workaround? Shared clusters have a lot of limitations

Re: Cannot read from view if no access to underlying table

daniel_sahal — Fri, 26 Jul 2024 05:42:25 GMT

@tomph

Yup, you're correct. I've check the documentation once more and it clearly statest that:

For single-user compute resources on Databricks Runtime 15.4 and above, shared compute resources, and SQL warehouses, you need SELECT on the view itself, USE CATALOG on its parent catalog, and USE SCHEMA on its parent schema.

For single-user compute resources on Databricks Runtime 15.3 and below, you must also have SELECT on all tables and views that the view references, in addition to USE CATALOG on their parent catalogs and USE SCHEMA on their parent schemas.

This was introduced in DBR 15.4 LTS (that was released this week). Switching to that DBR should workaround your issue.
https://docs.databricks.com/en/release-notes/runtime/15.4lts.html#single-user-compute-supports-fine-grained-access-control-materialized-views-and-streaming-tables-public-preview