POST_START

Controlling Who Can Create Tables in Production Unity Catalog Schemas

I’m working on a project where I need to ensure that only specific teams have the ability to create tables in our production Unity Catalog schemas. This is important to maintain data integrity and prevent accidental or unauthorized changes. Today, I’m going to walk through how I set up access controls for the `production` catalog and its schemas using Databricks Unity Catalog.

Understanding the Scope of Access

I started by recalling that Unity Catalog allows fine-grained access control at the catalog, schema, and table levels. My goal is to ensure that only the `data_engineers` group can create tables in the `production.engineering` schema. But first, I need to make sure that the `data_engineers` group has access to the `production` catalog itself.

I ran the following command to grant the `data_engineers` group the ability to use the `production` catalog:

GRANT USE CATALOG ON CATALOG production TO `data_engineers`;

This step ensures that the group can interact with the `production` catalog. Without this, they wouldn’t be able to access any schemas or tables within it. I noticed that this command doesn’t grant them the ability to create tables yet, just the ability to use the catalog.

Granting Access to the Schema

Next, I needed to allow the `data_engineers` group to access the `production.engineering` schema. I ran the following command:

GRANT USE SCHEMA ON SCHEMA production.engineering TO `data_engineers`;

This step gives the group access to the specific schema. It’s important because without being able to use the schema, they wouldn’t be able to create tables or perform any operations on it. I verified that this command doesn’t grant the ability to create tables yet, only the ability to access the schema.

Allowing Table Creation

Now, I wanted to ensure that the `data_engineers` group could actually create tables in the `production.engineering` schema. I ran the following command:

GRANT CREATE TABLE ON SCHEMA production.engineering TO `data_engineers`;

This is the critical step. By granting the `CREATE TABLE` privilege, the group now has the ability to create new tables in the specified schema. I made sure to use the exact syntax from the curriculum to avoid any mistakes. I also noticed that this privilege is scoped to the schema, so it doesn’t affect other schemas in the `production` catalog.

Verifying the Grants

To confirm that the permissions were applied correctly, I ran the following command:

SHOW GRANTS ON SCHEMA production.engineering;

This command lists all the grants assigned to the `production.engineering` schema. I checked the output to ensure that the `data_engineers` group had the `USE SCHEMA` and `CREATE TABLE` privileges. Seeing these grants confirmed that the access controls were successfully applied.

Throughout this process, I learned that Unity Catalog provides a powerful way to manage access at various levels. By carefully granting the right privileges, I can ensure that only authorized users and groups have the ability to create tables in production schemas, which helps maintain data security and governance.