Using External Tokenization¶

Step 1: Create an external function¶

Create an external function in Snowflake and configure your cloud provider environment to communicate with the external function. For details, see:

• Creating external functions on AWS

• Creating external functions on Microsoft Azure

• Creating external functions on GCP

Step 2: 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:

The following example creates the MASKING_ADMIN role and grants masking policy privileges to that role.

Create a masking policy administrator custom role:

use role useradmin;
CREATE ROLE masking_admin;

Copy

Grant privileges to masking_admin role:

use role securityadmin;
GRANT CREATE MASKING POLICY on SCHEMA <db_name.schema_name> to ROLE masking_admin;
GRANT APPLY MASKING POLICY on ACCOUNT to ROLE masking_admin;

Copy

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;

Copy

Where:

• db_name.schema_name

  Specifies the identifier for the schema for which the privilege should be granted.

For more information, see:

• GRANT <privileges>

• Configuring access control

• Access control privileges

Step 3: Grant the Custom Role to a User¶

Grant the MASKING_ADMIN custom role to a user serving as the security or privacy officer.

use role useradmin;
grant role masking_admin to user jsmith;

Step 4: Create a Masking Policy¶

In this representative example, users with the ANALYST custom role see the detokenized email values. Users without the ANALYST custom role see the tokenized values.

The external function to detokenize email values is de_email().

-- create masking policy

create or replace masking policy email_de_token as (val string) returns string ->
  case
    when current_role() in ('ANALYST') then de_email(val)
    else val
  end;

Step 5: 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_de_token;

-- apply the masking policy to a view column

alter view user_info_v modify column email set masking policy email_de_token;

Step 6: Query Data in Snowflake¶

Execute two different queries in Snowflake, one query with the ANALYST custom role and another query with a different role, to verify that users without the ANALYST custom role see tokenized values.

-- using the ANALYST custom role

use role ANALYST;
select email from user_info; -- should see plain text value

-- using the PUBLIC system role

use role public;
select email from user_info; -- should see tokenized value