PERMISSION_DENIED error when accessing Unity Catalog tables

You must have explicit permission to access a catalog or schema when trying to read a Unity Catalog table.

Written by Rajeev kannan Thangaiah

Last published at: November 30th, 2023

Problem

You want to list the details of a Unity Catalog table, like location, size etc. You are trying to use the describe detail command, but it returns a permission denied error message.

describe detail poc.schema1.testtable

Error Message:

Error in SQL statement: ExecutionException: java.util.concurrent.ExecutionException: com.databricks.sql.managedcatalog.acl.UnauthorizedAccessException: PERMISSION_DENIED: User does not have USE CATALOG on Catalog 'poc'.
Error in SQL statement: ExecutionException: java.util.concurrent.ExecutionException: com.databricks.sql.managedcatalog.acl.UnauthorizedAccessException: PERMISSION_DENIED: User does not have USE SCHEMA on Schema 'poc.schema1'.

Cause

When using Unity Catalog, a given user doesn't have privileges to a table by default. The privileges are inherited downward in Unity Catalog. 

For example, if a user is granted access to a catalog, then the user has permission to access all objects within that catalog. Similarly if a user is granted access to a schema, all objects within the schema share the same access permissions.

Solution

The owner of the catalog or schema must provide explicit access to the user or users who need to access the table.

%sql

GRANT USAGE ON CATALOG `poc` to `<email-address>`;
GRANT USAGE ON SCHEMA `schema1` to `<email-address>`;


For more information please review the Manage privileges (AWS | Azure | GCP) documentation.