Categories:: Table, View, & Sequence DDL

CREATE MASKING POLICY¶

Creates a new masking policy in the current/specified schema or replaces an existing masking policy.

After creating a masking policy, apply the masking policy to a column in a table using an ALTER TABLE … ALTER COLUMN command or a view using an ALTER VIEW command.

Masking Policy DDL

In this Topic:

Syntax
Parameters
Access Control Requirements
Usage Notes
Examples
- Normal Masking Policy
- Conditional Masking Policy

Syntax¶

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> -> <expression_on_arg_name>
[ 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 ( arg_name_to_mask arg_type_to_mask [ , arg_1 arg_type_1 ... ] )

The signature for the masking policy; specifies the input columns and data types to evaluate at query runtime.

For more details, see Data Types.

arg_name_to_mask arg_type_to_mask

The first column and its data type always indicate the column data type values to mask or tokenize in the subsequent policy conditions.

Note that you can not specify a virtual column as the first column argument in a conditional masking policy.

[ , arg_1 arg_type_1 ... ]

Specifies the conditional columns and their data types to evaluate to determine whether the policy conditions should mask or tokenize the data in the first column in each row of the query result.

If these additional columns and data types are not specified, Snowflake evaluates the policy as a normal masking policy.

RETURNS arg_type_to_mask

The return data type must match the input data type of the first column that is specified as an input column.

expression_on_arg_name

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 USAGE on the UDF or external function. The USAGE privilege on the UDF or external function is not required for the role used to query a column that has a masking policy applied to it.

If a UDF or external function is used inside the conditional masking policy body, the policy owner must have OWNERSHIP on the UDF or external function. Users querying a column that has a conditional 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.

Access Control Requirements¶

A role used to execute this SQL command must have the following privileges at a minimum:

Privilege	Object	Notes
CREATE MASKING POLICY	Schema

Note that operating on any object in a schema also requires the USAGE privilege on the parent database and schema.

For instructions on creating a custom role with a specified set of privileges, see Creating Custom Roles.

For general information about roles and privilege grants for performing SQL actions on securable objects, see Access Control in Snowflake.

For additional details on masking policy DDL and privileges, see Managing Column-level Security.

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 Normal Masking Policy section.
A given table or view column can be specified in either a masking policy signature or a row access policy signature. In other words, the same column cannot be specified in both a masking policy signature and a row access policy signature at the same time.

For more information, see CREATE ROW ACCESS POLICY.
A data sharing provider cannot create a masking policy in a reader account.
If using a UDF in a masking policy, ensure the data type of the column, UDF, and masking policy match. For more information, see User-defined Functions in a Masking Policy.
Regarding metadata:

Attention

Customers should ensure that no personal data (other than for a User object), sensitive data, export-controlled data, or other regulated data is entered as metadata when using the Snowflake service. For more information, see Metadata Fields in Snowflake.
CREATE OR REPLACE <object> statements are atomic. That is, when the object is replaced, the old object deletion and the new object creation are processed in a single transaction.

Examples¶

Normal Masking Policy¶

You can use Conditional Expression Functions, Context Functions, and UDFs to write the SQL expression.

The following are representative examples of the policy body to show how to create masking policy conditions using different SQL expressions, functions, and data types.

These examples mostly use the CURRENT_ROLE context function. If role activation and role hierarchy is necessary in the policy conditions, use IS_ROLE_IN_SESSION.

Full mask:

The analyst custom role can see the plain-text value. Users without the analyst custom 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_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;

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. If the data type in the table column, UDF, and masking policy signature do not match, Snowflake returns an error message because it cannot resolve the SQL.

-- 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;

Using the GEOGRAPHY data type:

In this example, a masking policy uses the TO_GEOGRAPHY function to convert all GEOGRAPHY data in a column to a fixed point, the longitude and latitude for Snowflake in San Mateo, California, for users whose CURRENT_ROLE is not ANALYST.

create masking policy mask_geo_point as (val geography) returns geography ->
  case
    when current_role() IN ('ANALYST') then val
    else to_geography('POINT(-122.35 37.55)')
  end;

Set the masking policy on a column with the GEOGRAPHY data type and set the GEOGRAPHY_OUTPUT_FORMAT value for the session to GeoJSON:

alter table mydb.myschema.geography modify column b set masking policy mask_geo_point;
alter session set geography_output_format = 'GeoJSON';
use role public;
select * from mydb.myschema.geography;

Snowflake returns the following:

---+--------------------+
 A |         B          |
---+--------------------+
 1 | {                  |
   |   "coordinates": [ |
   |     -122.35,       |
   |     37.55          |
   |   ],               |
   |   "type": "Point"  |
   | }                  |
 2 | {                  |
   |   "coordinates": [ |
   |     -122.35,       |
   |     37.55          |
   |   ],               |
   |   "type": "Point"  |
   | }                  |
---+--------------------+

The query result values in column B depend on the GEOGRAPHY_OUTPUT_FORMAT parameter value for the session. For example, if the parameter value is set to WKT, Snowflake returns the following:

alter session set geography_output_format = 'WKT';
select * from mydb.myschema.geography;

---+----------------------+
 A |         B            |
---+----------------------+
 1 | POINT(-122.35 37.55) |
 2 | POINT(-122.35 37.55) |
---+----------------------+

For examples using other context functions and role hierarchy, see Advanced Column-level Security Topics.

Conditional Masking Policy¶

The following example returns unmasked data for users whose CURRENT_ROLE is the admin custom role, or whose value in the visibility column is Public. All other conditions result in a fixed masked value.

-- Conditional Masking

create masking policy email_visibility as
(email varchar, visibility string) returns varchar ->
  case
    when current_role() = 'ADMIN' then email
    when visibility = 'Public' then email
    else '***MASKED***'
  end;

The following example returns detokenized data for users whose CURRENT_ROLE is the admin custom role, and whose value in a different column is Public. All other conditions result in a tokenized value.

-- Conditional Tokenization

create masking policy de_email_visibility as
 (email varchar, visibility string) returns varchar ->
   case
     when current_role() = 'ADMIN' and visibility = 'Public' then de_email(email)
     else email -- sees tokenized data
   end;