Error: Permission Denied When Querying Unity Catalog Tables in Databricks

Posted by

Introduction

If you encounter a “Permission denied” error when querying Unity Catalog tables, it typically means that:

  • Your Databricks user or role does not have the required access privileges.
  • Your cluster or SQL Warehouse is not correctly configured for Unity Catalog.
  • Your Unity Catalog metastore permissions are not correctly assigned.

๐Ÿšจ Common errors when querying Unity Catalog tables:

  • PERMISSION_DENIED: User does not have permission to access table.
  • User is not authorized to perform the operation on catalog/schema/table.
  • Resource not found: Cannot access catalog/schema/table.

This guide will walk you through troubleshooting steps and solutions to fix permission errors in Unity Catalog.


1. Check If You Have the Required Unity Catalog Permissions

Symptoms:

  • Cannot query tables in Unity Catalog.
  • Error: “PERMISSION_DENIED: User does not have permission to access catalog.”

Causes:

  • The user is missing required privileges to access the catalog, schema, or table.
  • The Unity Catalog administrator has not granted access.

Fix:

โœ… Check your current user privileges:

SHOW GRANTS ON CATALOG <catalog_name>;

โœ… If missing, grant permissions using the GRANT command:

GRANT USE CATALOG ON CATALOG <catalog_name> TO `user@example.com`;
GRANT USE SCHEMA ON SCHEMA <catalog_name>.<schema_name> TO `user@example.com`;
GRANT SELECT ON TABLE <catalog_name>.<schema_name>.<table_name> TO `user@example.com`;

โœ… If managing permissions at a group level, use:

GRANT SELECT ON TABLE <catalog_name>.<schema_name>.<table_name> TO `my_group`;

โœ… Check all privileges assigned to the user:

SHOW GRANTS TO `user@example.com`;

๐Ÿšจ Note:

  • Users need USE CATALOG permission to access tables inside a catalog.
  • They must also have USE SCHEMA permission to query a schema within the catalog.

2. Verify If Your Cluster or SQL Warehouse Supports Unity Catalog

Symptoms:

  • Can access Unity Catalog in SQL Editor, but not from a Databricks notebook.
  • Clusters do not list Unity Catalog tables.
  • Error: “USE CATALOG is not supported on this cluster.”

Causes:

  • The Databricks cluster is not Unity Catalog-enabled.
  • The SQL Warehouse is missing Unity Catalog configuration.

Fix:

โœ… Ensure your cluster supports Unity Catalog:

  1. Go to Databricks UI โ†’ Clusters.
  2. Edit your cluster โ†’ Advanced options โ†’ Enable Unity Catalog.
  3. If using a legacy cluster (Hive Metastore), create a new Unity Catalog-enabled cluster.

โœ… Ensure SQL Warehouses are Unity Catalog-enabled:

  1. Go to Databricks UI โ†’ SQL Warehouses.
  2. Edit your SQL Warehouse and enable Unity Catalog support.

โœ… Restart the cluster or SQL Warehouse after enabling Unity Catalog.


3. Check If the Unity Catalog Metastore Is Assigned to the Workspace

Symptoms:

  • Error: “No metastore configured for this workspace.”
  • Tables in Unity Catalog do not appear in Databricks UI.

Causes:

  • Unity Catalog requires a metastore, but it is not assigned to your workspace.

Fix:

โœ… Check if a metastore is assigned:

SHOW METASTORES;

โœ… If no metastore is assigned, assign one (Admin Required):

databricks unity-catalog metastores assign --metastore-id <metastore-id> --workspace-id <workspace-id>

โœ… Verify that the metastore assignment was successful:

SHOW METASTORES;

4. Ensure That IAM and Cloud Permissions Are Correct (AWS & Azure)

Symptoms:

  • Error: “Permission denied when accessing Unity Catalog tables.”
  • Cannot access cloud storage-backed tables.

Causes:

  • AWS IAM roles do not allow access to S3, Glue, or Unity Catalog.
  • Azure service principals do not have the right Key Vault or storage permissions.

Fix:

โœ… Check IAM roles for AWS Unity Catalog access:

{
  "Effect": "Allow",
  "Action": ["glue:Get*", "glue:Create*", "s3:GetObject", "s3:PutObject"],
  "Resource": "*"
}

โœ… Assign IAM role permissions:

aws iam put-role-policy --role-name <your-role-name> --policy-name UnityCatalogAccess --policy-document file://policy.json

โœ… For Azure, ensure Databricks has access to Storage & Key Vault:

az role assignment create --assignee <databricks-service-principal> --role "Storage Blob Data Contributor" --scope /subscriptions/<sub-id>/resourceGroups/<rg>/providers/Microsoft.Storage/storageAccounts/<storage-name>

โœ… Verify Key Vault access policies:

az keyvault set-policy --name <keyvault-name> --spn <databricks-service-principal> --secret-permissions get list

5. Verify That Unity Catalog Table Ownership Is Correct

Symptoms:

  • Error: “User does not have ownership privileges on table.”
  • Cannot perform INSERT, UPDATE, or DELETE operations.

Causes:

  • The user does not have OWNERSHIP privileges on the table.

Fix:

โœ… Check table ownership:

SHOW GRANTS ON TABLE <catalog_name>.<schema_name>.<table_name>;

โœ… If ownership is missing, assign it:

GRANT OWNERSHIP ON TABLE <catalog_name>.<schema_name>.<table_name> TO `user@example.com`;

โœ… If using groups, assign ownership at the group level:

GRANT OWNERSHIP ON SCHEMA <catalog_name>.<schema_name> TO `my_group`;

6. Ensure Users Belong to the Correct Groups

Symptoms:

  • Users cannot access certain catalogs, schemas, or tables.
  • Unity Catalog permissions appear correct, but queries fail.

Causes:

  • Unity Catalog permissions are assigned to groups, but the user is not part of the group.

Fix:

โœ… Check group memberships for the user:

databricks groups list-members --group-name my_group

โœ… If missing, add the user to the correct group:

databricks groups add-member --group-name my_group --user-name user@example.com

7. Troubleshooting Step-by-Step

Step 1: Check User Permissions

SHOW GRANTS ON CATALOG <catalog_name>;
SHOW GRANTS TO `user@example.com`;
  • If missing, grant USE CATALOG and SELECT permissions.

Step 2: Verify That Unity Catalog Is Enabled for Clusters & Warehouses

  • Restart clusters and enable Unity Catalog support.

Step 3: Check Metastore Configuration

SHOW METASTORES;
  • If missing, assign a Unity Catalog metastore.

Step 4: Validate IAM and Cloud Permissions

  • Ensure AWS IAM roles or Azure AD permissions allow storage access.

Step 5: Check Table Ownership

SHOW GRANTS ON TABLE <catalog_name>.<schema_name>.<table_name>;
  • If needed, assign ownership permissions.

Best Practices for Managing Unity Catalog Permissions

โœ… Always Assign Permissions at the Catalog or Schema Level

GRANT USE CATALOG ON CATALOG <catalog_name> TO `my_group`;
GRANT SELECT ON TABLE <catalog_name>.<schema_name>.<table_name> TO `my_group`;

โœ… Use Groups Instead of Individual Users for Permissions

  • Assign roles to Databricks groups instead of individual users.

โœ… Ensure IAM Roles Are Properly Configured

  • AWS IAM and Azure Key Vault must allow Databricks access.

Conclusion

If you see “Permission Denied” when querying Unity Catalog tables:
โœ… Check user permissions (GRANT statements).
โœ… Verify cluster and SQL Warehouse settings.
โœ… Ensure metastore is assigned to the workspace.
โœ… Check IAM roles and cloud permissions.
โœ… Ensure users belong to the correct groups.

guest
0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x