In the Extension of my previous post : Row Access Policies, we are developing a scenario where we will be applying the RBAC to implement the Role Hierarchy along with Column Level Security (CLS) with the conjunction of Row Level Access (RLS) Policy. We will try to implement the data governance by applying all three concepts in one requirement.
We will implement the below Architecture as part of this Use case:
- CUSTOMER_CRID table holds the complete information about Customer in Snowflake Database.
- RBAC Hierarchy: REP1 –> FLM1 –> OPS
- REP1 reports to FLM which means FLM has access to it underlying Team
- FLM1 reports to OPS, means OPS has access to both TEAM REP and FLM.
- CLS (Column Level Security) :
Where REPS ,First level not authorized to view Customer Phone number and SSN ,
While the FLM ,above in the Hierarchy is authorized to access only Phone number as he/she can make a call to customer in case of issue but not SSN number which is confidential.
OPS the top most in hierarchy only authorized to access both Phone as well SSN number.
- RLS (Row Level Security):
REP which is lower in hierarchy is authorized to access only the data which belongs to CRID 2*, while FLM superior in hierarchy to the REP should be authorized to access 2* and 3* as well but not the T* CRID data.
OPS the top most in hierarchy should have access to all the data in Customer CRID table
- Mapping Table : Holds the relationship between CRID and ROLE.
Note: When a database object has both a row access policy and column masking policies, Snowflake evaluates the row access policy first. Also same column cannot be specified in both a masking policy signature and a row access policy signature at the same time.
- Create the Roles and establish the Role level hierarchy i.e. REP1->FLM –> OPS
- Create Users and assign the respective Roles to the Users.
- Grant the required privileges on DATABASE, WAREHOUSE and SCHEMA to the Respective Roles.
- Grant the SELECT privileges on CUSTOMER_CRID table to all the Roles Only SELECT would be suffice as we are not inserting any data.
- Also GRANT CREATE MASKING POLICY ON SCHEMA DEMO_DB.PUBLIC TO ROLE REP1.
- Login to the REP1 user and create the Masking policies.
- Login to the ACCOUNTADMIN and Assign these Masking policies to the SSN and Phone.
- Create the Row Access policy against the Customer CRID table.
- Create the Mapping table to define the ROLE-CRID relationship.
- Apply the Policy to the CRID column on Customer table.
- Query the data with respective Users login.
We have already covered how to create ROW Access Policy and Column Masking in earlier post. To implement the above Data governance scenario, Please refer the technical steps mentioned in the below posts.