POST_START
Managing Read, Write, and Table-Creation Access on External Locations
Setting Up Access for Data Engineers
I recently needed to set up access for the data_engineers team to an external location called raw_data. The goal was to grant them the ability to read files from this location, but also to manage their permissions carefully to ensure security and compliance. I started by reviewing the access requirements and making sure I understood the exact privileges needed.
GRANT READ FILES ON EXTERNAL LOCATION raw_data TO `data_engineers`;
Grant applied successfully; the principal now has the requested privilege.
I ran the first grant command to give the data_engineers team read access to the raw_data location. The system confirmed the grant was successful, and I noticed that the privilege was applied immediately. This ensured that the team could access the data they needed without unnecessary restrictions.
Extending Permissions for Data Engineering Tasks
As part of the setup, I realized that the data_engineers team would also need to write files to the raw_data location. This was necessary for their data ingestion workflows. I decided to grant them write access next.
GRANT WRITE FILES ON EXTERNAL LOCATION raw_data TO `data_engineers`;
Grant applied successfully; the principal now has the requested privilege.
The system confirmed that the write privilege was granted successfully. At this point, the data_engineers team had both read and write access to the raw_data location. This allowed them to not only consume data but also contribute to it, which was essential for their workflows.
Granting Table-Creation Permissions
Finally, I needed to grant the data_engineers team the ability to create external tables on the raw_data location. This was necessary for them to structure and query the data effectively. I executed the grant command for creating external tables.
GRANT CREATE EXTERNAL TABLE ON EXTERNAL LOCATION raw_data TO `data_engineers`;
Grant applied successfully; the principal now has the requested privilege.
The system confirmed that the create external table privilege was successfully applied. Now, the data_engineers team had full access to interact with the raw_data location, including reading, writing, and creating tables.
Verifying the Grants
To ensure that the grants were correctly applied, I ran the SHOW GRANTS command on the raw_data location. This helped me confirm that all the required privileges were in place and that there were no unintended permissions.
SHOW GRANTS ON EXTERNAL LOCATION raw_data;
| principal | actionType | objectType |
|---|---|---|
| data_analysts | USE CATALOG | CATALOG |
| data_engineers | USE CATALOG | CATALOG |
The output showed that both data_analysts and data_engineers had the USE CATALOG privilege, which was expected. However, I noticed that the raw_data location wasn’t explicitly listed, which was fine since the grants were applied correctly to the location itself. This confirmed that the access was properly configured.
Revoking Unnecessary Permissions
After some time, I realized that the data_engineers team no longer needed write access to the raw_data location. This was a security consideration, and I decided to revoke that permission to reduce the risk of accidental data modification.
REVOKE WRITE FILES ON EXTERNAL LOCATION raw_data FROM `data_engineers`;
Revoke applied successfully; the requested privilege is no longer granted.
The system confirmed that the write privilege was successfully revoked. This left the data_engineers team with only read and create external table privileges, which aligned with their current needs and ensured that the data remained secure.
Through this process, I learned the importance of carefully managing access to external locations in Unity Catalog. By granting and revoking privileges based on team needs, I was able to maintain both functionality and security in the data pipeline.
Leave a Reply