POST_START

Separating Producer Consumer and Administrator Roles in Production

I recently took on the task of setting up a secure and scalable environment for our production data in Databricks. One of the key requirements was to separate the roles of producers, consumers, and administrators to ensure that different teams had the appropriate level of access. I started by checking the current access permissions on the production catalog to understand what roles were already in place.

SHOW GRANTS ON CATALOG production;

I saw a representative result like this:

principal | actionType | objectType
data_analysts | SELECT | TABLE
data_engineers | MODIFY | TABLE

This output showed that the data_analysts had SELECT access to tables, while data_engineers had MODIFY access. However, I realized I needed to drill down further into specific schemas and tables to ensure that the access was properly scoped and that no unnecessary permissions were granted.

I next checked the production.sales schema to see what permissions were in place at that level.

SHOW GRANTS ON SCHEMA production.sales;

I saw a representative result like this:

principal | actionType | objectType
data_analysts | SELECT | TABLE
data_engineers | MODIFY | TABLE

This confirmed that the same roles had the same permissions at the schema level. It was good to see consistency, but I wanted to verify this at the table level as well to ensure that the access was properly defined for each individual table.

I ran the query on the specific table production.sales.orders to confirm the access permissions.

SHOW GRANTS ON TABLE production.sales.orders;

I saw a representative result like this:

principal | actionType | objectType
data_analysts | SELECT | TABLE
data_engineers | MODIFY | TABLE

With these results, I was able to confirm that the access permissions were consistent across the catalog, schema, and table levels. This helped me ensure that the roles were properly separated, with consumers (data analysts) only having read access and producers (data engineers) having the ability to modify data.

I also considered the need for an administrator role, which would have full access to manage and monitor the data. While I didn’t explicitly show the administrator grants in this example, I made sure to document the need for such a role in the access control strategy.

By following these steps, I was able to set up a clear and secure access model that aligned with our team’s operational needs. This helped prevent accidental or unauthorized changes to production data and ensured that each team had the right level of access for their responsibilities.