Tag-based Masking Policies

This topic provides concepts about tag-based masking policies and examples of tag-based masking policies to protect column data.

In this Topic:

Overview

A tag-based masking policy combines the object tagging and masking policy features to allow a masking policy to be set on a tag using an ALTER TAG command. When the data type in the masking policy signature and the data type of the column match, the tagged column is automatically protected by the conditions in the masking policy. This simplifies the data protection efforts because column data that should be protected no longer needs a masking policy manually applied to the column to protect the data. A column can be protected by a masking policy directly assigned to a column and a tag-based masking policy. If a column references both of these masking policies, the masking policy that is directly assigned to the column takes precedence over the tag-based masking policy.

The tag can support one masking policy for each data type that Snowflake supports. To simplify the initial column data protection efforts, create a generic masking policy for each data type (e.g. STRING, NUMBER, TIMESTAMP_LTZ) that allows authorized roles to see the raw data and unauthorized roles to see a fixed masked value.

The masking policy conditions can be written to protect the column data based on the policy assigned to the tag or protect the column data based on the tag string value of the tag assigned to the column, depending upon the decisions of the policy administrator, tag administrator, and data steward.

For examples of tag-based masking policies, see Using Tag-Based Masking Policies section (in this topic).

Benefits

Ease of Use

Assigning one or more masking policies to a tag is simple. Policy administrators can add or replace policies without breaking existing workflows.

Scalable

Tag-based policies allow policy administrators to write a policy once, assign a policy to a tag once, and, depending on the level at which the tag is set, have the policy apply to many objects. This results in the vast reduction of manually assigning a single policy to a single column every time a new column is created or replaced.

Comprehensive

Policy administrators can create a policy for each data type and assign all of those policies to a single tag. Once the tag is applied at the table level, all columns in the table are protected, provided that the column data type matches the data type specified in the policy.

Protect future objects

Assigning a tag-based masking policy to a table automatically applies the masking policy to any new table columns. This behavior is analogous to future grants.

Flexibility

Tag-based masking policies offer an alternative to specifying the masking policy in a CREATE TABLE statement, which helps to simplify table DDL management. Administrators can choose to assign the masking policy either at table creation or by assigning the policy to the tag, which uses tag lineage.

Considerations

  • For a tag-based masking policy where the tag is stored in a different schema than the masking policy and table, cloning the schema containing the masking policy and table results in the cloned table being protected by the masking policy in the source schema not the cloned schema.

    However, for a tag-based masking policy where the tag, masking policy, and table all exist in the schema, cloning the schema results in the table being protected by the masking policy in the cloned schema, not the source schema.

  • Regarding replication and tag-based masking policies, see policy replication considerations.

  • For details about Sharing Data Securely in Snowflake and this feature, see:

Limitations

All of the existing masking policy limitations apply to tag-based masking policies.

Note the following additional limitations when using tag-based masking policies:

Data types

A tag can support one masking policy for each data type. For example, if a tag already has a masking policy for the NUMBER data type, you cannot assign another masking policy with the NUMBER data type to the same tag.

System tags

A masking policy cannot be assigned to a system tag.

Dropping objects

Neither the masking policy nor the tag can be dropped if the masking policy is assigned to a tag. Similarly, the parent schema and database containing the tag and the masking policy cannot be dropped if the policy is assigned to a tag. For more information, see Assignment (in this topic).

Materialized Views

A materialized view cannot be created if the underlying table is protected by a tag-based masking policy. For additional details, see masking policies and materialized views.

If a materialized view exists and a tag-based masking policy is added to the underlying table later, the materialized view cannot be queried; the materialized view is now invalidated. To continue using the materialized view, unset the tag-based masking policy, recreate or resume, and then query the materialized view.

Row access policies

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.

Conditional columns

A masked column cannot be used as a conditional column in a masking policy.

Mapping tables

A table containing a column protected by a tag-based masking policy cannot be used as a mapping table.

Virtual columns

While a virtual column can be assigned a tag, the virtual column cannot be assigned a tag-based masking policy. If a tag-based policy is assigned to a virtual column, the query on the column fails at runtime.

Managing Tag-Based Masking Policies

The existing privileges for masking policies and tags, along with the DDL to manage masking policies and tags, apply to tag-based masking policies.

However, there are additional updates to APPLY MASKING POLICY privilege, the ALTER TAG command, and the DROP/REPLACE operations when the policy and tag are in the same parent database/schema.

Privilege

A role with the global APPLY MASKING POLICY privilege can set a masking policy on a tag and unset a masking policy from a tag.

For example, grant the global APPLY MASKING POLICY privilege to the TAG_ADMIN custom role:

use role securityadmin;

grant apply masking policy on account to role tag_admin;

Assignment

Use an ALTER TAG command to set a masking policy on a tag or to unset a masking policy from a tag. Note that syntax for the ALTER TAG command allows assigning multiple masking policies to a tag in a single statement:

ALTER TAG <tag_name> SET MASKING POLICY <masking_policy_name> [ , MASKING POLICY <masking_policy_2_name> , ... ]

ALTER TAG <tag_name> UNSET MASKING POLICY <masking_policy_name> [ , MASKING POLICY <masking_policy_2_name> , ... ]

For example, assign the masking policy named ssn_mask to the tag named security:

-- set
alter tag security set masking policy ssn_mask;

-- unset

alter tag security unset masking policy ssn_mask;

In addition to the ALTER TAG usage notes, note the following:

  • A tag can have only one masking policy per data type. For example, one policy for the STRING data type, one policy for NUMBER data type, and so on.

  • If a masking policy already protects a column and a tag with a masking policy is set on the same column, the masking policy directly assigned to the column takes precedence over the masking policy assigned to the tag.

  • A tag cannot be dropped if it is assigned to a masking policy.

  • A masking policy cannot be dropped if it is assigned to a tag.

For more information on managing masking policies and tags, see:

Parent Database & Schema

Enabling the 2022_07 behavior change bundle prevents DROP and REPLACE operations on the database and schema when:

  • The tag and masking policy are in the same schema.

  • The table or view is in a different schema.

  • The protected column in the table or view exists in a different schema than the schema that contains the masking policy and tag.

Enabling the bundle affects these four commands:

  • DROP DATABASE

  • DROP SCHEMA

  • CREATE OR REPLACE DATABASE

  • CREATE OR REPLACE SCHEMA

For details, see the announcement.

Conditional Arguments

A conditional masking policy can be assigned to a tag. After assigning the tag to a column, the conditional arguments map to a column in the table by name if the data type of the argument matches with the data type of the column.

A query will fail if a conditional masking policy is assigned to a column in the following cases:

  • The table does not have a column with the same name as a conditional argument of the policy.

  • The table has a column that matches the name of the conditional argument of the policy but the data type doesn’t match.

For more information on these errors, see Troubleshooting Tag-based Masking Policies (in this topic).

Tag Lineage

The tag with the masking policy can be assigned to all base table objects, or the tag can be assigned to a column in a base table. columns. When the tag-based masking policy is assigned to a base table, the columns are protected by the policy provided that the column data type matches the data type in the masking policy signature.

Since the masking policy protects the base table columns, view columns that are derived from the underlying base table columns are also protected, based on the current limitations, considerations, and behaviors regarding masking policies with tables and views.

Using Tag-Based Masking Policies

The subsections below provide the following information:

  • A common procedure to use with tag-based masking policies for data protection and validation.

  • Prerequisite steps to complete before implementing tag-based masking policies.

  • A list of common assumptions for the examples.

  • Representative examples of tag-based masking policy usage, including the usage of the following system functions:

Tag & Policy Discovery

The Information Schema table function POLICY_REFERENCES and the Account Usage POLICY_REFERENCES view can help determine whether a masking policy and a tag reference each other by looking at the following columns:

  • TAG_DATABASE

  • TAG_SCHEMA

  • TAG_NAME

  • POLICY_STATUS

The POLICY_STATUS column can have four possible values:

ACTIVE

Specifies that the column (i.e. REF_COLUMN_NAME) is only associated with a single policy by a tag.

MULTIPLE_MASKING_POLICY_ASSIGNED_TO_THE_COLUMN

Specifies that multiple masking policies are assigned to the same column.

COLUMN_IS_MISSING_FOR_SECONDARY_ARG

Specifies that the policy (i.e. POLICY_NAME) is a conditional masking policy and the table (i.e. REF_ENTITY_NAME) does not have a column with the same name.

COLUMN_DATATYPE_MISMATCH_FOR_SECONDARY_ARG

Specifies that the policy is a conditional masking policy and the table has a column with the same name but a different data type than the data type in the masking policy signature.

For details on related error messages with the possible values in the POLICY_STATUS column, see Troubleshooting Tag-Based Masking Policies (in this topic).

Data Protection and Validation Steps

Generally, Snowflake recommends the following approach when using tag-based masking policies:

  1. Create any tags that are needed for tag-based masking policies.

  2. Create one masking policy for each data type based on the table columns that you intend to protect with the tag-based masking policies.

  3. Assign the masking policies to the tag.

  4. Assign the tag with the masking policies to the table column directly or to the table.

  5. Check the Information Schema to verify the tag-based policy is assigned to the columns.

  6. Query the data to verify the tag-based masking policy protects the data as intended.

Prerequisite Steps

  1. Identify the existing tags and their string values in your Snowflake account.

    • Query the Account Usage TAG REFERENCES view to obtain all tags and their assigned string values.

    • Optionally:

      • Query the Account Usage TAGS view (i.e. the tag catalog) to obtain a list of tags to ensure that duplicate tag naming does not occur later while using tag-based masking policies.

      • Compare the outputs from the TAG_REFERENCES and TAGS queries to determine if there are any unassigned tags that can be used later.

      • Create any tags that will be needed later using the CREATE TAG command. Otherwise, create tags as needed.

  2. Identify the existing policies and their definitions in your Snowflake account.

    • Execute the SHOW MASKING POLICIES command to obtain a list of existing masking policies.

    • Decide whether these policies, in their current form, can be assigned to tags. If necessary, execute the DESCRIBE MASKING POLICY command to obtain the policy definition. Otherwise, plan to create new policies to assign to tags.

  3. Determine how to protect the column data with the masking policy in terms of whether the policy conditions should evaluate the tag string value that is set on the table column.

Common Assumptions With the Examples

The examples make the following assumptions:

  • The prerequisite steps were completed.

  • The tag_admin custom role has the following privileges:

    • The schema-level CREATE TAG privilege.

    • The global APPLY TAG privilege.

    For more information, see tag privileges.

  • The masking_admin custom role has the following privileges:

    • The schema-level CREATE MASKING POLICY privilege.

    • The USAGE privilege on the governance database and the governance.masking_policies schema.

    • The global APPLY MASKING POLICY privilege to assign masking policies to tags (see Privilege in this topic).

    • The global APPLY TAG privilege, to assign the tag (with the masking policies) to objects.

    For details, see, tag privileges.

  • The row_access_admin custom role has the following privileges:

    • The schema-level CREATE ROW ACCESS POLICY privilege.

    • The USAGE privilege on the governance database and the governance.row_access_policies schema.

    • The global APPLY ROW ACCESS POLICY privilege.

    For more information, see row access policy privileges.

  • The accounting_admin custom role has the following privileges:

    • The USAGE privilege on the finance database and the finance.accounting schema.

    • The SELECT privilege on tables in the finance.accounting schema.

  • The analyst custom role has the following privileges:

    • The USAGE privilege on the finance database and on the finance.accounting schema.

    • The SELECT privilege on the finance.accounting.name_number table.

  • The custom roles described above are granted to the appropriate users.

    For details, see Configuring Access Control.

Example 1: Protect Column Data Based on the Masking Policy Directly Assigned to the Tag

This example assigns two masking policies to a tag and then assigns the same tag to a table. The result is that the masking policies protect all table columns whose data types match the data types in the policies.

The following steps create a tag-based masking policy to mask accounting data. For example, consider the table named finance.accounting.name_number, which has two columns, ACCOUNT_NAME and ACCOUNT_NUMBER. The data types in these columns are STRING and NUMBER, respectively.

---------------+----------------+
  ACCOUNT_NAME | ACCOUNT_NUMBER |
---------------+----------------+
  ACME         | 1000           |
---------------+----------------+

Create a tag-based masking policy to protect the ACCOUNT_NAME and ACCOUNT_NUMBER columns as follows:

  1. Create a tag named accounting in the schema named governance.tags.

    use role tag_admin;
    use schema governance.tags;
    create or replace tag accounting;
    
  2. Create different masking policies to protect the ACCOUNT_NAME and ACCOUNT_NUMBER columns. In each of these policies, only the ACCOUNTING_ADMIN custom role can view the raw data.

    Account name policy:

    use role masking_admin;
    use schema governance.masking_policies;
    
    create or replace masking policy account_name_mask as (val string) returns string ->
      case
        when current_role() in ('ACCOUNTING_ADMIN') then val
        else '***MASKED***'
      end;
    

    Account number policy:

    create or replace masking policy account_number_mask as (val number) returns number ->
      case
        when current_role() in ('ACCOUNTING_ADMIN') then val
        else -1
      end;
    
  3. Assign both masking policies to the accounting tag. Note that both policies can be assigned to the tag in a single statement.

    alter tag governance.tags.accounting set
      masking policy account_name_mask,
      masking policy account_number_mask;
    
  4. Assign the accounting tag to the finance.accounting.name_number table.

    alter table finance.accounting.name_number set tag governance.tags.accounting = 'tag-based policies';
    
  5. Verify the ACCOUNT_NAME and ACCOUNT_NUMBER table columns are protected by the tag-based masking policy by calling the Information Schema POLICY_REFERENCES table function.

    For each protected column, the row in the query result should specify the appropriate values for the column name, policy name, and tag name:

    use role masking_admin;
    select *
    from table (governance.information_schema.POLICY_REFERENCES(
      REF_ENTITY_DOMAIN => 'TABLE',
      REF_ENTITY_NAME => 'governance.accounting.name_number' )
    );
    

    Returns (note the updated columns):

    -------------+------------------+---------------------+----------------+-------------------+-----------------+-----------------+-------------------+-----------------+----------------------+--------------+------------+------------+---------------+
      POLICY_DB  | POLICY_SCHEMA    | POLICY_NAME         | POLICY_KIND    | REF_DATABASE_NAME | REF_SCHEMA_NAME | REF_ENTITY_NAME | REF_ENTITY_DOMAIN | REF_COLUMN_NAME | REF_ARG_COLUMN_NAMES | TAG_DATABASE | TAG_SCHEMA | TAG_NAME   | POLICY_STATUS |
    -------------+------------------+---------------------+----------------+-------------------+-----------------+-----------------+-------------------+-----------------+----------------------+--------------+------------+------------+---------------+
      GOVERNANCE | MASKING_POLICIES | ACCOUNT_NAME_MASK   | MASKING_POLICY | FINANCE           | ACCOUNTING      | NAME_NUMBER     | TABLE             | ACCOUNT_NAME    | NULL                 | GOVERNANCE   | TAGS       | ACCOUNTING | ACTIVE        |
      GOVERNANCE | MASKING_POLICIES | ACCOUNT_NUMBER_MASK | MASKING_POLICY | FINANCE           | ACCOUNTING      | NAME_NUMBER     | TABLE             | ACCOUNT_NUMBER  | NULL                 | GOVERNANCE   | TAGS       | ACCOUNTING | ACTIVE        |
    -------------+------------------+---------------------+----------------+-------------------+-----------------+-----------------+-------------------+-----------------+----------------------+--------------+------------+------------+---------------+
    
  6. Query the table columns with authorized and unauthorized roles to ensure Snowflake returns the correct query result.

    Authorized:

    use role accounting_admin;
    select * from finance.accounting.name_number;
    

    Returns:

    ---------------+----------------+
      ACCOUNT_NAME | ACCOUNT_NUMBER |
    ---------------+----------------+
      ACME         | 1000           |
    ---------------+----------------+
    

    Unauthorized:

    use role analyst;
    select* from finance.accounting.name_number;
    

    Returns:

    ---------------+----------------+
      ACCOUNT_NAME | ACCOUNT_NUMBER |
    ---------------+----------------+
      ***MASKED*** | -1             |
    ---------------+----------------+
    

Example 2: Protect Column Data Based on the Column Tag String Value

This example uses a tag-based masking policy to determine whether data should be masked based upon the tag string value of the tag assigned to a column. The masking policy dynamically evaluates the tag string value by calling the SYSTEM$GET_TAG_ON_CURRENT_COLUMN function into the masking policy conditions and writing the masking policy conditions to match the tag string value.

The following steps create a tag-based masking policy to mask accounting data. For brevity, the table columns have two data types, STRING and NUMBER, respectively. For example, a table named finance.accounting.name_number:

---------------+----------------+
  ACCOUNT_NAME | ACCOUNT_NUMBER |
---------------+----------------+
  ACME         | 1000           |
---------------+----------------+

Create a tag-based masking policy to protect the ACCOUNT_NAME and ACCOUNT_NUMBER columns as follows:

  1. Create a tag named accounting_col_string in the schema named governance.tags.

    use role tag_admin;
    use schema governance.tags;
    create tag accounting_col_string;
    
  2. Create different masking policies to protect the ACCOUNT_NAME and ACCOUNT_NUMBER columns. In each of these policies, the raw data is visible only when the current tag string value on the column is set to 'visible'.

    Account name policy:

    use role masking_admin;
    use schema governance.masking_policies;
    
    create masking policy account_name_mask_tag_string as (val string) returns string ->
      case
        when system$get_tag_on_current_column('tags.accounting_col_string') = 'visible' then val
        else '***MASKED***'
      end;
    

    Account number policy:

    create masking policy account_number_mask_tag_string as (val number) returns number ->
      case
        when system$get_tag_on_current_column('tags.accounting_col_string') = 'visible' then val
        else -1
      end;
    

    Note

    These policies use the schema_name.tag_name object name format in the function argument because the tags schema and the masking_policies schema both exist in the governance database. Alternatively, you can also use the fully-qualified name for the tag in the function argument.

    Snowflake returns an error at query runtime on a column protected by a tag-based masking policy if the system function argument in the policy conditions contains a tag name that is not sufficiently qualified. For example, the argument uses the tag name as accounting_col_string only, without specifying the schema name or the database name.

    For more information, see Object Name Resolution.

  3. Assign both masking policies to the accounting_col_string tag. Note that both policies can be assigned to the tag in a single statement.

    alter tag accounting_col_string set
      masking policy account_name_mask_tag_string,
      masking policy account_number_mask_tag_string;
    
  4. Assign the accounting_col_string tag to each table column. In this example, the tag string value on the ACCOUNT_NAME column is 'visible', however, the tag string value on the ACCOUNT_NUMBER column is set to 'protect'.

    alter table finance.accounting.name_number modify column
      account_name set tag governance.tags.accounting_col_string = 'visible',
      account_number set tag governance.tags.accounting_col_string = 'protect';
    
  5. Verify the ACCOUNT_NAME and ACCOUNT_NUMBER table columns are protected by the tag-based masking policy by calling the Information Schema POLICY_REFERENCES table function.

    For each protected column, the row in the query result should specify the appropriate values for the column name, policy name, and tag name.

    select *
    from table (governance.information_schema.policy_references(
      ref_entity_domain => 'TABLE',
      ref_entity_name => 'finance.accounting.name_number' )
    );
    

    Returns (note the updated columns):

    ------------+----------------+--------------------------------+----------------+-------------------+-----------------+-----------------+-------------------+-----------------+----------------------+--------------+------------+-----------------------+---------------+
     POLICY_DB  | POLICY_SCHEMA  | POLICY_NAME                    |  POLICY_KIND   | REF_DATABASE_NAME | REF_SCHEMA_NAME | REF_ENTITY_NAME | REF_ENTITY_DOMAIN | REF_COLUMN_NAME | REF_ARG_COLUMN_NAMES | TAG_DATABASE | TAG_SCHEMA | TAG_NAME              | POLICY_STATUS |
    ------------+----------------+--------------------------------+----------------+-------------------+-----------------+-----------------+-------------------+-----------------+----------------------+--------------+------------+-----------------------+---------------+
     GOVERNANCE | MASKING_POLICY | ACCOUNT_NAME_MASK_TAG_STRING   | MASKING_POLICY | FINANCE           | ACCOUNTING      | NAME_NUMBER     | TABLE             | ACCOUNT_NAME    | NULL                 | GOVERNANCE   | TAGS       | ACCOUNTING_COL_STRING | ACTIVE        |
     GOVERNANCE | MASKING_POLICY | ACCOUNT_NUMBER_MASK_TAG_STRING | MASKING_POLICY | FINANCE           | ACCOUNTING      | NAME_NUMBER     | TABLE             | ACCOUNT_NUMBER  | NULL                 | GOVERNANCE   | TAGS       | ACCOUNTING_COL_STRING | ACTIVE        |
    ------------+----------------+--------------------------------+----------------+-------------------+-----------------+-----------------+-------------------+-----------------+----------------------+--------------+------------+-----------------------+---------------+
    
  6. Query the table columns to ensure Snowflake returns the correct query result, which should only mask the value in the ACCOUNT_NUMBER column.

    use role accounting_admin;
    select * from finance.accounting.name_number;
    

    Returns:

    ---------------+----------------+
      ACCOUNT_NAME | ACCOUNT_NUMBER |
    ---------------+----------------+
      ACME         | -1             |
    ---------------+----------------+
    

Example 3: Protect a Table Based on the Table Tag String Value

This example uses a row access policy to protect a table based on a tag string value assigned to the table and a tag-based masking policy to protect the columns in the table. For simplicity, this example uses one tag, assigns the masking policies to the tag, and assigns the tag to the table. The columns in the table will automatically have the same tag and its string value because of tag lineage.

The row access policy dynamically evaluates the tag string value of the tag assigned to the table by calling the SYSTEM$GET_TAG_ON_CURRENT_TABLE function in the row access policy conditions. As with the previous example, the masking policy conditions call the SYSTEM$GET_TAG_ON_CURRENT_COLUMN function to evaluate the tag string value on the table columns.

Important

Note that you cannot assign a row access policy to the tag.

Instead, assign the row access policy to the table directly using an ALTER TABLE command.

The table finance.accounting.name_number has two columns, which have the data types STRING and NUMBER:

---------------+----------------+
  ACCOUNT_NAME | ACCOUNT_NUMBER |
---------------+----------------+
  ACME         | 1000           |
---------------+----------------+

Protect the table and its columns with a row access policy and a tag-based masking policy as follows:

  1. Create a row access policy that calls the SYSTEM$GET_TAG_ON_CURRENT_TABLE function in the policy conditions:

    use role row_access_admin;
    use schema governance.row_access_policies;
    
    create row access policy rap_tag_value as (account_number number)
    returns boolean ->
    system$get_tag_on_current_table('tags.accounting_row_string') = 'visible'
    AND
    'accounting_admin' = current_role();
    

    The policy specifies that Snowflake return rows in the query result only when the accounting_row_string tag is assigned to the table with a string value as 'visible' and the role executing the query on the table or its columns is the accounting_admin custom role.

    Snowflake does not return rows in the query result if any of the following are true:

    • The accounting_row_string tag is not set on the table.

    • The accounting_row_string tag is set on the table but with a different string value.

    • The role executing a query on the table or its columns is not the accounting_admin custom role.

  2. Assign the row access policy to the table:

    alter table finance.accounting.name_number
      add row access policy rap_tag_value on (account_number);
    

    Note that at this point in the procedure, a query on the table should not return any rows in the query result for any role in Snowflake because the accounting_row_string tag is not assigned to the table. So, the expected result from a query on the table should be:

    use role accounting_admin;
    select * from finance.accounting.name_number;
    

    Returns:

    ---------------+----------------+
      ACCOUNT_NAME | ACCOUNT_NUMBER |
    ---------------+----------------+
                   |                |
    ---------------+----------------+
    

    By choosing to assign the row access policy to the table before assigning the tag-based masking policy to the table, all of the table data is protected as early as possible.

  3. Create a tag named accounting_row_string in the schema named governance.tags.

    use role tag_admin;
    use schema governance.tags;
    create tag accounting_row_string;
    
  4. Create different masking policies to protect the ACCOUNT_NAME and ACCOUNT_NUMBER columns. In each of these policies, the raw data is visible only when the current tag string value on the column is set to 'visible'.

    Account name policy:

    use role masking_admin;
    use schema governance.masking_policies;
    
    create masking policy account_name_mask as (val string) returns string ->
      case
        when system$get_tag_on_current_column('tags.accounting_row_string') = 'visible' then val
        else '***MASKED***'
      end;
    

    Account number policy:

    create masking policy account_number_mask as (val number) returns number ->
      case
        when system$get_tag_on_current_column('tags.accounting_row_string') = 'visible' then val
        else -1
      end;
    
  5. Assign both masking policies to the accounting_row_string tag. Note that both policies can be assigned to the tag in a single statement.

    alter tag governance.tags.accounting_row_string set
      masking policy account_name_mask,
      masking policy account_number_mask;
    
  6. Assign the accounting_row_string tag to the table with the tag string value 'visible':

    alter table finance.accounting.name_number set tag governance.tags.accounting_row_string = 'visible';
    

    Now that the tag is assigned to the table with a string value of visible, only the accounting_admin custom role can view the table data; a query made by a user with any other role should result in no rows being returned as shown earlier in this example. In other words, the conditions of the row access policy now evaluate to true.

    Similarly, the table columns also have the tag string value of visible tag because the columns inherit the tag and its string value through tag lineage. The result is that when a user with the accounting_admin custom role queries the table, Snowflake returns unmasked data:

    use role accounting_admin;
    select * from finance.accounting.name_number;
    

    Returns:

    ---------------+----------------+
      ACCOUNT_NAME | ACCOUNT_NUMBER |
    ---------------+----------------+
      ACME         | 1000           |
    ---------------+----------------+
    
  7. To mask data in either column, update the tag string value for the column directly. For example, to mask the data in the ACCOUNT_NUMBER column:

    alter table finance.accounting.name_number modify column
      account_number set tag governance.tags.accounting_row_string = 'protect';
    

    Now when a user with the accounting_admin custom role queries the table or the ACCOUNT_NUMBER column, Snowflake returns masked data:

    use role accounting_admin;
    select * from finance.accounting.name_number;
    

    Returns:

    ---------------+----------------+
      ACCOUNT_NAME | ACCOUNT_NUMBER |
    ---------------+----------------+
      ACME         | -1             |
    ---------------+----------------+
    

Troubleshooting Tag-based Masking Policies

The following table lists some error messages that Snowflake can return when using tag-based masking policies:

Behavior

Error message

Troubleshooting action

Cannot query a column: too many policies.

SQL execution error: Column <col_name> is mapped to multiple masking policies by tags.Please contact your local administrator to fix the issue.

A given column can be protected by only one masking policy.

Call the POLICY_REFERENCES function to identify the masking policies set on the column. Modify the tags by unsetting the masking policy from the tag so that the column is protected by only one policy.

Cannot query a column: no conditional column.

SQL execution error: Column <col_name> is mapped to a masking policy where the table doesnt have acolumn for a secondary argument name of the policy.Please contact your local administrator to fix the issue.

A masking policy that uses conditional arguments must have all of the specified columns in the same table or view. Do one of the following to protect the column data:

  • Assign a different policy to the column directly.

  • Modify the tag by assigning a different masking policy to the tag.

Column data is not masked due to a data type mismatch for the column and the policy.

SQL execution error: Column <col_name> is mapped to a masking policy where the table has a column with different data-type for a secondary argument name.Please contact your local administrator to fix the issue.

To mask the column data, the data type for the column and the data type in the masking policy signature must match. Do one of the following to protect the column data:

  • Assign a different policy to the column directly.

  • Assign a masking policy to the tag, making sure that the data type for the policy and the data type for the column match.

Back to top