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.
- 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>;
- 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.- Click Data to open Data Explorer.
- Select the catalog.
- Click Permissions.
- Verify the permissions for the view owner.
- 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.- Click Data to open Data Explorer.
- Select the catalog.
- Click Permissions.
- Click Grant.
- Select the view owner from the Users and groups drop down list.
- Select Data Reader from the Privilege presets drop down list.
- Click Grant.
- The view owner should now be able to access the view.