POST_START

Controlling Table Data Changes with MODIFY Privileges

I recently had the task of ensuring that only specific data engineers could modify the customer data in our training database. The goal was to prevent accidental or unauthorized changes to the training.sales.customers table while still allowing the team to perform necessary updates. I decided to use the MODIFY privilege in Unity Catalog to achieve this.

Granting MODIFY Privileges

I started by identifying the role that should have the ability to modify the customer data. The role was data_engineers, and I needed to grant them the MODIFY privilege on the training.sales.customers table. I ran the following command:

GRANT MODIFY ON TABLE training.sales.customers TO `data_engineers`;

By granting MODIFY, I allowed the data_engineers role to update, insert, and delete records in the table. This was necessary for them to perform their daily tasks, such as correcting customer information or adding new entries. However, I also wanted to ensure that only this role had this level of access.

Verifying the Privileges

After granting the privilege, I wanted to confirm that the data_engineers role indeed had the MODIFY permission. To do this, I ran the SHOW GRANTS command:

SHOW GRANTS ON TABLE training.sales.customers;

The output showed that the data_engineers role had been successfully granted the MODIFY privilege. This gave me confidence that the access control was working as intended. I also noticed that other roles or users did not have this privilege, which helped me understand the current state of access to the table.

Revoking MODIFY Privileges

Later, I realized that the data_engineers role no longer needed the MODIFY privilege. Perhaps the team had moved to a different process or the table was being archived. I decided to revoke the privilege to ensure that no one had unnecessary access. I ran the following command:

REVOKE MODIFY ON TABLE training.sales.customers FROM `data_engineers`;

After revoking the privilege, I checked the grants again using SHOW GRANTS to confirm that the MOD


This step was critical to maintaining security and ensuring that access was always aligned with the team’s needs. I learned that managing privileges like MODIFY is an ongoing process, and it’s important to review and adjust access regularly.