POST_START

Controlling Table Read Access with SELECT Privileges

I started my day by reviewing the access control requirements for the training catalog. The team needed to ensure that analysts could read specific tables, but not access other parts of the catalog. I knew that Databricks Unity Catalog provides fine-grained access control, and I wanted to set up read access for the training catalog and its contents in a secure and controlled way.

I first decided to grant the analysts group access to the training catalog. This would allow them to see the schemas and tables within it, but not modify anything. I ran the following command:

GRANT USE CATALOG ON CATALOG training TO `analysts`;

I noticed that this step was necessary to ensure the analysts could navigate the catalog structure without having write access. It was a good starting point for setting up a controlled access environment.

Granting Schema Access

Next, I needed to give the analysts access to the sales schema within the training catalog. This would allow them to see the tables inside that schema but not access other schemas. I executed:

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

I verified that this step was essential to limit the scope of access. By granting schema-level access, I ensured that analysts could only work within the sales schema and not explore other parts of the catalog.

Granting Table Read Access

Now, I wanted to give the analysts the ability to read the customers table in the sales schema. I ran the command:

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

I noticed that this was the most granular level of access needed for the analysts’ work. They needed to query the data but not modify it, and this command achieved exactly that.

To confirm that the analysts had the correct permissions, I checked the grants assigned to the training.sales.customers table:

SHOW GRANTS ON TABLE training.sales.customers;

I verified that the output listed the SELECT privilege granted to the analysts group. This gave me confidence that the access was correctly configured.

Revoking Access When Needed

Later, I realized that the analysts should no longer have access to the customers table. I decided to revoke the SELECT privilege to ensure data security. I executed:

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

I checked the grants again to confirm that the privilege had been removed. This step was important to maintain compliance and prevent unauthorized data access.

By following these steps, I ensured that the analysts group had the right level of access to the training catalog and its contents. The process reinforced the importance of granular access control in a shared data environment.