Unable to access Unity Catalog views

You cannot access a view if you do not have sufficient permissions on the underlying table.

Written by John.Lourdu

Last published at: May 10th, 2023

Problem

A user is trying to access a view in Unity Catalog when it fails with a Table '<view-name>' does not have sufficient privilege to execute error message.

Error in SQL statement: AnalysisException: Table '<view-name>' does not have sufficient privilege to execute.

Cause

The owner of the view does not have sufficient privileges on the source table.

Solution

You must ensure the owner of the view has enough privileges on the underlying table from which the view was created.

  1. Verify the owner of the view with DESCRIBE TABLE EXTENDED. Run DESCRIBE TABLE EXTENDED on the view and look at the results in the Owner column. 
    %sql
    
    DESCRIBE TABLE EXTENDED <catalog>.<schema>.<view-name>;
  2. Verify the view owner has USE CATALOG and USE SCHEMA permissions on the catalog and the SELECT privilege on the table.
    %sql
    
    SHOW GRANTS `<view-owner-user-name>` on <catalog>.<schema>.<view-name>;
    You can also verify the permissions using Data Explorer.

    1. Click Data to open Data Explorer.
    2. Select the catalog.
    3. Click Permissions.
    4. Verify the permissions for the view owner.

  3. If the view owner does not have the correct permissions, grant USE CATALOG and USE SCHEMA on the catalog and SELECT on the table.
    %sql
    
    GRANT USE CATALOG,USE SCHEMA, SELECT ON CATALOG <catalog-name> TO `<view-owner-user-name>`;
    You can also grant permissions using Data Explorer.

    1. Click Data to open Data Explorer.
    2. Select the catalog.
    3. Click Permissions.
    4. Click Grant.
    5. Select the view owner from the Users and groups drop down list.
    6. Select Data Reader from the Privilege presets drop down list.
    7. Click Grant.


  4. The view owner should now be able to access the view.