Question

A data analyst has been granted SELECT on the table `sales_prod.reporting.daily_orders` in Unity Catalog. However, when the analyst runs `SELECT * FROM sales_prod.reporting.daily_orders`, they receive a permission error and cannot access the data. The table owner confirms the SELECT grant was applied correctly to the table. What is the most likely cause and required fix?

Accepted Answer

The analyst also needs USE CATALOG on `sales_prod` and USE SCHEMA on `sales_prod.reporting`, because SELECT on a table alone does not grant traversal of the parent catalog and schema.

Answer

The analyst must be granted MODIFY on the table in addition to SELECT before any read query will succeed.

Answer

SELECT grants only work if the analyst is also the owner of the table, so ownership must be transferred to the analyst.

Answer

The table must be recreated as an external table, since SELECT grants cannot be enforced on managed tables.