POST_START

Managing Read, Write, and Table-Creation Access on External Locations

I started my day by reviewing the access control requirements for our raw data location in Unity Catalog. The team needed to grant specific permissions to the `data_engineers` role—read access to files, write access to files, and the ability to create external tables. I knew that managing these permissions correctly was essential to ensure data security and collaboration without overexposing sensitive information.

I first checked the current grants on the external location to understand the existing permissions. I ran the `SHOW GRANTS` command to see what access was already in place.

Checking Current Permissions

I ran the following command to check the current grants on the `raw_data` external location:

SHOW GRANTS ON EXTERNAL LOCATION raw_data;

I noticed that there were no grants assigned yet. This meant that the `data_engineers` role had no permissions to interact with the location at all. It was time to start assigning the necessary access.

Granting Read Access to Files

I decided to start by granting read access to the files in the `raw_data` location. This would allow the data engineers to scan and process the data without modifying it. I ran the following command:

GRANT READ FILES ON EXTERNAL LOCATION raw_data TO `data_engineers`;

I verified that the command executed successfully, and I noticed that the `data_engineers` role now had the ability to read files from the location. This was a crucial first step in enabling collaboration while keeping the data safe.

Granting Write Access to Files

Next, I needed to grant write access so that the data engineers could process and save new files to the `raw_data` location. I ran the following command:

GRANT WRITE FILES ON EXTERNAL LOCATION raw, data TO `data_engineers`;

Wait, I noticed a typo in the command—`raw, data` instead of `raw_data`. I quickly corrected it and reran the command:

GRANT WRITE FILES ON EXTERNAL LOCATION raw_data TO `data_engineers`;

This time, the command executed successfully, and I confirmed that the `data_engineers` role now had write access. This meant they could now process data and save new files to the location.

Granting Table-Creation Access

Finally, I needed to grant the ability to create external tables so that the data engineers could structure and query the data using SQL. I ran the following command:

GRANT CREATE EXTERNAL TABLE ON EXTERNAL LOCATION raw_data TO `data_engineers`;

I verified that the command executed successfully, and I noticed that the `data_engineers` role now had the ability to create external tables. This was the last piece of the access puzzle, enabling them to work with the data in a structured and efficient way.

Verifying All Grants

To make sure everything was set up correctly, I ran the `SHOW GRANTS` command again to confirm all the permissions I had just assigned:

SHOW GRANTS ON EXTERNAL LOCATION raw_data;

The output showed that the `data_engineers` role had been granted read, write, and create external table permissions on the `raw_data` location. I felt confident that the access model was secure and aligned with the team’s needs.

Revoking Write Access

Later in the day, I received feedback from the team that they did not need write access anymore. They only required read access and the ability to create external tables. I decided to revoke the write permission to ensure that no unnecessary access was in place.

I ran the following command to revoke the write access:

REVOKE WRITE FILES ON EXTERNAL LOCATION raw_data FROM `data_engineers`;

I verified that the command executed successfully and then ran the `SHOW GRANTS` command again to confirm that the write access had been removed. This step was important to maintain a secure and minimal access model.

By the end of the day, I had successfully managed the access permissions for the `raw_data` external location. I learned that carefully assigning and revoking permissions is key to maintaining data security and enabling collaboration in a controlled environment. Using Unity Catalog’s grant and revoke commands allowed me to make precise changes without affecting other roles or locations.