Using Dynamic Data Masking¶
This topic provides instructions on how to configure and use Dynamic Data Masking in Snowflake.
Using Dynamic Data Masking¶
The following lists the high-level steps to configure and use Dynamic Data Masking in Snowflake:
Grant masking policy management privileges to a custom role for a security or privacy officer.
The security or privacy officer creates and defines masking policies and applies them to columns with sensitive data.
Execute queries in Snowflake. Note the following:
Snowflake dynamically rewrites the query applying the masking policy SQL expression to the column.
The column rewrite occurs at every place where the column specified in the masking policy appears in the query (e.g. projections, join predicate, where clause predicate, order by, and group by).
Users see masked data based on the execution context conditions defined in the masking policies. For more information on the execution context in Dynamic Data Masking policies, see Advanced Column-level Security Topics.
Step 1: Grant Masking Policy Privileges to Custom Role¶
A security or privacy officer should serve as the masking policy administrator (i.e. custom role:
MASKING_ADMIN) and have the privileges to define, manage, and apply masking policies to columns.
Snowflake provides the following privileges to grant to a security or privacy officer for Column-level Security masking policies:
CREATE MASKING POLICY
This schema-level privilege controls who can create masking policies.
APPLY MASKING POLICY
This account-level privilege controls who can [un]set masking policies on columns and is granted to the ACCOUNTADMIN role by default. . This privilege only allows applying a masking policy to a column and does not provide any additional table privileges described in Access Control Privileges.
APPLY ON MASKING POLICY
Optional. This policy-level privilege can be used by a policy owner to decentralize the [un]set operations of a given masking policy on columns to the object owners (i.e. the role that has the OWNERSHIP privilege on the object). . Snowflake supports discretionary access control where object owners are also considered data stewards. . If the policy administrator trusts the object owners to be data stewards for protected columns, then the policy administrator can use this privilege to decentralize applying the policy [un]set operations.
The following example creates the
MASKING_ADMIN role and grants masking policy privileges to that role.
-- create a masking policy administrator custom role CREATE ROLE masking_admin; -- grant privileges to masking_admin role. GRANT CREATE MASKING POLICY on SCHEMA <schema_name> to ROLE masking_admin; GRANT APPLY MASKING POLICY on ACCOUNT to ROLE masking_admin; -- allow table_owner role to set or unset the ssn_mask masking policy (optional) GRANT APPLY ON MASKING POLICY ssn_mask to ROLE table_owner;
Specifies the identifier for the schema for which the privilege should be granted.
For more information, see:
Step 2: Create a Masking Policy¶
Using the MASKING_ADMIN role, create a masking policy and apply it to a column.
In this representative example, users with the ANALYST role see the unmasked value. Users without the ANALYST role see a full mask.
-- create masking policy create or replace masking policy email_mask as (val string) returns string -> case when current_role() in ('ANALYST') then val else '*********' end;
Step 3: Apply the Masking Policy to a Table or View Column¶
These examples assume that a masking policy is not applied to the table column when the table is created and the view column when the view is created. You can optionally apply a masking policy to a table column when you create the table with a CREATE TABLE statement or a view column with a CREATE VIEW statement.
Execute the following statements to apply the policy to a table column or a view column.
-- apply masking policy to a table column alter table if exists user_info modify column email set masking policy email_mask; -- apply the masking policy to a view column alter view user_info_v modify column email set masking policy email_mask;
Step 4: Query Data in Snowflake¶
Execute two different queries in Snowflake, one query with the ANALYST role and another query with a different role, to verify that users without the ANALYST role see a full mask.
-- using the ANALYST role use role ANALYST; select email from user_info; -- should see plain text value -- using the PUBLIC role use role public; select email from user_info; -- should see full data mask
Additional Masking Policy Examples¶
The following are additional, representative examples that can be used in the body of the Dynamic Data Masking policy.
Allow a production account to see unmasked values and all other accounts (e.g. development, test) to see masked values.
case when current_account() in ('<prod_account_identifier>') then val else '*********' end;
Return NULL for unauthorized users:
case when current_role() IN ('ANALYST') then val else NULL end;
Return a static masked value for unauthorized users:
CASE WHEN current_role() IN ('ANALYST') THEN val ELSE '********' END;
Return a hash value using SHA2 , SHA2_HEX for unauthorized users. Using a hashing function in a masking policy may result in collisions; therefore, exercise caution with this approach. For more information, see Advanced Column-level Security Topics.
CASE WHEN current_role() IN ('ANALYST') THEN val ELSE sha2(val) -- return hash of the column value END;
Apply a partial mask or full mask:
CASE WHEN current_role() IN ('ANALYST') THEN val WHEN current_role() IN ('SUPPORT') THEN regexp_replace(val,'.+\@','*****@') -- leave email domain unmasked ELSE '********' END;
case WHEN current_role() in ('SUPPORT') THEN val else date_from_parts(0001, 01, 01)::timestamp_ntz -- returns 0001-01-01 00:00:00.000 end;
Currently, Snowflake does not support different input and output data types in a masking policy, such as defining the masking policy to target a timestamp and return a string (e.g.
***MASKED***); the input and output data types must match.
Using a UDF:
CASE WHEN current_role() IN ('ANALYST') THEN val ELSE mask_udf(val) -- custom masking function END;
On variant data:
CASE WHEN current_role() IN ('ANALYST') THEN val ELSE OBJECT_INSERT(val, 'USER_IPADDRESS', '****', true) END;
Using a custom entitlement table. Note the use of EXISTS in the WHEN clause. Always use EXISTS when including a subquery in the masking policy body. For more information on subqueries that Snowflake supports, see Working with Subqueries.
CASE WHEN EXISTS (SELECT role FROM <db>.<schema>.entitlement WHERE mask_method='unmask' AND role = current_role()) THEN val ELSE '********' END;
case when current_role() in ('ANALYST') then DECRYPT(val, $passphrase) else val -- shows encrypted value end;
For examples using other context functions and role hierarchy, see Advanced Column-level Security Topics.