Snowflake Masking, External Tokenization and Row Access Policies

1.Column-level security with Dynamic Data Masking

Column-level Security in Snowflake has two features: Dynamic Data Masking and External Tokenization. Both use the same kind of object, the masking policy. A masking policy lives in a schema, so a database and schema must exist before you can apply one to a column. It has one data type, one or more conditions, and one or more masking functions. You write it once and can apply it to any number of columns with a matching data type, across databases and schemas.

At query time Snowflake rewrites the query. The policy expression replaces the column everywhere it appears, including projections, joins, WHERE, ORDER BY and GROUP BY. Depending on the conditions, a user sees the plain-text value, a partial mask or a full mask. The input and output data types must match. A policy that takes a timestamp cannot return a string such as ***MASKED***.

CREATE OR REPLACE MASKING POLICY email_mask AS (val string) RETURNS string ->
  CASE
    WHEN CURRENT_ROLE() IN ('ANALYST') THEN val
    ELSE '*********'
  END;

Snowflake recommends that a security or privacy officer, not the object owner, administer masking. In the docs this is a custom MASKING_ADMIN role. Three privileges divide the work. By default, object owners can neither unset a masking policy nor see the masked column data. That separation of duties is the point of the feature. It can even keep ACCOUNTADMIN or SECURITYADMIN from viewing data they don't need. To change a policy, run ALTER MASKING POLICY. You don't need to reapply it, and the columns stay protected while you edit it.

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

Sources123

2.External Tokenization and when to choose it

External Tokenization uses the same policy structure, with one difference: the policy body calls an external function. A third-party tokenization provider tokenizes the data before it is loaded into Snowflake, so the table stores undecipherable tokens rather than plain text. At query time the external function makes a REST API call to the provider. The provider evaluates its own tokenization policy and returns tokenized or detokenized data. Roles must be mapped in both Snowflake and the provider for this to work.

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;

To decide between them, ask what the business needs to be true about the stored data. With Dynamic Data Masking, the table holds plain text and the policy hides it at query time. With External Tokenization, the data is tokenized before loading, so users never see the real value even on a column that has no policy. The cost is more moving parts: an API integration, an external function, and a provider that must stay in sync with your identity provider and roles. Integrating a tokenization provider with Snowflake External Tokenization requires Enterprise Edition or higher. The privileges are mostly the same as for masking. In addition, the policy owner needs USAGE on the external function, and the function's owner needs USAGE on the API integration it references. A tokenization policy can also be assigned to a tag.

Sources43

3.Configuring a row access policy

Masking controls what a user sees in a column. A row access policy controls which rows come back at all. It is a schema-level object whose expression returns a BOOLEAN. It applies to SELECT and to the rows chosen by UPDATE, DELETE and MERGE. It does not stop inserts, and it does not stop visible rows from being updated or deleted. The simplest policy only checks the role, which costs almost nothing in performance.

CREATE OR REPLACE ROW ACCESS POLICY rap_it
AS (empl_id varchar) RETURNS BOOLEAN ->
  'it_admin' = current_role()
;

For more detailed entitlements, the policy can look up a mapping table, for example sales managers mapped to regions, inside an EXISTS subquery. Keep that mapping table in the same database as the protected table. Mapping-table lookups are slower than simple CASE logic, and replacing them with a memoizable function improves performance. If role hierarchy matters, use IS_ROLE_IN_SESSION or IS_DATABASE_ROLE_IN_SESSION instead of CURRENT_ROLE. Snowflake evaluates the expression with the privileges of the policy owner, so the querying user doesn't need access to the mapping table.

You attach the policy with ADD ROW ACCESS POLICY … ON (column). This works in CREATE TABLE and CREATE VIEW, or later with ALTER TABLE and ALTER VIEW. The listed columns are bound to the policy's signature arguments. One policy can protect many tables and views at once.

ALTER TABLE t1 ADD ROW ACCESS POLICY rap_t1 ON (empl_id);

Sources5

4.What changes when a row access policy is attached

Once a policy is attached, every row of the object is protected by it, and queries run differently. Snowflake wraps the object in a dynamic secure view. EXPLAIN shows this as the operation DynamicSecureView on the object "<object_name> (+ RowAccessPolicy)", but it doesn't show the policy's name or expression. Some shortcuts stop working. Without a policy, SELECT COUNT(*) returns in milliseconds from metadata. With a policy, Snowflake has to scan the table to count the rows the current context can see. Bound columns are scanned even when the query doesn't reference them, so a policy with fewer arguments performs better.

Attaching a policy also affects other features:

- With masking: the row access policy is evaluated first, and the same column can't appear in both a row access policy signature and a masking policy signature. - Nested policies: the table's policy runs first, then each view's policy from left to right. - Copies: CREATE TABLE … LIKE gives an empty table with no policy. CTAS gives filtered rows with no policy. A clone maps to the same policies as the source. - Materialized views: you can't add a policy to a table that already has a materialized view built on it, and you can't build a materialized view on a protected table. A dynamic table is the suggested alternative. - Time Travel: supported on tables and views with a row access policy. Snowflake evaluates the policy's mapping tables at the time of the query, so Time Travel does not affect the mapping table.

Sources5

5.Row access policies compared with secure views

Before policies existed, the usual way to restrict rows or columns was a secure view per audience. Snowflake's column-security docs list two problems with that approach. First, secure views multiply: you need many views, each with its own BI dashboards. Second, secure views have no segregation of duties, because whoever owns the view decides what it exposes. The row access policy docs make the matching case for row filtering. A central data administrator, not the object owner, decides which objects to protect. The policy restricts even the object owner. You write it once and apply it across databases and schemas. If it uses a mapping table, you can change entitlements by editing the table, without touching the policy.

Under the hood, a row access policy *is* applied as a dynamic secure view that Snowflake builds at runtime. So the choice isn't between two filtering engines. It is a choice between hand-maintaining many view objects and maintaining one governed policy. ALTER ROW ACCESS POLICY updates the expression in place, and the table stays protected while it changes.

Sources35

6.Tagging use cases: tracking, classifying and protecting data

A tag is a Snowflake object that you set on a database, schema, table, view or column, with a string value such as governance.tags.pii = 'sensitive'. A tag on its own hides nothing. It labels data so that other governance features can find it, track it and act on it. The sources describe these use cases:

- Tracking sensitive data for compliance: after tracing where sensitive data moves with access history, set tags so that the stages, tables and columns holding it can be tracked for compliance requirements. The Account Usage TAG_REFERENCES view shows which tags are set on a table or its columns. - Classification: sensitive data classification labels each sensitive column with the system tags SNOWFLAKE.CORE.SEMANTIC_CATEGORY (for example NAME) and SNOWFLAKE.CORE.PRIVACY_CATEGORY (IDENTIFIER, QUASI_IDENTIFIER or SENSITIVE). You can map your own tags to these, and then track sensitive data across your account by tracking the tags. - Protecting data at scale with tag-based policies: assign a masking policy to a tag, then set the tag. Set on a database or schema, tag inheritance protects the matching columns of every table and view in it, including tables and views added later. Combined with classification, columns are masked automatically when classification applies the tag. A masking policy set directly on a column still takes precedence over one that comes from a tag. - Following data downstream: a tag can be configured to propagate on object dependency, on data movement, or both. The tag-based policy then protects those downstream objects as well. - Certifying trusted data: the built-in system tag SNOWFLAKE.CORE.CERTIFICATION_STATUS = 'CERTIFIED' marks a table or view as the authoritative source. - Auditing: ACCESS_HISTORY records tag updates, such as setting a tag or changing its value, in the object_modified_by_ddl column.

ALTER TAG my_tag SET MASKING POLICY my_masking_policy;

ALTER TABLE my_table SET TAG my_tag = 'protected';

Sources678910

Exam traps

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

1. 1.When a table has both a masking policy and a row access policy, the masking policy is applied first.Why is that wrong?

   The row access policy is always evaluated first. Also, the same column cannot appear in both policy signatures.

   Covered in What changes when a row access policy is attached

2. 2.A table created with CTAS from a protected table inherits the row access policy.Why is that wrong?

   CTAS copies only the rows the creator could see. The new table has no policy.

   Covered in What changes when a row access policy is attached

3. 3.External Tokenization with a provider integration works on any Snowflake edition, like basic external functions.Why is that wrong?

   Integrating a tokenization provider with Snowflake External Tokenization requires Enterprise Edition or higher.

   Covered in External Tokenization and when to choose it

4. 4.If a column has its own masking policy and also gets a tag that carries a masking policy, the tag-based policy wins.Why is that wrong?

   The masking policy set directly on the column takes precedence over the one assigned to the tag.

   Covered in Tagging use cases: tracking, classifying and protecting data