Column-Level Security: Masking Policies, External Tokenization and Projection Policies

1.Two features, one policy object

In Snowflake, column-level security means attaching a masking policy to a column in a table or view. Two features use this mechanism. Dynamic Data Masking stores plain text and masks it when a query runs. External Tokenization works the other way round: a third-party provider tokenizes the data before it is loaded, and Snowflake detokenizes it at query time for callers who are authorized.

Both features use the same policy structure, with one difference. A tokenization policy must call an external function in its body. That function makes a REST call to the tokenization provider, which applies its own tokenization policy and returns either the token or the clear value. Because of this, roles must be mapped in both Snowflake and the provider.

-- Dynamic Data Masking

CREATE MASKING POLICY employee_ssn_mask AS (val string) RETURNS string ->
  CASE
    WHEN CURRENT_ROLE() IN ('PAYROLL') THEN val
    ELSE '******'
  END;

-- External Tokenization

  CREATE MASKING POLICY employee_ssn_detokenize AS (val string) RETURNS string ->
  CASE
    WHEN CURRENT_ROLE() IN ('PAYROLL') THEN ssn_unprotect(VAL)
    ELSE val -- sees tokenized data
  END;

The way the rewrite works is important. Snowflake does not mask only the output. It replaces the column with the policy expression wherever the column appears in the query. For an unauthorized role, joins, filters, groupings and sorts all run on the masked value. A join on a masked column can therefore return no matches for one role and the correct matches for another.

Secure views could also hide columns, so why use policies? Secure views need many views and dashboards to manage, and they do not separate the people who protect the data from the people who own it. Masking policies avoid both problems.

Sources12

2.Masking policies with RBAC: separating policy admins from owners

Masking policies work alongside role-based access control. The usual pattern is a custom role, such as MASKING_ADMIN, held by a security or privacy officer. The officer decides which columns are protected. The table owner does not. By default, object owners cannot unset masking policies and cannot see the masked data. A policy can also stop ACCOUNTADMIN and SECURITYADMIN from viewing data they do not need.

Three privileges control this:

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;

The per-policy APPLY privilege is what makes a decentralized or hybrid model possible. Policy administrators write the policies, then let trusted object owners attach them, acting as data stewards. With hundreds of teams, the central team does not have to apply every policy itself.

Inside the policy body, RBAC appears as conditions:

- CURRENT_ROLE() checks the active role. - IS_ROLE_IN_SESSION takes the role hierarchy into account. - INVOKER_ROLE is used for views. - INVOKER_SHARE is used for shares.

A policy can also look up a custom entitlement table. If it uses a subquery, wrap it in EXISTS. A memoizable function can cache that lookup.

Sources231

3.Managing masking policies with DDL, and the best practices

These commands manage masking policies:

- CREATE MASKING POLICY - ALTER MASKING POLICY - DROP MASKING POLICY - SHOW MASKING POLICIES - DESCRIBE MASKING POLICY

To attach a policy, use ALTER TABLE or ALTER VIEW with MODIFY COLUMN ... SET MASKING POLICY. You can also attach it when you create the table or view. To change a policy's logic, read the current definition with GET_DDL or DESCRIBE, then run ALTER MASKING POLICY. You do not need to unset the policy first, so the column stays protected while you edit it.

ALTER TABLE IF EXISTS user_info MODIFY COLUMN email  ?  MASKING POLICY email_mask;

Most masking-policy questions on the exam come down to a few lifecycle rules. A column can have only one masking policy at a time. A policy's input and output data types must match, so to mask a timestamp you return a made-up timestamp rather than a string. If a policy is still attached anywhere, you cannot drop it. Unset it first.

Best practices that the documentation calls out:

- Write a policy once and apply it to every matching column. - Be careful with hash-based masks such as SHA2, because hashes can collide. - In conditional policies that read other columns, keep the number of column arguments small, because every argument is evaluated at runtime.

For auditing, the Account Usage MASKING_POLICIES view lists every policy, and POLICY_REFERENCES shows where each one is set. To find which policy affected a particular query, look in the Query Profile. QUERY_HISTORY does not record policy names.

Sources132

4.Projection policies: use a column without showing it

A masking policy changes the value that users see. A projection policy decides whether a column can appear in the final output at all. A column with a projection policy is called projection constrained. If the caller's active role does not meet the policy condition, the column comes back as NULL in the outer query. It also cannot be inserted into another table or passed to an external function or stored procedure. A column can have only one projection policy.

The point of a projection policy is that the column can still be used. It works in inner queries, in WHERE filters and as a join key, so analysts can work with the real values without seeing them. This is more flexible than working with a masked or tokenized value, and it is designed for sharing data with partners.

That flexibility comes with limits:

- A projection policy does not stop anyone from targeting an individual with a WHERE filter. - It cannot be tag-based, and it cannot go on a virtual column. - Its body cannot reference a column that has a masking policy or a table that has a row access policy. - A determined attacker can still extract values with carefully built queries.

Use projection policies with partners you already trust. If a column must never leak, remove it from the data or use differential privacy.

How it fits with other policies. A projection policy does not exclude a masking policy or a row access policy. The same column can carry a projection policy and a masking policy, and the table can carry a row access policy. The restriction is only on the projection policy body, which cannot reference a masked column or a row-access-protected table.

When a projection policy has to be swapped for another, the FORCE keyword on the column clause of ALTER TABLE replaces the masking or projection policy currently set on the column in a single statement. Projection policies also interact with aggregation policies. They are enforced after aggregation policies, whereas masking policies are enforced before them. If a column is protected by a projection policy, a query against an aggregation-constrained table cannot use that column as an argument of the COUNT function.

Sources4567

Exam traps

Each one states something that sounds right. Open it to see what is actually true.

1. 1.The role that owns a table can always see its unmasked data and can remove a masking policy from it.Why is that wrong?

   Masking policies separate duties. By default, object owners can neither unset the policy nor see the protected column data.

   Covered in Masking policies with RBAC: separating policy admins from owners

2. 2.DROP MASKING POLICY removes the policy from every column it is attached to.Why is that wrong?

   The drop fails while any column still uses the policy. Unset it with ALTER TABLE/VIEW ... MODIFY COLUMN first, then drop it.

   Covered in Managing masking policies with DDL, and the best practices

3. 3.A projection policy stops users from learning anything about the column's values.Why is that wrong?

   Hidden columns still work in inner queries and WHERE clauses, so filters and joins can reveal information.

   Covered in Projection policies: use a column without showing it