Column-level security with masking policies in Snowflake - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to create a masking policy that masks the email column.

Snowflake

CREATE MASKING POLICY mask_email AS (val STRING) RETURNS STRING -> CASE WHEN CURRENT_ROLE() IN ('[1]') THEN val ELSE '****@****.com' END;

Drag options to blanks, or click blank then click option'

AACCOUNTADMIN

BSECURITY_ADMIN

CPUBLIC

DANALYST_ROLE

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'PUBLIC' exposes data to everyone.

Using admin roles is not best practice for masking policies.

The masking policy allows only the role 'ANALYST_ROLE' to see the real email. Others see masked data.

2fill in blank

medium

Complete the code to apply the masking policy to the email column in the users table.

Snowflake

ALTER TABLE users ALTER COLUMN email SET MASKING POLICY [1];

Drag options to blanks, or click blank then click option'

Amask_email

Bmask_ssn

Cmask_phone

Dmask_address

Attempts:

3 left

💡 Hint

Common Mistakes

Applying the wrong masking policy to the column.

Forgetting to use the correct policy name.

The masking policy named 'mask_email' is applied to the email column to enforce column-level security.

3fill in blank

hard

Fix the error in the masking policy creation by completing the missing keyword.

Snowflake

[1] MASKING POLICY mask_ssn AS (val STRING) RETURNS STRING -> CASE WHEN CURRENT_ROLE() IN ('SECURITY_ADMIN') THEN val ELSE 'XXX-XX-XXXX' END;

Drag options to blanks, or click blank then click option'

AIF

BOR

CCREATE

DREPLACE

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'OR' or 'IF' instead of 'CREATE'.

Omitting the 'CREATE' keyword.

The correct syntax to create a masking policy starts with 'CREATE MASKING POLICY'.

4fill in blank

hard

Fill both blanks to create a masking policy that shows full phone number only to 'SUPPORT_ROLE' and masks others.

Snowflake

CREATE MASKING POLICY mask_phone AS (val STRING) RETURNS STRING -> CASE WHEN CURRENT_ROLE() [1] ('SUPPORT_ROLE') THEN val [2] 'XXX-XXX-XXXX' END;

Drag options to blanks, or click blank then click option'

AIN

BNOT IN

CELSE

DTHEN

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'NOT IN' reverses the logic.

Using 'THEN' instead of 'ELSE' for the masked value.

The policy checks if the current role is in 'SUPPORT_ROLE' and shows the value; otherwise, it masks it.

5fill in blank

hard

Fill all three blanks to create a masking policy that masks salary except for 'HR_ROLE' and 'FINANCE_ROLE'.

Snowflake

CREATE MASKING POLICY mask_salary AS (val NUMBER) RETURNS NUMBER -> CASE WHEN CURRENT_ROLE() [1] ('[2]', '[3]') THEN val ELSE 0 END;

Drag options to blanks, or click blank then click option'

AIN

BHR_ROLE

CFINANCE_ROLE

DNOT IN

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'NOT IN' reverses the logic.

Swapping role names or misspelling them.

The policy allows roles 'HR_ROLE' and 'FINANCE_ROLE' to see the real salary; others see 0.