Tag-based masking policies¶

Privilege¶

There are different privilege requirements depending on whether you choose to set the tag-based masking policy on a database, schema, or table.

With tag-based masking on a database or schema, the current role or a role in the current role hierarchy must inherit the privileges as shown in either of the following two options.

Option 1:

The role must have both the global APPLY MASKING POLICY and the global APPLY TAG privileges. For example, grant these privileges to the data_engineer custom role:

USE ROLE ACCOUNTADMIN;

GRANT APPLY MASKING POLICY ON ACCOUNT TO ROLE data_engineer;

GRANT APPLY TAG ON ACCOUNT TO ROLE data_engineer;

Copy

This is the most centralized approach to protect columns with a tag-based masking policy in a schema or database.

Option 2:

A schema owner (i.e. a role with the OWNERSHIP privilege on the schema) can have the global APPLY MASKING POLICY privilege and the APPLY privilege on the tag. For example, if the tag is named governance.tags.schema_mask and the custom role that owns the schema is schema_owner:

USE ROLE ACCOUNTADMIN;

GRANT APPLY MASKING POLICY ON ACCOUNT TO ROLE schema_owner;

GRANT APPLY ON TAG governance.tags.schema_mask TO ROLE schema_owner;

Copy

This approach provides more flexibility by delegating column protection to schema owners.

With tag-based masking on tables and views, a role with the global APPLY MASKING POLICY privilege can assign and replace a masking policy on 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;

Copy

Assign a masking policy to a tag¶

Assigning a tag-based masking policy on a schema or database follows the same procedure as setting a tag-based masking policy on a table:

1. Create a tag using the CREATE TAG command.

2. Create a masking policy using the CREATE MASKING POLICY command.

   You can optionally use the SYSTEM$GET_TAG_ON_CURRENT_COLUMN and SYSTEM$GET_TAG_ON_CURRENT_TABLE system functions in the masking policy conditions.

3. Set the masking policy on the tag using an ALTER TAG command.

4. Set the tag on the object based on how you want to protect your data using one of the following commands:

   • ALTER DATABASE

   • ALTER SCHEMA

   • ALTER TABLE

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:

• Managing Column-level Security

• Managing tags

Replace a masking policy on a tag¶

After setting a masking policy on a tag, there are two different pathways to replace the masking policy on the tag with a different masking policy. The ALTER TAG statement must specify the masking policy name as shown in the following options.

Option 1:

Unset the policy from a tag in one statement and then set a new policy on the tag in a different statement:

ALTER TAG security UNSET MASKING POLICY ssn_mask;

ALTER TAG security SET MASKING POLICY ssn_mask_2;

Copy

Option 2:

Use the FORCE keyword to replace the policy in a single statement.

Note that using the FORCE keyword replaces the policy when a policy of the same data type is already set on the tag.

ALTER TAG security SET MASKING POLICY ssn_mask_2 FORCE;

Copy

The option you select in the Privilege section and the order of operations in the Assign a masking policy to a tag section can impact tag management if you need to replace or unset a tag on a database or schema.

If a schema owner sets a tag on a schema and then a different role sets a masking policy on the same tag, the schema owner cannot unset the tag from the schema unless the schema owner is granted the global APPLY MASKING POLICY privilege. Snowflake fails the ALTER SCHEMA … UNSET TAG operation for the schema owner. This scenario ensures that column data that is protected by a tag-based masking policy stays protected. To avoid this scenario, use option 1 in the Privilege section.

Important

Exercise caution when replacing a masking policy on a tag.

Depending on the timing of the replacement and the query on the column, choosing to replace the policy in two separate statements could lead to a data leak because the column data is unprotected in the time interval between the UNSET and SET operations.

However, if the policy conditions are different in the replacement policy, specifying the FORCE keyword could lead to a lack of access because (previously) users could access data and the replacement no longer allows access.

Prior to replacing a policy, consult your internal data administrators to coordinate the best approach to protect data with tag-based masking policies and replace masking policies as needed.

Update a tag value¶

If a schema owner (i.e. sch_role) sets a tag on a schema and then a different role sets a masking policy on the same tag (i.e. masking_admin_role), the schema owner cannot change the tag value. Snowflake fails the ALTER SCHEMA … SET TAG operation for the schema owner.

To change the tag value:

1. Using the masking_admin_role, unset the masking policy from the tag.

2. Using the sch_role, modify the tag value.

3. Reassign the masking policy to the tag using the masking_admin_role.

Parent database and schema¶

You cannot perform 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.

These four commands refer to DROP and replace operations on the database and schema:

• DROP DATABASE

• DROP SCHEMA

• CREATE OR REPLACE DATABASE

• CREATE OR REPLACE SCHEMA

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 Troubleshoot 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. 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.

Data Sharing¶

Tag-based masking policies that are set on a shared schema or shared database in the provider account are enforced in the consumer account. This scenario ensures protected data that is shared remains protected, even if a consumer creates a new database from the share.

Additionally, note the following:

• Tag lineage is preserved in the consumer account.

  When the provider sets a tag-based masking policy on their database and shares that database, Snowflake references the shared provider database in the consumer account in terms of the database that contains the tag.

• Snowflake does not honor tag lineage with shared objects when the tags and tag-based masking policies originate in the consumer account.

  Tags and tag-based masking policies from the consumer account are not enforced on any shared objects.

Snowsight¶

You can monitor and assign tag-based masking policies in Snowsight. For details, see:

• Monitor tags with Snowsight

• Monitor masking policies with Snowsight

Tag and 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, refer to Troubleshoot 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;
   

   Copy

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;
   

   Copy

   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;
   

   Copy

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;
   

   Copy

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

   Copy

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

   Copy

   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;
   

   Copy

   Returns:

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

   Unauthorized:

   USE ROLE analyst;
   SELECT * FROM finance.accounting.name_number;
   

   Copy

   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;
   

   Copy

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;
   

   Copy

   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;
   

   Copy

   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;
   

   Copy

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

   Copy

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

   Copy

   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;
   

   Copy

   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.

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

   Copy

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

   Copy

   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;
   

   Copy

   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;
   

   Copy

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;
   

   Copy

   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;
   

   Copy

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;
   

   Copy

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

   Copy

   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;
   

   Copy

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

   Copy

   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;
   

   Copy

   Returns:

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