POST_START

Granting and Revoking Schema-Level Access

Setting Up the Scenario

I started my day by reviewing the access control requirements for our data team. We needed to ensure that the analysts had access to the right schemas in the training catalog, but not to everything. The goal was to grant them access to the training catalog and specifically to the training.sales schema, and then later revoke that access if needed. This was a schema-level access control scenario, and I was going to use Unity Catalog to manage it.

Granting Use of the Catalog

I first needed to grant the analysts group access to the training catalog. I remembered that in Unity Catalog, the USE CATALOG privilege allows users to access a catalog, which is a top-level container for schemas. So I ran the following command:

GRANT USE CATALOG ON CATALOG training TO `analysts`;

I noticed that the syntax required the catalog name, the privilege type, and the principal to which the privilege is granted. This step was essential because without access to the catalog, the analysts wouldn’t be able to see or access any schemas within it.

Granting Access to a Specific Schema

Next, I wanted to grant the analysts group access to the training.sales schema. Since the analysts already had access to the training catalog, I needed to give them the USE SCHEMA privilege on the sales schema within that catalog. I executed the following command:

GRANT USE SCHEMA ON SCHEMA training.sales TO `analysts`;

This allowed the analysts to interact with the sales schema, such as querying tables or creating new objects. I realized that schema-level access is more granular than catalog-level access, which is useful for controlling access to specific data sets.

Verifying the Grants

To make sure the grants were applied correctly, I decided to check what privileges the analysts group had on the training.sales schema. I ran the following command:

SHOW GRANTS ON SCHEMA training.sales;

The output confirmed that the analysts group had been granted the USE SCHEMA privilege. This was a good sign, and it gave me confidence that the access controls were being applied as intended.

Revoking Access to the Schema

Later in the day, I received a request to revoke the analysts group’s access to the training.sales schema. This might be necessary if the team no longer needed access or if there was a security concern. I executed the following command to remove the privilege:

REVOKE USE SCHEMA ON SCHEMA training.sales FROM `analysts`;

I verified that the privilege was successfully revoked by running the SHOW GRANTS command again. This time, there was no entry for the analysts group on the training.sales schema, which confirmed that the access had been removed.

Conclusion

Through this process, I learned that Unity Catalog provides a powerful way to manage access at the catalog and schema levels. By using the GRANT and REVOKE commands, I could precisely control who had access to which data, ensuring that our team followed the principle of least privilege. This approach is essential for maintaining data security and compliance in a collaborative environment.