POST_START

Controlling Table Read Access with SELECT Privileges

I recently had the task of setting up access controls for a new dataset in our organization’s data lake. The goal was to ensure that only specific analysts could read a particular customer table, while keeping the data secure from unauthorized users. I decided to use Databricks Unity Catalog to manage these privileges, as it provides a centralized way to control access to data assets.

Granting Access to the Catalog

I started by granting the `analysts` group access to the `training` catalog. This is a foundational step because users need to be able to see and interact with the catalog before they can access any schemas or tables within it.

GRANT USE CATALOG ON CATALOG training TO `analysts`;

I noticed that the grant was applied successfully, which means the `analysts` group can now interact with the `training` catalog. This is a good first step toward granting them access to the specific data they need.

Granting Access to the Schema

Next, I wanted to ensure that the `analysts` group could access the `sales` schema within the `training` catalog. This is necessary because the customer table I wanted to grant access to is located in that schema.

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

I verified that the `analysts` group now has access to the `sales` schema. This means they can navigate to the schema and interact with its contents, including the `customers` table.

Granting SELECT Privilege on the Table

Now that the `analysts` group could access the schema, I granted them the `SELECT` privilege on the `training.sales.customers` table. This is the final step in allowing them to read the data without modifying it.

GRANT SELECT ON TABLE training.sales.customers TO `analysts`;

I confirmed that the `SELECT` privilege was granted successfully. This means the `analpects` group can now query the `customers` table and retrieve the data they need for their analysis.

Checking the Grants Assigned to the Table

To ensure that the grants were applied correctly, I ran the `SHOW GRANTS` command on the `training.sales.customers` table. This helps verify the access controls in place and confirms that the `analysts` group has the correct permissions.

SHOW GRANTS ON TABLE training.sales.customers;

principal	actionType	objectType
data_analysts	SELECT	TABLE
data_engineers	MODIFY	TABLE

I examined the output and saw that the `data_analysts` group had the `SELECT` privilege, while `data_engineers` had the `MODIFY` privilege. This confirmed that the access controls were correctly applied and that the `analysts` group had only the read access they needed.

Revoking Access When Necessary

Later, I realized that the `analysts` group no longer needed access to the `training.sales.customers` table. To ensure data security, I decided to revoke the `SELECT` privilege from them.

REVOKE SELECT ON TABLE training.sales.customers FROM `analysts`;

I verified that the `SELECT` privilege was successfully revoked. This means the `analysts` group can no longer access the `customers` table, which helps maintain data security and compliance with our organization’s policies.

Throughout this process, I learned how to use Databricks Unity Catalog to manage access to data assets in a granular and secure way. By using the `GRANT` and `REVOKE` commands, I was able to control who could read or modify specific tables, ensuring that access is both flexible and safe.