- Purpose of the article: In this blog, we have explained clearly about the row access policy and column level security from the users.
- Intended Audience: this POC/blog will help for the people to mask an important or sensitive data from the users.
- Tools and Technology: Snowflake(snowsight).
- Key words: Row access policy from user (data masking).
What’s data masking:
Data masking creates a fake data with realistic interpretation of orginal data.
Types of data masking:
- Column level security (dynamic data masking)
- Row level Security
Dynamic data masking:
Dynamic data masking is a masking policy for a column that has formally loaded as a understandable (text) format in Snowflake.
Row level Security:
Row level Security is useful to return the specified rows from the table through from the query.
Uses of data masking:
- Protects sensitive data from third party systems
- Reduces data loss and risks
Flowchart:
Way to create Data masking in Snowsight
- Step-1: Create account and login to the snowsight and go for worksheet.
- Step-2: Create a data warehouse to collect data from many data sources.
- Step-3: Create a database, create table and insert values into it. Here we have created secure_db database and within the secure_db database we have created secure_db table.
- Step-4: Insert the values in the table.
- Step-5: Use role Accountadmin to create a masking policy for a column.
- Step-6: Apply that column level masking policy to the column in the table.
- Step-7: As we are in Accountadmin the data is visible.
- Step-8: Now change the role and then see the data in the table. (Phone_number column in the table is masked)
- Step-9: Let’s create a mapping table which is used for filtering the rows or which rows in the table must be visible for the specified user.
- Step-10: Insert the values into the mapping table.
- Step-11: Now let’s create a row access policy for a column in the table (datamask). Here we have created a row access policy for a job_id column in the datamask table.
Step-12: Add that masking policy to the column in table.
- Step-13: Select the data from the table. As it is in Accountadmin the data is visible.
- Step-14: Now create a role for the roles in the filtering table.
- Step-15: Grant t permission on usage of warehouse for the created roles.
- Step-16: Grant permission on usage of database for the created roles.
- Step-17: Grant permission on usage of schema for the created roles.
- Step-18: Grant permission on usage of tables for the created roles.
- Step-19: Now let’s create a user and grant permission of a role to that user.
- Step-20: Open a new table and login with user credentials.
- Step-21: Within the worksheets, you can see the role and warehouse of a generaluser. Here for SYSROLE role I have assigned all the dept_names.
- Step-22: By using that role and data warehouse you can access the table.
- Step-23: Here we can see the three job_ids that are assigned for generaluser.
- Step-24: Now create a new user and grant the permission of a role to that user.
- Step-25: Now login with the new credentials.
- Step-26: By using the role and data warehouse you can access the table. Here for USROLE role I have assigned only the IT_PROG dept_names.
- Step-27: Here we can see only one job_id that are assigned to restricted user.
Author Bio:
Thanusha Payyavula
Associate Software Engineer
I am Thanusha Payyavula working as Associate Software Engineering at Mouri Tech for the past 1.5 years. I have good experience in SQL, Snowflake, AWS, and Qlikview. I have been involved in multiple projects where I have worked with multiple technologies and been involved in a couple of activities to find the solution in project deliverables. .
