POST_START

Auditing Role-Based Access Control Assignments with Unity Catalog INFORMATION_SCHEMA

I recently had the task of auditing role-based access control (RBAC) assignments within our Unity Catalog environment. The goal was to ensure that all access to sensitive data in the ‘production’ catalog was properly scoped and assigned to roles, following our organization’s security policies. Since Unity Catalog provides a comprehensive set of metadata through the INFORMATION_SCHEMA, I decided to leverage it to audit the access controls without needing to manually inspect each role or permission.

Starting with Catalog-Level Privileges

I began by querying the CATALOG_PRIVILEGES view to get an overview of which roles have access to the ‘production’ catalog. This is the top-level view that gives me a broad understanding of who has access at the catalog level.

SELECT * FROM system.information_schema.catalog_privileges WHERE catalog_name = 'production';

I ran this query and noticed that several roles, including data_engineers and analysts, had been granted access to the ‘production’ catalog. However, I wanted to dig deeper into what specific permissions they had and whether those permissions were aligned with our access control policies.

Drilling Down into Schema-Level Privileges

Next, I focused on the Sales schema within the ‘production’ catalog, as this is where a significant portion of our business data resides. I used the SCHEMA_PRIVILEGES view to check which roles have access to this schema.

SELECT * FROM system.information_schema.schema_privileges WHERE catalog_name = 'production' AND schema_name = 'sales';

The results showed that the analysts role had SELECT privileges, which is appropriate for their role. However, I noticed that the data_engineers role had both SELECT and MODIFY privileges, which raised a flag. I wanted to confirm whether this was intentional or a misconfiguration.

Checking Table-Level Privileges for Precision

To ensure that the access was properly scoped, I then checked the TABLE_PRIVILEGES view to see what specific tables within the ‘sales’ schema were being accessed by which roles.

SELECT * FROM system.information
_schema.table_privileges WHERE table_catalog = 'production' AND table_schema = 'sales';

This query revealed that the data_engineers role had access to a subset of tables, including customer_transactions and inventory_logs. While this was in line with their responsibilities, it also highlighted that they had access to more sensitive data than necessary. I noted this as a potential area for further refinement.

Putting It All Together

By combining the results from the three views—CATALOG_PRIVILEGES, SCHEMA_PRIVILEGES, and TABLE_PRIVILEGES—I was able to create a clear picture of the access control landscape for the ‘production’ catalog. This allowed me to identify where roles had been granted access and whether that access was appropriate and aligned with our security policies.

I used this information to draft a report for the security team, highlighting areas where access could be tightened and where roles might need to be re-evaluated. The process was efficient, and the INFORMATION_SCHEMA proved to be an invaluable tool for auditing and managing access in Unity Catalog.