POST_START

Designing Service Principal Access for Production Data Pipelines

I recently took on the task of securing access for our ETL pipelines to a production data catalog in Databricks. The goal was to ensure that the service principals used by our data pipelines had the right level of access without exposing sensitive data unnecessarily. I started by understanding the structure of the catalog and the specific needs of the ETL processes.

Granting Access to the Production Catalog

The first step was to grant the service principal access to the entire production catalog. This is necessary because the ETL pipelines need to interact with multiple schemas and tables within this catalog. I ran the following SQL command to grant the use of the catalog:

GRANT USE CATALOG ON CATALOG production TO `etl-service-principals`;

I saw a representative result like this:

result
Grant applied successfully; the principal now has the requested privilege.

I learned that granting access to the catalog allows the service principal to navigate and access its contents, which is essential for the pipelines to function properly.

Granting Access to the Sales Schema

Next, I needed to ensure that the service principal could access the sales schema within the production catalog. This schema contains critical data that the ETL pipelines process regularly. I executed the following SQL command to grant access to the schema:

GRANT USE SCHEMA ON SCHEMA production.sales TO `etl-service-principals`;

I saw a representative result like this:

result
Grant applied successfully; the principal now has the requested privilege.

I verified that this step was necessary because the ETL pipelines need to read and write data within this schema. Granting access to the schema itself allows the service principal to interact with the tables within it.

Granting Access to the Orders Table

Finally, I needed to grant the service principal the ability to select and modify data in the orders table within the sales schema. This table is the primary source of data for our ETL processes, and the pipelines need to both read from and write to it. I ran the following SQL command:

GRANT SELECT, MODIFY ON TABLE production.sales.orders TO `etl-service-principals`;

I saw a representative result like this:

result
Grant applied successfully; the principal now has the requested privilege.

I noticed that this grants the service principal both read and write access, which is essential for the ETL pipelines to process and update data. I made sure that I only granted the necessary privileges and not more than required, following the principle of least privilege.

Conclusion

By carefully granting access to the production catalog, the sales schema, and the orders table, I ensured that the ETL pipelines had the right level of access to perform their tasks securely. This approach not only allowed the pipelines to function as intended but also helped maintain the integrity and security of our production data.