Understanding External Tokenization

This topic provides a general overview of the External Tokenization feature.

Important

External tokenization requires External Functions, which are included in the Snowflake Standard Edition, and you can use external functions with a tokenization provider (i.e. Protegrity).

However, if you choose to integrate your tokenization provider with Snowflake External Tokenization, you must upgrade to Enterprise Edition or higher.

To inquire about upgrading, please contact Snowflake Support.

What is External Tokenization?

External Tokenization enables accounts to tokenize data before loading it into Snowflake and detokenize the data at query runtime. Tokenization is the process of removing sensitive data by replacing it with an undecipherable token. External Tokenization makes use of masking policies with external functions.

In Snowflake, masking policies are schema-level objects, which means a database and schema must exist in Snowflake before a masking policy can be applied to a column. Currently, Snowflake supports using Dynamic Data Masking on tables and views.

At query runtime, the masking policy is applied to the column at every location where the column appears. Depending on the masking policy conditions, the SQL execution context, and role hierarchy, Snowflake query operators may see the plain-text value, a partially masked value, or a fully masked value.

For more details about how masking policies work, including the query runtime behavior, creating a policy, usage with tables and views, and management approaches using masking policies, see: Understanding Column-level Security.

For more details on the effects of the SQL execution context and role hierarchy, see Advanced Column-level Security Topics.

Tokenizing data before loading into Snowflake ensures that sensitive data is never exposed unnecessarily. Using masking policies with external functions ensures that only the appropriate audiences can view de-tokenized data at query runtime.

Currently, Snowflake supports using Protegrity to manage the tokenization of sensitive data across all data stores (e.g. tables, databases, data warehouses) in your organization. When Snowflake calls the external function in the masking policy, a REST API call is made to the Protegrity Data Security Gateway (DSG).

Since an external function does not execute in Snowflake, it is necessary to enable an Amazon API Gateway in your environment and create a REST API integration between Snowflake and the server that tokenizes that data. At query runtime, if the query invokes one or more columns in which a masking policy with an external function applies, Protegrity detokenizes the column data to send back to Snowflake. The masking policy ensures that only users that are members of the specified roles can see the detokenized data.

External Tokenization Benefits

The following summarizes some of the key benefits of External Tokenization.

Pre-load Tokenized Data

Using a tokenization provider, tokenized data is pre-loaded into Snowflake. Therefore, even without applying a masking policy to a column in a table or view, users never see the real data value. This provides enhanced data security to the most sensitive data in your organization.

Ease of use

You can write a policy once and have it apply to thousands of columns across databases and schemas.

Data administration and SoD

A security or privacy officer decides which columns to protect, not the object owner. Masking policies are easy to manage and support centralized and decentralized administration models.

Data authorization and governance

Contextual data access by role or custom entitlements.

Supports data governance as implemented by security or privacy officers and can prohibit privileged users with the ACCOUNTADMIN or SECURITYADMIN role from unnecessarily viewing data.

Change management

Easily change masking policy content without having to reapply the masking policy to thousands of columns.

For a comparison of benefits between Dynamic Data Masking and External Tokenization, see: Column-level Security Benefits.

External Tokenization Limitations

For an overview on the limitations, see Column-level Security Limitations.

External Tokenization Privileges and Dependencies

The following table summarizes the privileges related to External Tokenization masking policies.

Privilege

Usage

APPLY

Enables applying the policy [un]set operations for the masking policy.

OWNERSHIP

Transfers ownership of a masking policy, which grants full control over the masking policy. Required to alter most properties of a masking policy.

Note

Operating on a masking policy also requires the USAGE privilege on the parent database and schema.

Since the external tokenization masking policy requires an external function that depends on an API integration, the following table summarizes the privileges the custom role (e.g. MASKING_ADMIN) must have on Snowflake objects. Note that these privileges apply to the custom role only and are not necessary for the role of the user querying the column with a masking policy.

Custom role

Privilege

Object

External tokenization policy owner

USAGE

External function

External function owner (i.e. the role with the OWNERSHIP privilege on the external function)

USAGE

Any API integration objects that are referenced by the external function.

External Tokenization DDL

Snowflake provides the following set of commands to manage External Tokenization policies.

Auditing External Tokenization

Snowflake records the original query run by the user on the History page (in the web interface). The query is found in the SQL Text column.

The masking policy names that were used in a specific query can be found in the Query Profile.

Currently, the query history is specific to the ACCOUNT_USAGE QUERY_HISTORY view only. In this view, the Query Text column contains the text of the SQL statement. Masking policy names are not not included in the QUERY_HISTORY view.

Troubleshooting External Tokenization

You can use error messages to help troubleshoot masking policy issues.

Error Messages

The following table describes error messages Snowflake can return while using masking policies.

Behavior

Error Message

Troubleshooting Action

Cannot apply a masking policy to a Snowflake feature.

FAILURE: Unsupported feature CREATE ON MASKING POLICY COLUMN.

Masking policies are currently not applicable to this feature.

An active role cannot create or replace a masking policy.

FAILURE: SQL access control error: Insufficient privileges to operate on account <account_name>

Grant the CREATE MASKING POLICY privilege to the specified role using grant create masking policy on account to role <role_name>; . Verify the role has the privilege using show grants to role <role_name>, and try the CREATE OR REPLACE masking statement again.

A given role cannot attach a masking policy to a table.

FAILURE: SQL compilation error: Database <database_name> does not exist or not authorized.

Grant the APPLY MASKING POLICY privilege to the role using grant apply masking policy on account to role <role_name>;

A given role that does not own a masking policy on a table tries to apply a masking policy on a table they can use.

FAILURE: SQL compilation error: Masking policy <policy_name> does not exist or not authorized.

Grant the given role usage on the masking policy using grant apply on masking policy <policy_name> to role sysadmin;

Cannot drop or remove a policy using drop masking policy <policy_name>;

FAILURE: SQL compilation error: Policy <policy_name> cannot be dropped/replaced as it is associated with one or more entities.

Use an ALTER Table/View MODIFY COLUMN statement to UNSET the policy first, then try the DROP statement again.

Restoring a dropped table produces a masking policy error.

FAILURE: SQL execution error: Column <column_name> already attached to a masking policy that does not exist. Please contact the policy administrator.

Unset the currently attached masking policy with an ALTER Table/View MODIFY COLUMN statement and then reapply the masking policy to the column with a CREATE OR REPLACE statement.

Cannot apply a masking policy to a specific column, but the masking policy can be applied to a different column.

FAILURE: Specified column already attached to another masking policy.A column cannot be attached to multiple masking policies.please drop the current association in order to attach a new masking policy.

Decide which masking policy should apply to the column, update, and try again.

Updating a policy with an ALTER statement fails.

FAILURE: SQL compilation error: Masking policy <policy_name> does not exist or not authorized.

Verify the policy name in the ALTER command matches an existing policy by executing show masking policies;

The cloned table owner role cannot unset a masking policy.

FAILURE: SQL access control error: Insufficient privileges to operate on ALTER TABLE UNSET MASKING POLICY ‘{1}’

Grant the APPLY privilege to the table cloner role using grant apply on masking policy ssn_mask to role <role_name>; . Verify that the table cloner role has the grant using show grants to role table_cloner_role; and try the ALTER statement again.

Updating a policy using IF EXISTS returns a successful result but does not update the policy.

No error message returned; Snowflake returns Statement executed successfully.

Remove IF EXISTS from the ALTER statement and try again.

While creating or replacing a masking policy with CASE, the data types do not match (e.g. (VAL string) -> returns number).

FAILURE: SQL compilation error: Masking policy function argument and return type mismatch.

Update the masking policy using CASE with matching data types using a CREATE OR REPLACE statement or an ALTER MASKING POLICY statement.

Apply a masking policy to a virtual column.

FAILURE: SQL compilation error: Masking policy cannot be attached to a VIRTUAL_COLUMN column.

Apply the masking policy to the column(s) in the source table.

Apply a masking policy to a materialized view.

FAILURE: SQL compilation error: syntax error line <number> at position <number> unexpected ‘modify’. . FAILURE: SQL compilation error: error line <number> at position <number> invalid identifier ‘<character>’

Apply the masking policy to the column(s) in the source table.

Apply a masking policy to a table column used to create a materialized view

FAILURE: SQL compilation error: Masking policy cannot be attached to a MATERIALIZED_VIEW column.

To apply the masking policy to the table column, drop the materialized view.

Next Topic: