Tri-Secret Secure CMKs and Dynamic Data Masking policies

1.Tri-Secret Secure: owning the customer-managed key

Tri-Secret Secure adds a key you control to Snowflake's standard encryption. A Snowflake-maintained key and a customer-managed key (CMK), which you create in the key management service (KMS) of the cloud provider hosting your account, together form a composite master key. That key acts as the account master key: it wraps the keys below it in the hierarchy, such as table master keys, and those keys derive the file keys that encrypt the data. The composite key never encrypts raw data itself.

You also take on responsibility. If the CMK is revoked, Snowflake can no longer decrypt your data. So treat the KMS key's availability as part of your data's availability. Two more constraints: if TSS is enabled, or will be, you must turn on Dedicated Storage Mode before you create hybrid tables. And for AWS external key stores, the only products Snowflake tests and supports are Thales HSMs and Thales CipherTrust Cloud Key Manager.

You do the self-registration. Snowflake Support does the activation: once the key is verified, you ask Support to enable Tri-Secret Secure for that specific account. Then read the output of SYSTEM$GET_CMK_INFO. Just after activation it reports that the CMK "is being activated", which means rekeying hasn't finished. When activation completes, it reports "is activated". To rotate or replace the CMK, repeat the same registration steps with the new key. During the change, GET_CMK_INFO reports that the key "is being rekeyed".

Sources1

2.Designing Dynamic Data Masking policies

Data protection moves from key ownership to column-level security. Dynamic Data Masking uses masking policies to mask plain-text column data in tables and views when a query runs. A masking policy is a schema-level object made of a single data type, one or more conditions, and one or more masking functions. Write it once and you can apply it to any column with a matching data type.

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

The body can return a value for each kind of user:

- NULL or a static string for unauthorized users. - A partial mask built with REGEXP_REPLACE. - A SHA2 hash. Hashes can collide, so use them with caution. - The output of a UDF. The role creating the policy needs USAGE on the UDF. - For VARIANT data, an OBJECT_INSERT that overwrites a single field.

The conditions can use CURRENT_ACCOUNT to unmask only in production, or SYS_CONTEXT('SNOWFLAKE$CURRENT', 'IS_AGENT_ACTIVATED') to mask whenever an AI agent is active. For entitlements kept in a table, put the subquery inside EXISTS. For a lookup on a mapping table, a MEMOIZABLE function can cache the result.

CASE WHEN EXISTS (SELECT role FROM <db>.<schema>.entitlement WHERE mask_method='unmask' AND role = current_role()) THEN val ELSE '********' END;

Because the rewrite covers joins and filters too, masking only one side of a comparison is a known anti-pattern: the results are not what the user intended. Masking also enforces segregation of duties by default. Object owners can't unset a masking policy, and they can't see the data in a masked column.

Sources234

3.Managing the masking policy lifecycle and watching its impact

A policy's life runs from create, to set on columns, to update, and finally to unset and drop. Updating is the easy part. Get the current body with GET_DDL or DESCRIBE MASKING POLICY, then change it with ALTER MASKING POLICY. You don't need to unset the policy first, so the columns stay protected while the definition changes. To swap a column's policy for a different one in one statement, use ALTER TABLE ... MODIFY COLUMN with the FORCE keyword.

The common errors each point to a specific fix:

- A column can carry only one masking policy. - A policy that is still attached cannot be dropped. Unset it with ALTER TABLE or ALTER VIEW ... MODIFY COLUMN first. - A policy cannot go on a virtual column or a materialized view column. Apply it to the source table instead. - ALTER ... IF EXISTS can return "Statement executed successfully" without updating anything. Remove IF EXISTS and run it again.

Every body change alters what someone can see, so check the impact deliberately:

- The Account Usage MASKING_POLICIES view catalogs all policies. - The POLICY_REFERENCES view or Information Schema table function lists every object a policy is set on. That is the reach of your change. - The Query Profile shows which policies a specific query used. The QUERY_HISTORY view does not. - Before changing a policy, call POLICY_CONTEXT to simulate a query under chosen context values, such as activated roles. - After changing it, query the column as an authorized role and then as a role like PUBLIC, and confirm the results differ as intended.

Sources4526

Exam traps

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

1. 1.The Account Usage QUERY_HISTORY view shows which masking policies a query triggered.Why is that wrong?

   QUERY_HISTORY holds only the SQL text. The policy names used by a query are in the Query Profile.

   Covered in Managing the masking policy lifecycle and watching its impact

2. 2.If ALTER MASKING POLICY IF EXISTS returns success, the policy was updated.Why is that wrong?

   It can report success and change nothing. The documented fix is to remove IF EXISTS and run the statement again.

   Covered in Managing the masking policy lifecycle and watching its impact

3. 3.Snowflake can still decrypt Tri-Secret Secure data if the customer revokes the CMK.Why is that wrong?

   The CMK is part of the composite master key. Revoking it makes the data undecryptable by Snowflake.

   Covered in Tri-Secret Secure: owning the customer-managed key