POST_START

Reviewing Production Schema Creation Privileges

I recently needed to review the privileges associated with the production.finance schema in our Databricks environment. This was part of a routine security audit to ensure that only authorized users had access to sensitive financial data. I started by checking the current grants assigned to the schema using the SHOW GRANTS ON SCHEMA command.

SHOW GRANTS ON SCHEMA production.finance;

I saw a representative result like this:

principal | actionType | objectType
data_analysts | USE SCHEMA | SCHEMA
data_engineers | CREATE TABLE | SCHEMA

From this output, I learned that the data_analysts group has the USE SCHEMA privilege, which allows them to access the schema and its contents. Meanwhile, the data_engineers group has the CREATE TABLE privilege, which means they can create new tables within the production.finance schema. This was useful to confirm that the right people had the appropriate permissions for their roles.

Next, I wanted to cross-verify these findings using the system.information_schema.schema_privileges table. This table provides a more detailed and structured view of schema-level privileges across the catalog.

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

I saw a representative result like this:

catalog_name | schema_name | schema_owner
production | finance | finance_engineers

This output confirmed that the production.finance schema is owned by the finance_engineers group. While the SHOW GRANTS command showed who had privileges, the schema_privileges table provided the ownership information, which is useful for understanding the administrative responsibility for the schema.

By running these two queries, I was able to get a clear picture of both the privileges assigned to the schema and the ownership structure. This helped me ensure that the schema was properly secured and that the right teams had the right access based on their roles.