Snowflake — A smart RBAC Architecture Pattern!
Reference — Snowflake Pattern — Security — Access to Sensitive Objects
When I was browsing through the Architecture Patterns by Snowflake, I came across this interesting recommendation, put forth in one of the Security Patterns, of designing an efficient RBAC hierarchy, which is simple, scalable and easy to maintain.
In this blog post, I tried to visualise the pattern using pictorial representation of the concept explained in it.
Scenario
Within a PROD_DB database, there are 2 schemas — PUBLIC_SCHEMA & SENSITIVE_SCHEMA. As the name suggests, the non-sensitive objects are organised within PUBLIC_SCHEMA & sensitive data objects are organised within SENSITIVE_SCHEMA. For this scenario, we are going to look at a RBAC design pattern that is efficient and easily scalable.
Disclaimer
- This pattern does not prescribe how to populate these objects,perform row or column level security, or grant roles to users; each of which may also be required. The scope of this pattern is simply how to provide visibility to the objects themselves.
- This pattern assumes a user should have the same access level permissions on objects in a database. If the user indeed requires separate permissions levels for schemas contained within the same database, the model may need to be extended or a different model used.
Privileges
Database Objects
Recommended RBAC Pattern
The essence of this particular RBAC Architecture Pattern lies with the fact that, even if a user is granted with the role(s) to access all objects (Tables, Views, etc.) in the Database, unless and until he is granted with a role containing the USAGE privilege of a particular Schema, he will NOT be able to access those objects within that Schema.
Based on this, we can leverage the USAGE privilege at the Schema level as a control switch, to restrict access to the underlying objects, even if the Role holds enough Grants on those underlying objects.
Analogy
Ima room with few boxes full of valuables. Here, the room is analogous to a Schema & the boxes are analogous to the Objects within that Schema.
Even if someone holds the purple key, which can unlock all the boxes, without the blue key to the room, that person cannot access the boxes.
Traditional RBAC Pattern (Not Recommended)
If we follow the traditional methodology of designing the RBAC hierarchy for the scenario in discussion, we would end up in a more complex structure as below. But with the smart approach of leveraging the USAGE ON SCHEMA privilege as briefed in the previous section, we can avoid this and have a simpler, easy to scale/maintain RBAC hierarchy as above.
About Me
I am Madhivanan Anbalagan, currently working as an Associate Solution Architect at kipi.bi (an Elite Partner of Snowflake), located in Bangalore, India. I have an extensive 9+ years of experience in the data space, lucky to have played various roles such as Data Engineer, ETL Developer, Oracle PL/SQL Developer, etc. In my current role, I primarily architect strategic solutions for the clients with Snowflake Data Cloud as the platform of choice, along with the Modern Data Stack tools & technologies (such as dbt, Fivetran, Matillion, Airflow, etc.). I’m loving it! 💙💚
Along with my passion towards data, my interests are Photography and Graphic Design. I’m also sort of a perfectionist, a sample of that is in the banner image of this blog post up top! Its basically a gradient of 2 precise hex color codes (Snowflake Blue ↔ kipi.bi Green), which I’m planning to keep in my future blog posts as a color signature.
Please feel free to Follow or Connect with me on LinkedIn or other social media platforms. You can find me on pretty much all the platforms with the username @madhiceg.
