Understanding Dynamic Data Masking

This topic provides a general overview of the Dynamic Data Masking feature.

What is Dynamic Data Masking?

Dynamic Data Masking is a column-level security feature that uses masking policies to selectively mask plain-text data in table and view columns at query time.

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.

Dynamic Data Masking Benefits

The following summarizes some of the key benefits of Dynamic Data Masking.

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.

Data sharing

Easily mask data before sharing.

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.

Dynamic Data Masking Limitations

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

Dynamic Data Masking Privileges

The following table summarizes the privileges related to Dynamic Data Masking.

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.

Dynamic Data Masking DDL

Snowflake provides the following set of commands to manage Dynamic Data Masking policies.

Auditing Dynamic Data Masking

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 Dynamic Data Masking

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 Topics: