POST_START

Auditing Production Catalog Privileges Through INFORMATION_SCHEMA

I recently needed to audit the privileges assigned to the production catalog in our Databricks environment. This was part of a routine security review to ensure that only authorized users had access to sensitive data. I decided to use the INFORMATION_SCHEMA to get a clear and structured view of the catalog privileges.

Checking Catalog Privileges with INFORMATION_SCHEMA

I started by running a query to fetch all the catalog privileges for the production catalog. This would give me a comprehensive list of who has what privileges.

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

The output showed that both the data_analysts and data_engineers groups had the USE CATALOG privilege on the production catalog. This means they can access and work with the catalog, but they don’t have the ability to modify its structure or permissions.

Extracting Grantees and Privilege Types

To make the information more readable, I ran a simplified query that only returned the grantee and privilege_type. This helped me quickly identify which roles had access and what kind of access they had.

SELECT grantee, privilege_type FROM system.information_schema.catalog_privileges WHERE catalog_name = 'production';

From this query, I confirmed that the same groups had the same privileges as before. This consistency was reassuring, as it indicated that the access controls were stable and well-defined.

Understanding the Implications

I used this information to update our internal access control documentation and to discuss the findings with the security team. The USE CATALOG privilege is essential for users to interact with the catalog, but it’s important to ensure that only the right roles have it.

By using the INFORMATION_SCHEMA, I was able to quickly and accurately audit the catalog privileges, which helped me make informed decisions about access control in our environment.