Cannot read from view if no access to underlying table

[tomph]

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

[daniel_sahal]

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

[tomph]

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

[daniel_sahal]

@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-...