Column-level security with masking policies in Snowflake - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ service_behavior

intermediate

How does a masking policy affect query results?

Consider a masking policy applied to a column in Snowflake. What happens when a user without the required role queries that column?

A The user sees the original data without any changes.

B The user sees masked or obfuscated data as defined by the masking policy.

C The query fails with an access denied error.

D The user sees NULL values for that column.

💡 Hint

Think about what masking means: hiding or changing data for unauthorized users.

A masking policy replaces the original data with masked values for users who do not have the required privileges. It does not block the query or return NULLs but shows obfuscated data.

❓ Configuration

intermediate

Identify the correct syntax to create a masking policy

Which of the following SQL statements correctly creates a masking policy in Snowflake that masks a column's value to '****' for unauthorized users?

A CREATE MASKING POLICY mask_ssn AS (val STRING) RETURNS STRING -> CASE WHEN CURRENT_ROLE() IN ('FULL_ACCESS') THEN val ELSE '****' END;

B CREATE MASKING POLICY mask_ssn (val STRING) RETURNS STRING AS CASE WHEN CURRENT_ROLE() = 'FULL_ACCESS' THEN val ELSE '****' END;

C ;DNE '****' ESLE lav NEHT )'SSECCA_LLUF'( NI )(ELOR_TNERRUC NEHW ESAC >- GNIRTS SNRUTER )GNIRTS lav( SA nss_ksam YCILOP GNIKSAM ETAERC

D CREATE MASKING POLICY mask_ssn AS (val STRING) RETURNS STRING -> CASE WHEN CURRENT_ROLE() = 'FULL_ACCESS' THEN val ELSE '****' END;

💡 Hint

Look for correct use of 'IN' operator and arrow '->' syntax.

The correct syntax uses 'AS (val STRING) RETURNS STRING ->' followed by the CASE expression. The role check uses 'IN' with parentheses for a list.

❓ Architecture

advanced

Best practice for applying masking policies in multi-role environments

In an environment with multiple user roles, what is the best practice for applying masking policies to protect sensitive columns?

A Apply a single masking policy that masks data for all roles except the highest privileged role.

B Create multiple masking policies for each role and apply them all to the same column.

C Apply masking policies only on views, not on base tables.

D Avoid masking policies and rely solely on role-based access control on tables.

💡 Hint

Think about simplicity and maintainability in role management.

Using a single masking policy with role checks simplifies management and ensures consistent masking behavior. Multiple policies on the same column are not supported. Views do not enforce masking policies directly, and relying only on access control misses the benefit of dynamic masking.

❓ security

advanced

What happens if a masking policy references a non-existent role?

If a masking policy's CASE expression checks for a role that does not exist in Snowflake, what is the effect when the policy is applied?

A The masking policy fails to create with an error about the invalid role.

B The policy causes queries to fail with a runtime error when applied.

C The policy ignores the invalid role and masks data only for existing roles.

D The policy works but the role check always evaluates to false, masking data for all users.

💡 Hint

Consider how role checks are evaluated at runtime.

Snowflake does not validate role names inside masking policies at creation. If a role does not exist, the check returns false, so users never match that role and data is masked for all.

🧠 Conceptual

expert

Order of evaluation in masking policies with multiple conditions

Given this masking policy snippet:
CASE WHEN CURRENT_ROLE() IN ('ADMIN') THEN val WHEN CURRENT_ROLE() IN ('AUDITOR') THEN 'REDACTED' ELSE NULL END
What will a user with the 'AUDITOR' role see when querying the masked column?

A NULL value.

B An error because multiple roles are checked.

C The string 'REDACTED'.

D The original value of the column.

💡 Hint

Think about how CASE statements evaluate conditions in order.

The CASE statement checks conditions top to bottom. Since the user is not 'ADMIN', the first condition fails. The second condition matches 'AUDITOR', so the user sees 'REDACTED'.