- Categories:
CREATE MASKING POLICY¶
Creates a new Column-level Security masking policy. A masking policy is a schema-level object that can have multiple rules and a default function where, based on the executing context (e.g. role), one rule can be applied.
Snowflake evaluates the masking policy as a SQL expression. Therefore, rules are evaluated in the order they are written if specified as WHEN-THEN clauses in a CASE expression in the masking policy body.
A masking policy body is a SQL expression that specifies the rules. At runtime, Snowflake rewrites the query to apply the masking policy expression to the column.
After creating a masking policy, apply the masking policy to a column in a table or view using ALTER TABLE … ALTER COLUMN or ALTER VIEW.
- See also:
Choosing a Centralized, Hybrid, or Decentralized Approach, Advanced Column-level Security Topics
Syntax¶
CREATE [ OR REPLACE ] MASKING POLICY [ IF NOT EXISTS ] <name> AS
(VAL <data_type>) RETURNS <data_type> -> <expression_ON_VAL>
[ COMMENT = '<string_literal>' ];
Parameters¶
name
Identifier for the masking policy; must be unique for your schema.
The identifier value must start with an alphabetic character and cannot contain spaces or special characters unless the entire identifier string is enclosed in double quotes (e.g.
"My object"
). Identifiers enclosed in double quotes are also case-sensitive.For more details, see Identifier Requirements.
AS ( VAL data_type )
Defines the data type to mask. For more details, see Data Types.
RETURNS data_type
Defines the data type to return. For more details, see Data Types.
expression_ON_VAL
SQL expression that transforms the data.
The expression can include Conditional Expression Functions to represent conditional logic, built-in functions or UDFs to transform the data.
If a UDF or external function is used inside the masking policy body, the policy owner must have OWNERSHIP on the UDF or external function. Users querying a column that has a masking policy applied to it do not need to have USAGE on the UDF or external function.
COMMENT = 'string_literal'
Adds a comment or overwrites an existing comment for the masking policy.
Usage Notes¶
If you want to replace an existing masking policy and need to see the current definition of the policy, call the GET_DDL function or run the DESCRIBE MASKING POLICY command.
For masking policies that include a subquery in the masking policy body, use EXISTS in the WHEN clause. For a representative example, see the custom entitlement table example in the Examples section.
Examples¶
You can use Conditional Expression Functions, Context Functions, and UDFs to write the SQL expression.
The following are representative examples showing how to create masking policies using a full mask, a hash, a regular expression, and a UDF.
Full mask:
The ANALYST role can see the plain-text value. Users without the ANALYST role see a full mask.
CREATE OR REPLACE MASKING POLICY email_mask AS (val string) returns string -> CASE WHEN current_role() IN ('ANALYST') THEN VAL ELSE '*********' END;
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_name>') 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;
Using timestamps.
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;Important
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.A workaround is to cast the actual timestamp value with a fabricated timestamp value. For more information, see DATE_FROM_PARTS and CAST , ::.
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;
Using DECRYPT on previously encrypted data with either ENCRYPT or ENCRYPT_RAW, with a passphrase on the encrypted data:
case when current_role() in ('ANALYST') then DECRYPT(val, $passphrase) else val -- shows encrypted value end;
Using JavaScript UDFs on JSON (Variant):
In this example, a JavaScript UDF masks location data in a JSON string. It is important to set the data type as VARIANT in the UDF and the masking policy.
-- Flatten the JSON data create or replace table <table_name> (v variant) as select value::variant from @<table_name>, table(flatten(input => parse_json($1):stationLocation)); -- JavaScript UDF to mask latitude, longitude, and location data CREATE OR REPLACE FUNCTION full_location_masking(v variant) RETURNS variant LANGUAGE JAVASCRIPT AS $$ if ("latitude" in V) { V["latitude"] = "**latitudeMask**"; } if ("longitude" in V) { V["longitude"] = "**longitudeMask**"; } if ("location" in V) { V["location"] = "**locationMask**"; } return V; $$; -- Grant UDF usage to ACCOUNTADMIN grant ownership on function FULL_LOCATION_MASKING(variant) to role accountadmin; -- Create a masking policy using JavaScript UDF create or replace masking policy json_location_mask as (val variant) returns variant -> CASE WHEN current_role() IN ('ANALYST') THEN val else full_location_masking(val) -- else object_insert(val, 'latitude', '**locationMask**', true) -- limited to one value at a time END;
For examples using other context functions and role hierarchy, see Advanced Column-level Security Topics.