POST_START

Auditing Production Schema Privileges Through INFORMATION_SCHEMA

I recently needed to audit the privileges assigned to the sales schema in the production catalog. This is a common task when ensuring that only authorized users have access to sensitive or critical data. I decided to use the INFORMATION_SCHEMA to get a clear and structured view of who has what privileges on the schema.

Exploring Schema Privileges

I started by running a query to retrieve all the privilege records for the sales schema in the production catalog. This gives me a complete list of grants, including grantee, privilege type, and catalog and schema names.

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

I noticed that the output includes all the columns from the schema_privileges table, which gives me a comprehensive view. This is useful for understanding the full context of each privilege.

Focusing on Grantees and Privilege Types

To make the output more readable and focused, I ran a second query that only included the grantee and privilege_type columns. This helps me quickly identify who has access and what kind of access they have.

SELECT grantee, privilege_type FROM system.information_schema.schema_privileges WHERE catalog_name = 'production' AND schema_name = 'sales';

I verified that both queries return the same data, but the second one is more concise for auditing purposes. This helps me quickly understand which roles or users have access to the sales schema in the production catalog.

Conclusion

Using the INFORMATION_SCHEMA to audit schema privileges is a powerful and straightforward method. It allows me to quickly understand who has access to which schemas and what privileges they hold. This is essential for maintaining security and compliance in a production environment.