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'.

Practice

1. What is the main purpose of a masking policy in Snowflake's column-level security?

easy

A. To create backups of sensitive columns

B. To hide or mask sensitive column data based on user roles

C. To encrypt the entire database automatically

D. To delete sensitive data permanently from a column

Solution

1. Step 1: Understand masking policy purpose

   Masking policies control how sensitive data is shown without changing the original data.

2. Step 2: Identify role-based data visibility

   They mask or reveal data depending on the user's role or permissions.

3. Final Answer:

   To hide or mask sensitive column data based on user roles -> Option B

4. Quick Check:

   Masking policy = role-based data masking [OK]

Hint: Masking policies control data visibility by user roles [OK]

Common Mistakes:

• Thinking masking deletes data
• Confusing masking with encryption
• Assuming masking backs up data

2. Which of the following is the correct syntax to create a masking policy in Snowflake?

easy

A. CREATE MASKING POLICY policy_name AS CASE WHEN CURRENT_ROLE() IN ('ANALYST') THEN val ELSE '****' END RETURNS STRING;

B. CREATE MASKING POLICY policy_name (val STRING) RETURNS STRING AS CASE WHEN CURRENT_ROLE() IN ('ANALYST') THEN val ELSE '****' END;

C. CREATE MASKING POLICY policy_name AS (val STRING) RETURNS STRING AS (CASE WHEN CURRENT_ROLE() IN ('ANALYST') THEN val ELSE '****' END);

D. CREATE MASKING POLICY policy_name RETURNS STRING AS (val STRING) CASE WHEN CURRENT_ROLE() IN ('ANALYST') THEN val ELSE '****' END;

Solution

1. Step 1: Recall correct syntax structure

   Snowflake masking policy syntax starts with CREATE MASKING POLICY name AS (input parameter) RETURNS type.

2. Step 2: Match syntax with options

   CREATE MASKING POLICY policy_name AS (val STRING) RETURNS STRING AS (CASE WHEN CURRENT_ROLE() IN ('ANALYST') THEN val ELSE '****' END); correctly uses AS (val STRING) RETURNS STRING followed by the CASE expression.

3. Final Answer:

   CREATE MASKING POLICY policy_name AS (val STRING) RETURNS STRING AS (CASE WHEN CURRENT_ROLE() IN ('ANALYST') THEN val ELSE '****' END); -> Option C

4. Quick Check:

   Correct syntax = CREATE MASKING POLICY policy_name AS (val STRING) RETURNS STRING AS (CASE WHEN CURRENT_ROLE() IN ('ANALYST') THEN val ELSE '****' END); [OK]

Hint: Syntax: CREATE MASKING POLICY name AS (param) RETURNS type [OK]

Common Mistakes:

• Placing RETURNS before AS
• Missing parentheses around input parameter
• Incorrect order of clauses

3. Given this masking policy:

CREATE MASKING POLICY mask_ssn AS (val STRING) RETURNS STRING AS (CASE WHEN CURRENT_ROLE() IN ('HR') THEN val ELSE 'XXX-XX-XXXX' END);

What will a user with role 'SALES' see when querying the masked SSN column?

medium

A. 'XXX-XX-XXXX'

B. NULL

C. The actual SSN value

D. An error message

Solution

1. Step 1: Analyze masking policy logic

   The policy shows the real value only if CURRENT_ROLE() is 'HR'. Otherwise, it masks with 'XXX-XX-XXXX'.

2. Step 2: Determine output for 'SALES' role

   Since 'SALES' is not 'HR', the user sees the masked string 'XXX-XX-XXXX'.

3. Final Answer:

   'XXX-XX-XXXX' -> Option A

4. Quick Check:

   Role not HR = masked value [OK]

Hint: If role not allowed, masking policy returns mask string [OK]

Common Mistakes:

• Assuming user sees actual data
• Expecting NULL instead of mask
• Thinking it causes an error

4. You created a masking policy but users still see unmasked data. What is the most likely cause?

medium

A. The masking policy was not attached to the column

B. The masking policy syntax has a typo

C. The user has the role that bypasses masking

D. The table is missing a primary key

Solution

1. Step 1: Check masking policy attachment

   Masking policies must be assigned to columns to take effect.

2. Step 2: Consider user roles and syntax

   While roles can bypass masking, if all users see unmasked data, likely the policy is not attached.

3. Final Answer:

   The masking policy was not attached to the column -> Option A

4. Quick Check:

   Policy attached = masking works [OK]

Hint: Attach masking policy to column to activate it [OK]

Common Mistakes:

• Ignoring policy attachment step
• Blaming user roles without checking
• Confusing table keys with masking

5. You want to create a masking policy that shows full email addresses to 'ADMIN' role, partially masks emails for 'SUPPORT' role (showing only domain), and masks completely for others. Which policy logic correctly implements this?

hard

A. CASE WHEN CURRENT_ROLE() = 'SUPPORT' THEN val WHEN CURRENT_ROLE() = 'ADMIN' THEN CONCAT('***@', SPLIT_PART(val, '@', 2)) ELSE '****' END

B. CASE WHEN CURRENT_ROLE() = 'ADMIN' THEN '****' WHEN CURRENT_ROLE() = 'SUPPORT' THEN val ELSE CONCAT('***@', SPLIT_PART(val, '@', 2)) END

C. CASE WHEN CURRENT_ROLE() IN ('ADMIN', 'SUPPORT') THEN val ELSE '****' END

D. CASE WHEN CURRENT_ROLE() = 'ADMIN' THEN val WHEN CURRENT_ROLE() = 'SUPPORT' THEN CONCAT('***@', SPLIT_PART(val, '@', 2)) ELSE '****' END

Solution

1. Step 1: Identify role conditions and outputs

   'ADMIN' sees full email (val), 'SUPPORT' sees masked local part but full domain, others see full mask.

2. Step 2: Match logic to requirements

   CASE WHEN CURRENT_ROLE() = 'ADMIN' THEN val WHEN CURRENT_ROLE() = 'SUPPORT' THEN CONCAT('***@', SPLIT_PART(val, '@', 2)) ELSE '****' END shows full val for ADMIN, partial mask for SUPPORT using CONCAT and SPLIT_PART, else full mask.

3. Final Answer:

   CASE WHEN CURRENT_ROLE() = 'ADMIN' THEN val WHEN CURRENT_ROLE() = 'SUPPORT' THEN CONCAT('***@', SPLIT_PART(val, '@', 2)) ELSE '****' END -> Option D

4. Quick Check:

   Role-based partial/full masking = CASE WHEN CURRENT_ROLE() = 'ADMIN' THEN val WHEN CURRENT_ROLE() = 'SUPPORT' THEN CONCAT('***@', SPLIT_PART(val, '@', 2)) ELSE '****' END [OK]

Hint: Order CASE by roles from most to less privileged [OK]

Common Mistakes:

• Swapping ADMIN and SUPPORT logic
• Showing full email to all roles
• Incorrect string functions usage