A masking policy in Snowflake is a robust security feature available in the Enterprise Edition or higher. It allows the creation, management, and application of rules to mask or tokenize sensitive data in table columns or views. This feature ensures that sensitive data is protected and only accessible based on predefined roles and conditions.
CREATE [ OR REPLACE ] MASKING POLICY [ IF NOT EXISTS ] <name>
AS ( <arg_name_to_mask> <arg_type_to_mask> [ , <arg_1> <arg_type_1> ... ] )
RETURNS <arg_type_to_mask> -> <body>
[ COMMENT = '<string_literal>' ]
[ EXEMPT_OTHER_POLICIES = { TRUE | FALSE } ]
This code snippet demonstrates the syntax for creating a masking policy in Snowflake. The policy defines rules and conditions under which data should be masked, ensuring sensitive information is protected.
How is a Masking Policy Created?
Creating a masking policy involves defining rules and conditions under which data should be masked. The syntax for creating a masking policy is as follows:
CREATE OR REPLACE MASKING POLICY email_mask
AS (val string)
RETURNS string ->
CASE
WHEN current_role() IN ('ANALYST') THEN val
ELSE '*********'
END;
This example shows a masking policy that displays plain-text for the 'ANALYST' role and a masked value for others.
- name: A unique identifier for the masking policy within the schema.
- AS (arg_name_to_mask arg_type_to_mask [ , arg_1 arg_type_1 ... ]): Specifies the input columns and data types to evaluate at query runtime.
- RETURNS arg_type_to_mask: The return data type must match the input data type of the first column.
- body: SQL expression that transforms the data in the column.
What are the Access Control Requirements?
To create and manage masking policies, specific privileges are required:
- CREATE MASKING POLICY: Privilege on the schema.
- USAGE: Privilege on the parent database and schema.
How to Use and Manage Masking Policies?
1. Viewing and Managing Policies
To see the current definition of an existing masking policy, use the GET_DDL function or DESCRIBE MASKING POLICY command. It's essential to centralize mapping tables in the same database as the protected table. Notably, a column can be specified in either a masking policy or a row access policy, but not both simultaneously.
2. Tag-based Masking Policies
Tag-based masking policies allow users to combine tagging and masking policies, setting them at the TAG level using the ALTER command.
CREATE TAG sensitive_data;
CREATE MASKING POLICY email_mask AS (val string) RETURNS string -> CASE WHEN current_role() IN ('ANALYST') THEN val ELSE '*********' END;
ALTER TAG sensitive_data SET MASKING POLICY email_mask;
ALTER TABLE employee SET TAG sensitive_data;
This example demonstrates creating a tag, a masking policy, and assigning the policy to the tag and the tag to a table.
3. Implementation Steps
Follow these steps to implement tag-based masking policies:
- Create a Tag: Use the
CREATE TAGcommand. - Create a Masking Policy: Use the
CREATE MASKING POLICYstatement. - Set Masking Policy to Tag: Use the
ALTERcommand. - Set Tag to Snowflake Objects: Use the
ALTERcommand.
Common Challenges and Solutions
- Ensure the data types in the masking policy match the column data types to avoid errors.
- Verify that the necessary privileges are granted to create and manage masking policies.
- Use the
INFORMATION_SCHEMAto validate the application of masking policies.
Recap
- Snowflake masking policies provide robust security for sensitive data.
- Tag-based masking policies offer scalable and flexible data protection.
- Proper implementation and validation are crucial for maintaining data security and compliance.
