Row access policies in Snowflake - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to create a row access policy named 'policy1'.

Snowflake

CREATE ROW ACCESS POLICY policy1 AS (user_role STRING) RETURNS BOOLEAN -> user_role = [1];

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

A'USER'

B'ADMIN'

C'GUEST'

D'ANALYST'

Attempts:

3 left

💡 Hint

Common Mistakes

Forgetting to use quotes around the role name.

Using a role name that is not 'ADMIN'.

The policy allows access only if the user role is 'ADMIN'.

2fill in blank

medium

Complete the code to apply the row access policy 'policy1' to the table 'sales'.

Snowflake

ALTER TABLE sales [1] ROW ACCESS POLICY policy1 ON (user_role);

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

AREMOVE

BDROP

CADD

DCREATE

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'REMOVE' or 'DROP' which deletes policies.

Using 'CREATE' which is for creating objects.

The 'ADD' keyword applies the row access policy to the table.

3fill in blank

hard

Fix the error in the row access policy definition to correctly check if the user role is 'ANALYST'.

Snowflake

CREATE ROW ACCESS POLICY policy2 AS (user_role STRING) RETURNS BOOLEAN -> user_role [1] 'ANALYST';

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

A=

B==

C!=

D<>

Attempts:

3 left

💡 Hint

Common Mistakes

Using '==' which is not valid in SQL.

Using '<>' which means not equal.

Snowflake uses a single '=' for equality in SQL expressions.

4fill in blank

hard

Fill both blanks to create a row access policy that allows access only if the user's department matches the row's department.

Snowflake

CREATE ROW ACCESS POLICY dept_policy AS (user_dept STRING, row_dept STRING) RETURNS BOOLEAN -> user_dept [1] row_dept [2] TRUE;

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

A=

BAND

COR

D!=

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'OR' instead of 'AND' which would allow more access.

Using '!=' which denies access on match.

The policy checks equality and returns TRUE if matched using AND.

5fill in blank

hard

Fill all three blanks to create a row access policy that allows access if the user role is 'MANAGER', the region matches, and the access flag is true.

Snowflake

CREATE ROW ACCESS POLICY complex_policy AS (user_role STRING, user_region STRING, row_region STRING, access_flag BOOLEAN) RETURNS BOOLEAN -> (user_role [1] 'MANAGER') [2] (user_region [3] row_region) AND access_flag;

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

A=

BAND

DOR

Attempts:

3 left

💡 Hint

Common Mistakes

Using AND instead of OR for role check, which restricts access too much.

Using '!=' which denies access on match.

The policy uses OR to check role, AND to combine conditions, and '=' to compare regions.