POST_START

Investigating Effective Access Across Catalog Schema and Table Grants

I recently needed to understand how access permissions propagate across different levels in Unity Catalog, specifically looking at how grants on a catalog affect access to schemas and tables within it. I started by examining the production catalog, which contains several schemas and tables that are critical for our analytics workflows.

Checking Catalog-Level Grants

To begin, I ran the SHOW GRANTS ON CATALOG production; command to see what permissions are defined at the catalog level. This helps establish the baseline access controls that apply to all objects within the catalog.

SHOW GRANTS ON CATALOG production;

I saw a representative result like this:

principal | actionType | objectType
data_analysts | SELECT | TABLE
data_engineers | MODIFY | TABLE

From this, I learned that the data_analysts group has SELECT access to all tables in the catalog, while data_engineers have MODIFY access. This means they can not only read but also update or insert data, which is important for data engineering workflows.

Examining Schema-Level Grants

Next, I wanted to check if there were any additional grants defined at the schema level. I chose the finance schema within the production catalog to investigate further.

SHOW GRANTS ON SCHEMA production.finance;

I saw a representative result like this:

principal | actionType | objectType
data_analysts | SELECT | TABLE
data_engineers | MODIFY | TABLE

Interestingly, the grants at the schema level were the same as those at the catalog level. This means that the permissions defined at the catalog level are inherited by all schemas within it, unless explicitly overridden at the schema level. This inheritance simplifies permission management across large data environments.

Verifying Table-Level Grants

To ensure that the grants were consistent across all objects, I decided to check the payments table within the finance schema. This step was crucial to confirm that the access controls applied down to the table level as expected.

SHOW GRANTS ON TABLE production.finance.payments;

I saw a representative result like this:

principal | actionType | objectType
data_analysts | SELECT | TABLE
data_engineers | MODIFY | TABLE

This result confirmed that the permissions applied consistently across the catalog, schema, and table levels. Since the data_analysts have SELECT access, they can query the payments table for reporting purposes, while data_engineers can modify the data as needed.

Conclusion

By investigating the grants at each level—catalog, schema, and table—I gained a clearer understanding of how access control is structured in Unity Catalog. This knowledge helps ensure that data is accessed appropriately while maintaining security and compliance across our organization.