Problem

When querying views in complex, multi-layer view setups in a Unity Catalog metastore, you encounter an insufficient table permissions error. 

Your request failed with status FAILED: [BAD_REQUEST] [INSUFFICIENT_PERMISSIONS] Insufficient privileges: Table 'test_table1' does not have sufficient privilege to execute because the owner of one of the underlying resources failed an authorization check. SQLSTATE: 42501

Cause

You’re working in an environment with multiple ownership layers between views and underlying tables that hold the actual data. 

There are missing permissions in the permission hierarchy for dependent views or tables, where the condition of “view table owner must have select access to its upstream tables/views” is not met. 

Context

In scenarios where there is only one layer, meaning the view table was created directly from a table(s) that holds data, only the view owner’s permission against the source table(s) will need to be checked. 

In more complex scenarios where there are multiple layers, meaning the view table is created from view table(s), those view tables might also be created from more view tables, and there are multiple layers of reference before reaching the source table.

Solution

Trace back the permission tree for the view table. To do so, you need Unity Catalog metastore admin privileges. If you do not have admin privileges, contact your metastore admin to assist. 

1. Find the owner of the view table giving you the error message. The owner’s permission on the upstream table is required for the view table to work.
2. Use the lineage graph of the view table to identify its upstream table(s).
3. Check if the owner of the view table has at least SELECT access to each of the upstream table(s). Note: the owner of the view table might not be found in the list of users with granted permission if the upstream table is owned by the same user.
4. Repeat the steps above for each view table if the upstream table is a view table, until you reach the data table.

Example illustrating the traceback procedure

The following diagram illustrates the solution steps, and is accompanied by an example description set of steps to make the traceback instruction steps more concrete. 

Start with the problematic view table (View Table4)

The issue happens when trying to query View Table4. The table owner is userA.

The upstreams of View Table4 are View Table2 and View Table3.

Examine View Table2

Check if userA has SELECT access on View Table2.

1. UserA is also the owner of View Table2, so the permission is good.
2. Then check the upstream of View Table2, which is Data Table1.
3. UserA is also the owner of Data Table1, so the permission is good.
4. You have reached the base table where the actual data is. The route is good.

Examine View Table3

Next, check if userA has SELECT access on the other view table initially identified, View Table3.

1. If userA does not have SELECT permission on View Table3, grant permission here.
2. If userA does have SELECT permission on View Table3, check if the owner of View Table3 (userD) has SELECT permission to the upstream tables, which are View Table1 and Data Table4.

Examine View Table1

If userD does not have SELECT permission on View Table1, grant permission here.

1. Check if the owner of View Table1 (userB) has SELECT permission on the upstream tables, which are Data Table2 and Data Table3.
2. UserB is also the owner of Data Table 2. The permission is good.
3. UserB does not have SELECT permission to Data Table3. Grant permission here.

Examine Data Table4

1. UserD is also the owner of Data Table4. The permission is good.
2. You have reached the base table where the actual data is. This route is good.

Once you or your metastore admin have checked all the routes from the problematic view table to the data table, and ensured that each view table has the necessary SELECT access on its upstream table(s), the “INSUFFICIENT_PERMISSIONS” error should resolve.