Snowflake Data Governance:
In my previous blog, we explored the concept of cloning a table, which in Snowflake makes information visible to all users accessing those cloned tables. However, this broad visibility may pose serious security concerns for organizations that prefer not to share data with everyone.
To address this issue, Snowflake offers features such as enforcing access policies on tables and allowing users to access only their relevant information.
Let me illustrate this with an example:
Consider a scenario where a school district holds assessment details for all pupils within its jurisdiction. It’s necessary for the district to share this information with individual schools. However, each school should only have access to its own pupils’ details. Let’s delve into how we can achieve this through data isolation using access policies.
Let’s say The school district’s table is called ASSESSMENT_DETAILS. There could be many schools in this School district.
For demonstration purposes, let’s consider three schools within the district:
a) Barnie Elementary School, with its table named BARNIE_SCHOOL_ASSESSMENT_DETAILS
b) Antlers Middle School, with its table named ANTLERS_SCHOOL_ASSESSMENT_DETAILS
c) EastMeadows Senior School, with its table named EASTMEA_SCHOOL_ASSESSMENT_DETAILS
Initially, we’ll clone the school district table for each school and then proceed to implement row-level access policies on each table. This way, each school will only have access to its own school-specific information.
- Cloning tables for each School from the School district’s ASSESSMENT_DETAILS table.
Here we have cloned the school district’s ASSESSMENT_DETAILS table
for
Barnie Elementary School as table BARNIE_SCHOOL_ASSESSMENT_DETAILS.
Antlers Middle School as table ANTLERS_SCHOOL_ASSESSMENT_DETAILS.
EastMeadows Senior School as table EASTMEA_SCHOOL_ASSESSMENT_DETAILS.
In the below screen, there are three Cloned tables available along with the source table.
2. We can see there are 1593 Pupil’s Assessment information in that school district for all the schools compounded.
3. To Achieve Data isolation for each school, We need to have specific Roles for each school.
Create Roles for each school.
4. Grant necessary Access permissions to the School
Similarly Grant necessary rights to other Roles (ANTLERS_ADMIN_ROLE, EASTMEA_ADMIN_ROLE). So for 3 Schools, we have 3 sets of Access Permissions role-wise.
5. Next, Let’s Create users for Three Schools: BARNIE_ADMIN, ANTLERS_ADMIN, and EASTMEA_ADMIN.
The Below Screenshot shows about one School BARNIE_ADMIN, Kindly repeat this for other schools.
6. After School Specific Roles and School specific User accounts are created
The next step is to specify a policy where each school can exclusively access its own data. This will be achieved through the implementation of a Role Mapping table containing detailed information on data visibility specific to each school.
7. The subsequent step involves crafting a policy that outlines data visibility for schools. Applying this policy to the table will enforce restrictions, ensuring that each school only has access to its own data.
8. Verification Steps: Let’s Sign in using the Users created with the Snowflake Account URL and find out whether we can view all data or school-specific data.
Login as BARNIE_ADMIN:
9. Login as BARNIE_ADMIN: Although there are additional tables in the database, our visibility was restricted to solely the Barnie School Assessment table. Snowflake’s access policy confines access solely to this table, ensuring data isolation.
Login as ANTLERS_ADMIN:
Here also we can see the Antlers School Assessment table only.
Login as EASTMEA_ADMIN:
Here also we are able to see the East Meadows School attendance table only.
Snowflake’s cloning capability is a streamlined process, requiring no extra infrastructure or cost. It can be executed swiftly, ensuring that the environment is swiftly prepared. By enforcing access policies within the table, Snowflake offers heightened security measures to tackle data governance concerns. Schools only incur costs for the data they access, optimizing financial efficiency.
Want to Connect with me?: sivakumar@assureeservices.com
