Managing Catalog, Schema, and Table Permissions with SQL

Hello! Welcome back to our module on Data Governance with Unity Catalog.

In our last lesson, we explored the foundational structure of Unity Catalog: the three-level namespace of catalog.schema.table. We learned how this hierarchy organizes all data assets and how to query them using their fully qualified names.

Now that we understand how data is organized, our focus shifts to securing it. The learning outcome for this lesson is to grant and revoke permissions on catalogs, schemas, and tables using SQL. This is a fundamental skill for any data engineer, ensuring that only authorized users can access or modify data, a cornerstone of robust data governance.

1. The Core Components of Unity Catalog Permissions

Before we start writing SQL commands, it's essential to understand the three core components of the Unity Catalog security model.

Principals: These are the identities (users, groups, or service principals) to whom permissions are granted. A best practice, which we'll follow, is to grant permissions to groups rather than individual users. This makes managing access much more scalable.
Securable Objects: These are the assets in Unity Catalog that you can protect. In this lesson, we'll focus on the three main data objects you learned about previously: CATALOG, SCHEMA, and TABLE.
Privileges: These define what a principal can do to an object. Common privileges include SELECT (read data), MODIFY (change data), CREATE TABLE (create new tables), and USE CATALOG / USE SCHEMA.

How to Manage Permissions in Databricks Unity Catalog

The article 'How to Manage Permissions in Databricks Unity Catalog' provides a great summary of these core components. Please read the introductory section to solidify your understanding of these terms.

Read the section titled 'Understanding Permissions in Unity Catalog'. Focus on the definitions of Securable Objects, Principals, and Privilege Types.

2. The Permission Hierarchy and Inheritance

One of the most critical concepts in Unity Catalog is that privileges are hierarchical and inherited downwards. However, there's a key detail: to perform an action on a child object (like a table), a principal must also have USAGE privilege on all its parent containers (the schema and the catalog).

For example, to SELECT data from main.silver.customers, a user needs:

SELECT privilege on the customers table.
USE SCHEMA privilege on the silver schema.
USE CATALOG privilege on the main catalog.

If any of the parent USAGE privileges are missing, the user won't be able to access the table, even if they have SELECT on it directly. This prevents users from accessing objects inside containers they aren't even allowed to see.

Let's watch a video that explains this concept very clearly.

Advancing Spark - Understanding the Unity Catalog Permission Model

This clip from the 'Advancing Spark' channel provides an excellent conceptual breakdown of the permission hierarchy and the importance of the USAGE privilege.

Watch the video from 04:13 to 07:05 and from 08:48 to 11:10. Pay close attention to the explanation of how USAGE on parent objects (catalog, schema) is a prerequisite for privileges like SELECT on a child object (table).

3. Managing Privileges with SQL

While you can manage permissions through the Databricks UI (as shown briefly in the video), the auditable, repeatable, and automatable method is to use SQL. For the Data Engineer Associate exam, you are expected to know the SQL syntax.

The three key commands you will use are GRANT, REVOKE, and SHOW GRANTS.

The official Databricks documentation is the best source for the precise syntax.

Manage privileges in Unity Catalog | Databricks on AWS

This documentation page contains the definitive syntax and examples for all privilege management commands.

Skim through the sections 'Show, grant, and revoke privileges', 'Grant permissions on an object', and 'Revoke permissions on an object'. You don't need to memorize everything, but familiarize yourself with the basic structure of the GRANT and REVOKE commands.

Let's break down each command with practical examples.

The `GRANT` Command

The GRANT command gives a principal a specific privilege on a securable object.

Syntax:
GRANT <privilege> ON <securable_object_type> <securable_object_name> TO <principal>;

Example Scenario: Let's grant read-only access on the samples.nyctaxi.trips table to a group called data_analysts.

Based on the inheritance rule, we need to perform three grants:

Grant USE CATALOG on the catalog:

GRANT USE CATALOG ON CATALOG samples TO `data_analysts`;

Grant USE SCHEMA on the schema:

GRANT USE SCHEMA ON SCHEMA samples.nyctaxi TO `data_analysts`;

Grant SELECT on the table:

GRANT SELECT ON TABLE samples.nyctaxi.trips TO `data_analysts`;

Only after all three commands are executed will members of the data_analysts group be able to query the table.

Granting Permissions in Databricks Unity Catalog using SQL — This image shows how to grant `USAGE` and `SELECT` permissions on a catalog and schema to a user group within a Databricks SQL notebook.

The `REVOKE` Command

The REVOKE command removes a previously granted privilege. Its syntax is very similar to GRANT.

Syntax:
REVOKE <privilege> ON <securable_object_type> <securable_object_name> FROM <principal>;

Example Scenario: Let's revoke the SELECT access we just granted.

REVOKE SELECT ON TABLE samples.nyctaxi.trips FROM `data_analysts`;

After running this, members of data_analysts can no longer read from the trips table, although they still have USAGE rights on the parent catalog and schema. Revoking USE CATALOG on samples would effectively block their access to all objects within that catalog.

The `SHOW GRANTS` Command

How do you check which permissions are currently active? The SHOW GRANTS command is used for auditing and troubleshooting.

Syntax:

To see grants on an object: SHOW GRANTS ON <securable_object_type> <securable_object_name>;
To see grants for a principal: SHOW GRANTS FOR <principal>;

Example: To see who has permissions on the trips table:

SHOW GRANTS ON TABLE samples.nyctaxi.trips;

This command is invaluable when a user reports an access issue. It's often the first step in diagnosing a missing USAGE or SELECT privilege.

4. Hands-On Practice: Managing Permissions

Now, let's put this theory into practice. In a Databricks SQL notebook, you will grant yourself permissions to a table, verify them, and then revoke them. We will use your own user principal for this exercise. In a real-world scenario, you would typically grant permissions to a group.

Instructions:

Open a new SQL notebook in your Databricks workspace.
Ensure a clean state: Run the following REVOKE commands to make sure you don't have pre-existing privileges on the samples catalog. Don't worry if it says a privilege can't be revoked; this just means it wasn't there to begin with. Replace <your_email@domain.com> with the email address you use for Databricks.
```
-- Use backticks for the principal as it contains special characters (@, .)
REVOKE USE CATALOG ON CATALOG samples FROM `<your_email@domain.com>`;
REVOKE USE SCHEMA ON SCHEMA samples.nyctaxi FROM `<your_email@domain.com>`;
REVOKE SELECT ON TABLE samples.nyctaxi.trips FROM `<your_email@domain.com>`;
```
Try to query: Now, try to query the table. This query should fail with a permissions error, proving our slate is clean.
```
SELECT * FROM samples.nyctaxi.trips LIMIT 10;
```

Grant permissions: Execute the following GRANT statements one by one.

GRANT USE CATALOG ON CATALOG samples TO `<your_email@domain.com>`;
GRANT USE SCHEMA ON SCHEMA samples.nyctaxi TO `<your_email@domain.com>`;
GRANT SELECT ON TABLE samples.nyctaxi.trips TO `<your_email@domain.com>`;

Verify with SHOW GRANTS: Check that your grants were applied correctly.
```
SHOW GRANTS ON TABLE samples.nyctaxi.trips;
```
You should see your user listed with the SELECT privilege.
Re-run the query: Execute the SELECT query from step 3 again. This time, it should succeed.

Revoke a privilege: Now, remove your SELECT privilege.

REVOKE SELECT ON TABLE samples.nyctaxi.trips FROM `<your_email@domain.com>`;

Final check: Run the SELECT query one last time. It should fail again, confirming that the REVOKE command worked.

This exercise walks you through the complete lifecycle of managing a permission, demonstrating the direct impact of the GRANT and REVOKE commands.

Conclusion

In this lesson, we moved from understanding the structure of Unity Catalog to actively managing its security. You've learned how to control access to the fundamental data objects—catalogs, schemas, and tables—using standard SQL commands.

Key Takeaways

Permissions in Unity Catalog are managed using principals, securable objects, and privileges.
The permission model is hierarchical. Accessing an object requires USAGE privileges on its parent catalog and schema.
The GRANT command is used to assign privileges.
The REVOKE command is used to remove privileges.
The SHOW GRANTS command is used to audit and verify permissions on an object or for a principal.
Using groups for permissions is a scalable best practice over assigning them to individual users.

Next Steps

We now know how to grant or deny access to an entire table. But what if you only want to expose certain columns or rows to a user? In our next lesson, we will explore a more granular level of security by learning how to create views to implement column-level and row-level security.

Recommended resources