Categories:: Table, View, & Sequence DDL

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.