Error: Permission Denied When Querying Unity Catalog Tables in Databricks

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.