CertSafari
    Snowflake SnowPro Advanced: Security Engineer (SEA-C01)· Lessons

    Domain 2 · Lesson 5/21

    Snowflake data security features: Tri-Secret Secure, masking, row access and privacy policies

    Implement data security features.

    29 min read
    4.29% of exam
    19 sources
    Published 20 Oct 2026
    Docs as of 18 Oct 2026

    What you will be able to do

    • Register, verify and rotate a customer-managed key for Tri-Secret Secure, and predict the effect of revoking it
    • Write a masking policy with context functions, attach it to a column, and fix common lifecycle errors
    • Attach a conditional masking policy and use an external function for detokenization
    • Protect columns at scale with tag-based masking and know when a direct assignment wins
    • Build a row access policy around a mapping table
    • Configure projection, aggregation and differential privacy policies and read privacy budget consumption

    Key concept

    Query-time policy enforcement — Snowflake policies do not change stored data. Each time a query runs, Snowflake rewrites it so the policy expression is applied, which means the role, account and other context of that query decide what the user sees.

    1.Tri-Secret Secure and the customer-managed key

    Tri-Secret Secure adds a key to your account that Snowflake cannot use unless you allow it. It uses a dual-key model. A Snowflake-maintained key is combined with a customer-managed key (CMK), which you create in the key management service (KMS) of the cloud platform that hosts your account. The two together form a composite master key, and that key acts as the account master key by wrapping every key below it. It wraps table master keys, and those are used to derive the file keys that encrypt the data. The composite key itself never encrypts raw data.

    Revocation affects the whole account because the CMK sits at the top of the hierarchy. Every file key depends on a table master key, every table master key is wrapped by the composite key, and the composite key needs your CMK. Revoking the CMK therefore cuts off tables loaded years ago just as much as this morning's loads. Keeping the key safe is your responsibility. That is the reason to use the feature: when you withdraw the key grant in your KMS, Snowflake can no longer decrypt the data.

    You register the CMK yourself by calling system functions, and Snowflake Support turns on Tri-Secret Secure. The steps are the same on every cloud, but the function arguments differ by platform. On Azure, for example, you must pass the tenant_id value to SYSTEM$GET_CMK_CONFIG. No statement you run activates Tri-Secret Secure. After you verify connectivity, you contact Support and name the account to enable.

    Self-registration steps, in order
    StepWhat it does
    Create the CMK in the cloud KMSThe customer-managed half of the composite key
    SYSTEM$REGISTER_CMK_INFORegisters the CMK with the KMS integration
    SYSTEM$GET_CMK_INFOShows the registered CMK and its registration and activation status
    SYSTEM$GET_CMK_CONFIGGenerates the policy information that lets Snowflake access the CMK
    Apply the output in the cloud KMSAuthorizes Snowflake to use the CMK
    SYSTEM$VERIFY_CMK_INFOConfirms connectivity between the Snowflake account and the CMK
    Contact Snowflake SupportSupport enables Tri-Secret Secure for the named account

    To rotate the key, you repeat the same registration steps with a new CMK. You can call SYSTEM$GET_CMK_INFO at any time to follow progress. Straight after activation it reports that the key "is being activated", which means rekeying is not finished. Later it reports "is activated". During a key change it reports "is being rekeyed". Snowflake does the rekeying and reports it through this status, so you do not rekey tables yourself. If you keep the key outside AWS, Tri-Secret Secure can use an AWS external key store. Snowflake officially tests and supports only Thales HSM and Thales CipherTrust Cloud Key Manager for this.

    Checkpoint 1 of 11· Put it in order

    Your security team is self-registering a customer-managed key so the account can use Tri-Secret Secure. Put these steps in order.

    1. 1.Call SYSTEM$VERIFY_CMK_INFO to confirm connectivity between the Snowflake account and the CMK
    2. 2.Call SYSTEM$REGISTER_CMK_INFO to register the CMK with the KMS integration
    3. 3.Call SYSTEM$GET_CMK_CONFIG to generate the policy information that lets Snowflake access the CMK
    4. 4.Apply the SYSTEM$GET_CMK_CONFIG output in the cloud KMS to authorize Snowflake to use the CMK
    5. 5.Contact Snowflake Support and name the account to enable for Tri-Secret Secure
    6. 6.Create the CMK in the key management service of the cloud platform that hosts the Snowflake account

    Checkpoint 2 of 11· Exam question

    During a ransomware tabletop drill, a security engineer revokes Snowflake's access to the customer-managed AWS KMS key behind the account's Tri-Secret Secure configuration and leaves it revoked. What happens to the account?

    Sources1

    2.Designing and attaching a masking policy

    Dynamic Data Masking is Snowflake's column-level security feature. A masking policy is a schema-level object, written as a SQL expression that takes the column value and returns either that value or a substitute. Snowflake applies the expression at query time everywhere the column appears in the query: in projections, join predicates, WHERE clauses, ORDER BY and GROUP BY. A role that can't see the value therefore can't get around the mask by filtering or joining on the column either.

    A role-based policy: ANALYST sees the email, every other role sees a fixed masksql
    CREATE OR REPLACE MASKING POLICY email_mask AS (val string) RETURNS string ->
      CASE
        WHEN CURRENT_ROLE() IN ('ANALYST') THEN val
        ELSE '*********'
      END;

    You attach a policy one column at a time with ALTER TABLE user_info MODIFY COLUMN email SET MASKING POLICY email_mask;. For a view you use ALTER VIEW with the same clause. You can also set the policy in CREATE TABLE or CREATE VIEW. The policy's conditions come from context functions and conditional expressions:

    • CURRENT_ROLE() gives role-based masking. CURRENT_ACCOUNT() lets a production account see real values while development and test accounts see masks.
    • INVOKER_ROLE returns the name of the executing role. For a policy reached through a view, that is the view owner's role, whereas CURRENT_ROLE() is the role of the user running the query. INVOKER_SHARE returns the name of the share that directly accessed the table or view, so a provider can give each share its own masking behavior.
    • A custom entitlement table works when the subquery is wrapped in EXISTS, which is required for any subquery in a policy body.
    • SYS_CONTEXT('SNOWFLAKE$CURRENT', 'IS_AGENT_ACTIVATED') masks values whenever an AI agent is active, even for a role that would otherwise see them.

    The masked branch must return the same data type as the input. A policy that takes a timestamp can't return the string ***MASKED***. Instead, return a fabricated timestamp such as 0001-01-01, or NULL. Hashing with SHA2 is allowed but can produce collisions.

    A mapping table is an ordinary table that records which roles may see the data, for example auth_role_t with a ROLE column and an IS_AUTHORIZED flag. A policy that queries it on every evaluation is slow. A MEMOIZABLE function wraps the lookup and caches its result: you create it with CREATE FUNCTION is_role_authorized(arg1 VARCHAR) RETURNS BOOLEAN MEMOIZABLE AS $$ ... $$, and the policy body calls it like any other function, as in WHEN is_role_authorized(CURRENT_ROLE()) THEN val ELSE NULL.

    Checkpoint 3 of 11· Check yourself

    A privacy officer is writing a masking policy for a TIMESTAMP_NTZ column called last_login. The SUPPORT role must see real values and every other role must see a mask. Which policy body will compile and do this?

    Sources23456

    3.Privileges and the masking policy lifecycle

    Masking policies separate who owns the data from who decides how it is protected. A security or privacy officer chooses which columns to protect. By default, an object owner can't unset a masking policy and can't see the masked data. A policy can even stop ACCOUNTADMIN or SECURITYADMIN from seeing values they have no reason to see. Three privileges implement this split.

    Masking policy privileges
    PrivilegeGranted onControls
    CREATE MASKING POLICYSchemaWho can create masking policies
    APPLY MASKING POLICYAccountWho can set or unset masking policies on any column; ACCOUNTADMIN holds it by default
    APPLYMasking policyLets a policy owner allow object owners to set or unset this one policy (decentralized model)

    Changing a policy is cheap. Removing one is not. You can edit a policy's body with ALTER MASKING POLICY, and every column that uses it picks up the change without being reattached. To see the current definition before editing, use DESCRIBE MASKING POLICY or GET_DDL. A DROP MASKING POLICY fails while the policy is still attached anywhere, so you first unset it with ALTER TABLE … MODIFY COLUMN. A column can have only one masking policy, so attaching a second one fails until you remove the first.

    One error gives no warning at all. IF EXISTS turns a missing object into a silent no-op: when the name doesn't resolve, for example because of a typo or a different database or schema in context, Snowflake returns "Statement executed successfully" and nothing is attached or updated. Remove IF EXISTS and run the statement again so a wrong name raises an error.

    Auditing works on two levels. POLICY_REFERENCES lists every column a policy is attached to; it exists as an Account Usage view and as an Information Schema table function. MASKING_POLICIES lists the policies themselves. To find which policies a particular query used, open its Query Profile. The Account Usage QUERY_HISTORY view does not record masking policy names.

    A policy edit changes what users see at once, because it takes effect when the next query that uses the policy runs. To judge the impact on visibility, list the affected columns with POLICY_REFERENCES before the edit, then call the POLICY_CONTEXT function to simulate a query with chosen context values, such as activated roles, and compare the result for each role before and after the change.

    Checkpoint 4 of 11· Check yourself

    A masking policy called ssn_mask protects columns in three tables. A privacy officer who owns the policy runs DROP MASKING POLICY ssn_mask to retire it. What happens?

    Sources4276

    4.Conditional masking and External Tokenization

    A conditional masking policy decides how to mask one column by reading another column in the same row. Its signature starts with the masked column's value, followed by one or more extra arguments, for example (val STRING, region STRING). When you attach it, Snowflake matches each extra argument to a column of that name in the table. If the table has no such column, POLICY_REFERENCES shows the status COLUMN_IS_MISSING_FOR_SECONDARY_ARG. If the column exists but has a different data type, it shows COLUMN_DATATYPE_MISMATCH_FOR_SECONDARY_ARG.

    You still attach a conditional policy to the one column it masks, with ALTER TABLE … MODIFY COLUMN email SET MASKING POLICY. The attach statement then passes the columns the policy reads, in the order of its signature: first the masked column, then the conditional column. The condition itself, such as "region is EU", belongs in the policy body and not in the attach statement. Attaching the same policy separately to the conditional column would mask that column too, which is not what you want. Also, a column that is masked can't serve as the conditional column of another masking policy.

    External Tokenization uses a masking policy to call an external function. With tokenization, the stored value is a token issued by a third-party provider. The masking function that would normally hide the value is instead an external function that sends the token to the provider and gets back the real value. This works through the API integration described in External functions and API integrations. The policy is written the other way round from Dynamic Data Masking: authorized roles get the result of the detokenizing function, and every other role gets the token unchanged.

    An External Tokenization policy: ANALYST gets the detokenized email, every other role sees the tokensql
    create or replace masking policy email_de_token as (val string) returns string ->
      case
        when current_role() in ('ANALYST') then de_email(val)
        else val
      end;

    The policy depends on several pieces outside it. de_email() is an external function, and it depends on an API integration. At query runtime Snowflake calls the provider through a REST API, and the provider evaluates a tokenization policy that was created outside Snowflake. Role mapping therefore has to exist on both sides, or the wrong audience gets detokenized values. The policy owner needs USAGE on the external function, and the function's owner needs USAGE on the API integration it references. Integrating a tokenization provider with External Tokenization requires Enterprise Edition or higher. You attach the policy exactly like any other masking policy, for example with ALTER TABLE user_info MODIFY COLUMN email SET MASKING POLICY email_de_token;.

    Checkpoint 5 of 11· Check yourself

    A conditional masking policy has the signature (val STRING, region STRING). It is attached to the email column of a customers table whose region column is stored as NUMBER. When you audit the attachment, which POLICY_STATUS does POLICY_REFERENCES report?

    Checkpoint 6 of 11· Exam question

    Support agents should see the full email value only when the row's region column equals 'EU'; everyone else must see a masked value. A masking policy mask_email(val STRING, region STRING) already exists. Which statement attaches it correctly?

    Sources849

    5.Tag-based masking policies

    An object tag is a named label with a string value that you attach to Snowflake objects. You create one with CREATE TAG accounting;, put it on a table with ALTER TABLE finance.accounting.name_number SET TAG governance.tags.accounting = 'tag-based policies';, and put it on a single column with ALTER TABLE … MODIFY COLUMN … SET TAG. Tags are inherited: a tag set on a table, schema or database reaches the objects beneath it. System tags are the tags Snowflake itself defines, such as SNOWFLAKE.CORE.SEMANTIC_CATEGORY.

    A tag-based masking policy attaches masking policies to a tag instead of to individual columns. You run ALTER TAG … SET MASKING POLICY, and from then on every column carrying that tag is masked, as long as the column's data type matches the policy's data type. A tag can hold one policy per data type, so the usual pattern is one generic policy each for STRING, NUMBER and TIMESTAMP_LTZ, all set on the same tag.

    Two policies of different data types attached to one tag in a single statementsql
    ALTER TAG governance.tags.accounting SET MASKING POLICY account_name_mask, MASKING POLICY account_number_mask;

    Where you put the tag sets how much it covers. On a table, the tag covers every column in the table, including columns added later, much like a future grant. On a schema or database, tag inheritance covers every table and view inside, including ones created later. Tagging at the schema level is therefore a safety net that protects new data until someone chooses a more specific policy. Setting the policy on the tag needs the global APPLY MASKING POLICY privilege. Applying the tag to a schema or database also needs APPLY TAG, or ownership of the schema together with APPLY on the tag.

    On top of the usual masking limits, tag-based masking has some of its own:

    • You can't attach a masking policy to a system tag.
    • While a policy is set on a tag, you can't drop the policy, the tag, or the schema and database that contain them.
    • A materialized view can't be created on a table protected by a tag-based policy. If you add the tag later, an existing materialized view becomes invalid.
    • A table with a column protected by a tag-based policy can't be used as a mapping table.
    • A tag owner needs APPLY MASKING POLICY to unset a policy from the tag.

    Checkpoint 7 of 11· Fill the gap

    A data engineer wants every STRING and NUMBER column carrying the accounting tag to be masked automatically, without attaching policies to individual columns. Which keyword completes the statement?

    ALTER  ?  governance.tags.accounting SET MASKING POLICY account_name_mask, MASKING POLICY account_number_mask;

    Sources8

    6.Row access policies and mapping tables

    A row access policy decides which rows a query returns. It is an expression that returns BOOLEAN for each row, and only rows where the result is true are returned. The signature names the column the policy reads and that column's data type. Most real policies combine a role check with a lookup in a mapping table, which links each role to the values it is allowed to see.

    Executives see every row; sales managers see only regions listed for them in the mapping tablesql
    CREATE OR REPLACE ROW ACCESS POLICY security.sales_policy AS (sales_region varchar) RETURNS BOOLEAN -> 'sales_executive_role' = CURRENT_ROLE() OR EXISTS ( SELECT 1 FROM salesmanagerregions WHERE sales_manager = CURRENT_ROLE() AND region = sales_region ) ;

    The schema owner role gets CREATE ROW ACCESS POLICY automatically and can grant it to other roles. The mapping table is an ordinary table, so maintaining entitlements means updating rows, not rewriting the policy. Two restrictions shape the design. A column can appear in a masking policy signature or in a row access policy signature, but not in both at once. And a table with a column protected by a tag-based masking policy can't be used as a mapping table.

    Binding the policy to the region column of the sales tablesql
    ALTER TABLE sales ADD ROW ACCESS POLICY security.sales_policy ON (region);

    A new table or view can take the policy at creation with WITH ROW ACCESS POLICY sales_policy ON (region). Binding needs the global APPLY ROW ACCESS POLICY privilege, and because future grants on row access policies are not supported, you grant that privilege to a custom role instead. A role holding APPLY on one policy can add or drop that policy on a table without broader rights. Policies run with the policy owner's rights, so give ownership to a custom role rather than SECURITYADMIN.

    Row access is evaluated before masking. When a table has a row access policy and masking policies, Snowflake filters the rows first, so masking only acts on rows that survive. Aggregation policies follow the same logic: a row excluded by the row access policy is not counted when the aggregate is calculated.

    When enforcement looks wrong, check these in turn:

    • Which policies are attached. POLICY_REFERENCES shows the policy on a given table, or every object a policy protects.
    • How the role is tested. CURRENT_ROLE() compares one role name; when role hierarchy matters, use IS_ROLE_IN_SESSION, which needs Enterprise Edition or higher.
    • Where the mapping table lives. It belongs in the same database as the protected table, and an external table can't serve as a mapping table.
    • Whether the signature needs changing. You can't change it on an existing policy, so drop the policy and create a new one.
    • Whether results differ by role. Counts and maximums can't use stored statistics under a row access policy, so they are slower and reflect only the rows the role may see.
    • Attaching a row access policy to a table already protected by other row access or masking policies may cause errors.

    Checkpoint 8 of 11· Check yourself

    On a sales table, the row access policy security.sales_policy takes sales_region in its signature to filter rows by manager. Governance now also wants sales_region itself masked for non-executive roles, using a masking policy whose signature takes that same column. What is the outcome?

    Sources1086111213

    7.Projection policies

    A projection policy lets a query use a column without returning it. The column can still be used in a WHERE clause or a join, but it can't appear in the result. This suits identifiers that partners need for matching but must never take away. The policy takes no arguments and must return the result of PROJECTION_CONSTRAINT.

    Projection policy syntaxsql
    CREATE [ OR REPLACE ] PROJECTION POLICY [ IF NOT EXISTS ] <name>
      AS () RETURNS PROJECTION_CONSTRAINT -> <body>
      [ COMMENT = '<string_literal>' ]

    The body can use CASE logic, for example on CURRENT_ROLE(), but each branch ends in PROJECTION_CONSTRAINT(ALLOW=>{TRUE|FALSE}, ENFORCEMENT=><enforcement_style>). With ALLOW => TRUE, the column can be returned. With ALLOW => FALSE, ENFORCEMENT decides what happens, and it is optional. FAIL is the default and rejects any query that includes the protected column in its outermost SELECT. NULLIFY lets the query run and returns NULL in every row of the protected column. Choose FAIL when a partner should learn immediately that the column is off limits, and NULLIFY when existing queries and dashboards must keep running. ALLOW => FALSE only affects columns that would appear in the final result, so subqueries and predicates can still use the column. You attach the policy to a column with ALTER TABLE … ALTER COLUMN or ALTER VIEW.

    ANALYST can project the column; everyone else gets NULL instead of an errorsql
    CREATE OR REPLACE PROJECTION POLICY project_analyst_only AS () RETURNS PROJECTION_CONSTRAINT -> CASE WHEN CURRENT_ROLE() = 'ANALYST' THEN PROJECTION_CONSTRAINT(ALLOW => true) ELSE PROJECTION_CONSTRAINT(ALLOW => false, ENFORCEMENT => 'NULLIFY') END;

    Checkpoint 9 of 11· Check yourself

    Two retailers share a customers table so a partner can join its own orders on customer_id. The partner's role must never get customer_id back in its query results. Which control meets both needs?

    Sources14

    8.Aggregation policies, differential privacy and privacy budgets

    An aggregation policy makes a table usable only through aggregates. Its body returns AGGREGATION_CONSTRAINT(MIN_GROUP_SIZE => n): a query against the table must aggregate the data, and each group in the result must be built from at least n rows. A group that falls short is not an error. Its rows are folded into a remainder group whose grouping value is NULL. A role is exempt when the body returns NO_AGGREGATION_CONSTRAINT() for it, and that role can query without restriction. A table or view under such a policy is called aggregation-constrained.

    Create a policy that requires groups of at least 3 rows, then assign it to a tablesql
    CREATE AGGREGATION POLICY my_policy AS () RETURNS AGGREGATION_CONSTRAINT -> AGGREGATION_CONSTRAINT(MIN_GROUP_SIZE => 3);
    A conditional policy: the ADMIN role is exempt, every other query needs groups of five or more rowssql
    CREATE AGGREGATION POLICY my_policy AS () RETURNS AGGREGATION_CONSTRAINT -> CASE WHEN CURRENT_ROLE() = 'ADMIN' THEN NO_AGGREGATION_CONSTRAINT() ELSE AGGREGATION_CONSTRAINT(MIN_GROUP_SIZE => 5) END;

    You assign the policy with ALTER TABLE t1 SET AGGREGATION POLICY my_policy;. The usual setup has a custom role such as agg_policy_admin hold CREATE AGGREGATION POLICY on the schema and APPLY AGGREGATION POLICY on the account. Adding an ENTITY KEY clause when you assign the policy changes what MIN_GROUP_SIZE counts: unique entities, such as households, instead of rows. Among other policies, masking is enforced before aggregation, so aggregates work on masked data, and projection is enforced after it. For a role that isn't exempt, several query patterns are rejected because they could leak individual values.

    Query patterns rejected against an aggregation-constrained table
    PatternRule
    External function callNot allowed unless another part of the query has already aggregated the results properly
    UDF logging or metricsA column of a constrained table can't be logged
    CAST in the SELECT listProhibited; use TRY_CAST, TRY_TO_NUMBER or another TRY_ conversion
    PIVOTCan't be used on a column of a constrained table

    Differential privacy limits what the combined effect of many queries can reveal. A privacy policy returns a named privacy budget for each user, and the budget keeps a running tally of cumulative privacy loss: every aggregate in an analyst's query adds to it. When a query would push the tally past the budget limit, the query fails until the budget refreshes. Users who need unrestricted, noise-free access get NO_PRIVACY_POLICY() from the policy body; the body must return that function rather than NULL.

    A privacy policy that always returns a privacy budget named analystssql
    CREATE PRIVACY POLICY my_priv_policy AS ( ) RETURNS PRIVACY_BUDGET -> PRIVACY_BUDGET(BUDGET_NAME=> 'analysts');

    You attach it with ALTER TABLE t1 ADD PRIVACY POLICY my_priv_policy ENTITY KEY (email);. A table or view can have only one privacy policy, and you grant SELECT on the data only after the policy is attached, since earlier grants would give analysts full access.

    Three parameters in the budget body tune it. BUDGET_LIMIT caps cumulative privacy loss and so controls how many queries an analyst can run. MAX_BUDGET_PER_AGGREGATE caps what each aggregate may spend; SELECT COUNT(*), AVG(a) has two aggregates. BUDGET_WINDOW sets how often the tally resets to 0, weekly by default. Changing any of them with ALTER PRIVACY POLICY has a catch: every parameter you leave out reverts to its default.

    Setting BUDGET_LIMIT and MAX_BUDGET_PER_AGGREGATE; BUDGET_WINDOW is not listed, so it reverts to its defaultsql
    ALTER PRIVACY POLICY users_policy SET BODY ->
      PRIVACY_BUDGET(BUDGET_NAME=>'analysts',
      BUDGET_LIMIT=>300,
      MAX_BUDGET_PER_AGGREGATE=>0.1);

    Besides the automatic reset at the end of each window, the RESET_PRIVACY_BUDGET stored procedure zeroes the tally for one account's analysts. The reset takes effect the next time a query incurs privacy loss, so a budget viewed straight after the call may still show the old value.

    There are two ways to monitor budgets, and they differ in freshness. The Account Usage PRIVACY_BUDGETS view lists every budget in the account and can be filtered by policy and budget name, but like other Account Usage views it has latency. The SNOWFLAKE.DATA_PRIVACY.CUMULATIVE_PRIVACY_LOSSES table function returns real-time losses for a single policy, and you must pass that policy's fully qualified name. Use the function when deciding whether a budget is about to run out.

    Real-time privacy loss for every budget in one privacy policysql
    SELECT * FROM TABLE(SNOWFLAKE.DATA_PRIVACY.CUMULATIVE_PRIVACY_LOSSES( 'my_policy_db.my_policy_schema.my_policy_privacy'));

    Checkpoint 10 of 11· Check yourself

    A privacy policy currently sets BUDGET_LIMIT, MAX_BUDGET_PER_AGGREGATE and BUDGET_WINDOW => 'daily' for the budget analysts. An administrator runs ALTER PRIVACY POLICY users_policy SET BODY -> PRIVACY_BUDGET(BUDGET_NAME=>'analysts', BUDGET_LIMIT=>300). What is the state afterward?

    Checkpoint 11 of 11· Exam question

    A provider protects a table with a differential privacy policy using a privacy budget. Which statements about this behaviour are correct? Select all that apply.(Select 2)

    0 of 2 selected

    Sources1315161718195

    Practise it for real

    Set up a masking policy administrator role, protect an email column, and check what different roles see

    1. 1.As USERADMIN, run CREATE ROLE masking_admin; then as SECURITYADMIN grant it CREATE MASKING POLICY on your schema and APPLY MASKING POLICY on ACCOUNT.

      Why: This keeps protection decisions with a security officer rather than the table owner.

      You should see: SHOW GRANTS TO ROLE masking_admin lists both privileges.

    2. 2.As masking_admin, create the email_mask policy that returns val for ANALYST and '*********' for every other role.

      Why: CURRENT_ROLE() makes what a user sees depend on the role running the query.

      You should see: SHOW MASKING POLICIES lists email_mask.

    3. 3.Run ALTER TABLE user_info MODIFY COLUMN email SET MASKING POLICY email_mask;

      Why: Policies are attached column by column. Leaving out IF EXISTS means a wrong table name fails with an error instead of attaching nothing.

      You should see: POLICY_REFERENCES shows email_mask on user_info.email.

    4. 4.Run SELECT email FROM user_info once as ANALYST and once as PUBLIC.

      Why: The same query gives different results depending on the role that runs it.

      You should see: ANALYST sees plain-text addresses; PUBLIC sees the full mask.

    Stuck? Get a nudge

    If the CREATE statement fails with insufficient privileges, check that the CREATE MASKING POLICY grant was made on the same schema you are working in.

    Sources

    (19)

    Every claim above is drawn from one of these pages, quoted as it was written on the date shown.

    1. 1.
      “combines a Snowflake-maintained key and a customer-managed key (CMK)”
      ↩︎ Tri-Secret Secure and the customer-managed key
      “The composite master key is never used to encrypt raw data.”
      ↩︎ Tri-Secret Secure and the customer-managed key
      “If Microsoft Azure hosts your Snowflake account, you must pass the tenant_id value into the function.”
      ↩︎ Tri-Secret Secure and the customer-managed key
      “Use the same steps to register a new CMK as the steps that you followed to register your initial CMK.”
      ↩︎ Tri-Secret Secure and the customer-managed key
      “returns a message that contains ...is being rekeyed”
      ↩︎ Tri-Secret Secure and the customer-managed key
      “Snowflake officially tests and supports only Thales Hardware Security Modules (HSM) and Thales CipherTrust Cloud Key Manager (CCKM) data encryption products.”
      ↩︎ Tri-Secret Secure and the customer-managed key
      “Snowflake Support enables your Snowflake account to use Tri-Secret Secure based on the CMK that you register.”
      ↩︎ Exam trap 1
      “If the customer-managed key (CMK) in the composite master key hierarchy is revoked, your data can no longer be decrypted by Snowflake.”
      ↩︎ Prediction
      “Generate information for the cloud provider. Apply the KMS policy. Confirm the connectivity between your Snowflake account and your CMK. Contact Snowflake Support”
      ↩︎ Checkpoint
    2. 2.
      “In Snowflake, masking policies are schema-level objects”
      ↩︎ Designing and attaching a masking policy
      “can prohibit privileged users with the ACCOUNTADMIN or SECURITYADMIN role from unnecessarily viewing data”
      ↩︎ Privileges and the masking policy lifecycle
      “Easily change masking policy content without having to reapply the masking policy to thousands of columns.”
      ↩︎ Privileges and the masking policy lifecycle
      “A column cannot be attached to multiple masking policies.”
      ↩︎ Privileges and the masking policy lifecycle
      “Remove IF EXISTS from the ALTER statement and try again.”
      ↩︎ Privileges and the masking policy lifecycle
      “Updating a policy using IF EXISTS returns a successful result but does not update the policy.”
      ↩︎ Privileges and the masking policy lifecycle
      “Masking policy names are not included in the QUERY_HISTORY view.”
      ↩︎ Privileges and the masking policy lifecycle
      “statement to UNSET the policy first, then try the DROP statement again.”
      ↩︎ Exam trap 5
      “Policy <policy_name> cannot be dropped/replaced as it is associated with one or more entities.”
      ↩︎ Checkpoint
    3. 3.
      “The column rewrite occurs at every place where the column specified in the masking policy appears in the query”
      ↩︎ Designing and attaching a masking policy
      “This example uses a memoizable function to cache the result of a query on the mapping table”
      ↩︎ Designing and attaching a masking policy
      “Always use EXISTS when including a subquery in the masking policy body.”
      ↩︎ Designing and attaching a masking policy
      “Using a hashing function in a masking policy may result in collisions”
      ↩︎ Designing and attaching a masking policy
      “Snowflake dynamically rewrites the query applying the masking policy SQL expression to the column.”
      ↩︎ Key concept
      “the input and output data types must match.”
      ↩︎ Exam trap 4
      “the input and output data types must match”
      ↩︎ Checkpoint
      “The column rewrite occurs at every place where the column specified in the masking policy appears in the query (e.g. projections, join predicate”
      ↩︎ Checkpoint
    4. 4.
      “You can use the context functions INVOKER_ROLE and INVOKER_SHARE for use with views and shares, respectively.”
      ↩︎ Designing and attaching a masking policy
      “Object owners cannot view column data in which a masking policy applies.”
      ↩︎ Privileges and the masking policy lifecycle
      “Writing external functions (for de-tokenization using an external tokenization provider)”
      ↩︎ Conditional masking and External Tokenization
      “Snowflake uses the external function to make a REST API call to the tokenization provider”
      ↩︎ Conditional masking and External Tokenization
      “role mapping must exist in Snowflake and the tokenization provider”
      ↩︎ Conditional masking and External Tokenization
    5. 5.
      “Returns the name of the share that directly accessed the table or view where the INVOKER_SHARE function is invoked.”
      ↩︎ Designing and attaching a masking policy
      “A table or view can have only one privacy policy assigned to it.”
      ↩︎ Aggregation policies, differential privacy and privacy budgets
    6. 6.
      “The base table or view policy can apply to the view owner (i.e. INVOKER_ROLE) or the query operator role (i.e. CURRENT_ROLE).”
      ↩︎ Designing and attaching a masking policy
      “Call the POLICY_CONTEXT function to simulate a query on a column that is protected by a masking policy”
      ↩︎ Privileges and the masking policy lifecycle
      “With a new table or view, apply the policy to a table with a CREATE TABLE statement”
      ↩︎ Row access policies and mapping tables
      “grant the APPLY ROW ACCESS POLICY privilege to a custom role”
      ↩︎ Row access policies and mapping tables
      “When a database object has both a row access policy and one or more masking policies, Snowflake evaluates the row access policy first.”
      ↩︎ Row access policies and mapping tables
      “the returned statistics are only based on what is permissible to access”
      ↩︎ Row access policies and mapping tables
      “Attaching row access policies to tables that are protected by other row access policies or masking policies may cause errors.”
      ↩︎ Row access policies and mapping tables
    7. 7.
      “Any changes made to the policy rules go into effect when the next SQL query that uses the masking policy runs.”
      ↩︎ Privileges and the masking policy lifecycle
    8. 8.
      “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.”
      ↩︎ Conditional masking and External Tokenization
      “A masked column cannot be used as a conditional column in a masking policy.”
      ↩︎ Conditional masking and External Tokenization
      “The tag can support one masking policy for each data type that Snowflake supports.”
      ↩︎ Tag-based masking policies
      “the columns in all newly added tables and views are automatically protected when the column data type matches the masking policy data type”
      ↩︎ Tag-based masking policies
      “you leverage tag inheritance to protect table and view columns in the schema or database”
      ↩︎ Tag-based masking policies
      “ALTER TABLE finance.accounting.name_number SET TAG governance.tags.accounting = 'tag-based policies';”
      ↩︎ Tag-based masking policies
      “A masking policy cannot be assigned to a system tag.”
      ↩︎ Tag-based masking policies
      “Neither the masking policy nor the tag can be dropped if the masking policy is assigned to a tag.”
      ↩︎ Tag-based masking policies
      “A tag owner must have the APPLY MASKING POLICY privilege to unset a masking policy from the tag.”
      ↩︎ Tag-based masking policies
      “A table containing a column protected by a tag-based masking policy cannot be used as a mapping table.”
      ↩︎ Row access policies and mapping tables
      “the masking policy that is directly assigned to the column takes precedence over the tag-based masking policy”
      ↩︎ Exam trap 2
      “the same column cannot be specified in both a masking policy signature and a row access policy signature at the same time.”
      ↩︎ Exam trap 3
      “the table has a column with the same name but a different data type than the data type in the masking policy signature”
      ↩︎ Checkpoint
      “the masking policy that is directly assigned to the column takes precedence over the tag-based masking policy”
      ↩︎ Prediction
      “the same column cannot be specified in both a masking policy signature and a row access policy signature at the same time.”
      ↩︎ Checkpoint
    9. 9.
      “the external tokenization masking policy requires an external function that depends on an API integration”
      ↩︎ Conditional masking and External Tokenization
      “you must upgrade to Enterprise Edition or higher”
      ↩︎ Conditional masking and External Tokenization
    10. 10.
      “Note that the schema owner role is automatically granted the CREATE ROW ACCESS POLICY privilege.”
      ↩︎ Row access policies and mapping tables
      “The returned value determines whether the user has access to a given row on the table or view”
      ↩︎ Row access policies and mapping tables
      “Add (bind) the row access policy to the region column in the sales data table”
      ↩︎ Row access policies and mapping tables
      “The sales_analyst_role custom role can add or drop the row access policy from a table as needed.”
      ↩︎ Row access policies and mapping tables
    11. 11.
      “execute a DROP ROW ACCESS POLICY statement on the policy and create a new row access policy”
      ↩︎ Row access policies and mapping tables
    12. 12.
      “Snowflake recommends using this function when the policy conditions need to evaluate role hierarchy and inherited privileges.”
      ↩︎ Row access policies and mapping tables
    13. 13.
      “a row excluded from the query results based on the row access policy is not included when calculating the aggregated results”
      ↩︎ Row access policies and mapping tables
      “Once the aggregation policy is assigned to a table, successful queries against the table must aggregate its data.”
      ↩︎ Aggregation policies, differential privacy and privacy budgets
      “For example, the TRY_CAST function is allowed, but the CAST function is prohibited.”
      ↩︎ Aggregation policies, differential privacy and privacy budgets
      “Create an aggregation policy to require aggregation and define a minimum group size of 3”
      ↩︎ Aggregation policies, differential privacy and privacy budgets
      “otherwise the records are placed in a remainder group”
      ↩︎ Aggregation policies, differential privacy and privacy budgets
    14. 14.
      “FALSE prevents the column from being projected, with the behavior specified by ENFORCEMENT.”
      ↩︎ Projection policies
      “FALSE affects only columns that appear in the final results table.”
      ↩︎ Projection policies
      “FAIL - The query will fail if a protected column is included in the outermost”
      ↩︎ Projection policies
      “NULLIFY - All rows in the protected column return the value NULL. Default: FAIL”
      ↩︎ Projection policies
    15. 15.
      “queries can return data from an aggregation-constrained table or view without restriction”
      ↩︎ Aggregation policies, differential privacy and privacy budgets
    16. 16.
      “the minimum group size defines the minimum number of unique entities that must appear in the group”
      ↩︎ Aggregation policies, differential privacy and privacy budgets
      “Masking policies are enforced before aggregation policies.”
      ↩︎ Aggregation policies, differential privacy and privacy budgets
    17. 17.
      “When a query would cause the cumulative privacy loss to exceed the privacy budget limit, the query fails until the privacy budget refreshes.”
      ↩︎ Aggregation policies, differential privacy and privacy budgets
      “any parameter not specified in your ALTER PRIVACY POLICY command reverts back to its default value”
      ↩︎ Aggregation policies, differential privacy and privacy budgets
      “limit on privacy loss and the maximum amount of privacy budget spent per aggregate”
      ↩︎ Aggregation policies, differential privacy and privacy budgets
      “this function does not have a fixed amount of latency and will return the real-time values for the cumulative privacy losses”
      ↩︎ Aggregation policies, differential privacy and privacy budgets
      “When calling the function, the name of the privacy policy must be fully qualified.”
      ↩︎ Aggregation policies, differential privacy and privacy budgets
    18. 19.
      “To indicate no privacy policy, you must return no_privacy_policy() rather than returning NULL.”
      ↩︎ Aggregation policies, differential privacy and privacy budgets

    Ready to test yourself?

    Practise the 15 questions on this subdomain.

    Spotted a mistake, or was something unclear? Tell us.