Role hierarchy in Snowflake - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to grant a role to another role in Snowflake.

Snowflake

GRANT ROLE [1] TO ROLE analyst_role;

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

Aadmin_role

Bfinance_role

Cread_only

Ddata_scientist

Attempts:

3 left

💡 Hint

Common Mistakes

Using a role that should receive privileges instead of the one granting them.

Confusing role names.

The GRANT ROLE command assigns one role to another, establishing a hierarchy. Here, admin_role is granted to analyst_role.

2fill in blank

medium

Complete the code to show the roles granted to a specific role.

Snowflake

SHOW GRANTS TO ROLE [1];

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

Afinance_role

Banalyst_role

Cdata_engineer

Dadmin_role

Attempts:

3 left

💡 Hint

Common Mistakes

Using the wrong role name in the command.

Confusing 'TO' with 'OF' in the command syntax.

The command SHOW GRANTS TO ROLE lists all roles and privileges granted to the specified role. Here, we check what analyst_role has.

3fill in blank

hard

Fix the error in the code to correctly revoke a role from another role.

Snowflake

REVOKE ROLE [1] FROM ROLE sales_role;

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

Afinance_role

Bsales_role

Cdata_analyst

Dmarketing_role

Attempts:

3 left

💡 Hint

Common Mistakes

Trying to revoke the role from itself.

Using a role that was never granted.

The REVOKE ROLE command removes a granted role from another role. Here, marketing_role is revoked from sales_role.

4fill in blank

hard

Fill both blanks to create a role hierarchy where 'manager_role' inherits from 'employee_role'.

Snowflake

GRANT ROLE [1] TO ROLE [2];

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

Aemployee_role

Badmin_role

Cmanager_role

Danalyst_role

Attempts:

3 left

💡 Hint

Common Mistakes

Reversing the roles in the grant statement.

Granting a higher role to a lower role.

This command grants employee_role to manager_role, so managers inherit employee permissions.

5fill in blank

hard

Fill all three blanks to list all roles granted to 'developer_role' and then revoke one role.

Snowflake

SHOW GRANTS TO ROLE [1];
REVOKE ROLE [2] FROM ROLE [3];

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

Adeveloper_role

Bintern_role

Dcontractor_role

Attempts:

3 left

💡 Hint

Common Mistakes

Using different roles inconsistently in the commands.

Revoking a role from a role that does not have it.

First, we show all grants to developer_role. Then, we revoke intern_role from developer_role.

Practice

1. What does role hierarchy in Snowflake allow you to do?

easy

A. Store data in different regions automatically

B. Create multiple users with the same password

C. Let roles inherit permissions from other roles

D. Schedule queries to run at specific times

Solution

1. Step 1: Understand role hierarchy concept

   Role hierarchy means roles can inherit permissions from other roles, simplifying access management.

2. Step 2: Identify correct description

   Only Let roles inherit permissions from other roles describes this inheritance feature correctly.

3. Final Answer:

   Let roles inherit permissions from other roles -> Option C

4. Quick Check:

   Role hierarchy = permission inheritance [OK]

Hint: Role hierarchy means roles inherit permissions [OK]

Common Mistakes:

• Confusing role hierarchy with user password management
• Thinking role hierarchy controls data storage
• Mixing role hierarchy with query scheduling

2. Which of the following is the correct syntax to grant a role to another role in Snowflake?

easy

A. GRANT ROLE parent_role TO ROLE child_role;

B. GRANT ROLE TO ROLE parent_role child_role;

C. GRANT ROLE child_role TO ROLE parent_role;

D. GRANT ROLE parent_role child_role TO ROLE;

Solution

1. Step 1: Recall Snowflake grant syntax

   To make one role inherit another, you grant the parent role to the child role using: GRANT ROLE parent_role TO ROLE child_role;

2. Step 2: Match syntax to options

   GRANT ROLE parent_role TO ROLE child_role; matches the correct syntax exactly.

3. Final Answer:

   GRANT ROLE parent_role TO ROLE child_role; -> Option A

4. Quick Check:

   Grant parent to child = correct syntax [OK]

Hint: Grant parent role TO child role syntax [OK]

Common Mistakes:

• Reversing parent and child roles in the grant statement
• Incorrect order of keywords in the command
• Missing 'TO ROLE' keyword

3. Given the following role hierarchy:
GRANT ROLE analyst TO ROLE senior_analyst;
GRANT ROLE senior_analyst TO ROLE manager;
If a user is assigned the role manager, which roles' permissions does the user have?

medium

A. Manager and senior_analyst only

B. Manager, senior_analyst, and analyst

C. Only manager

D. Only analyst

Solution

1. Step 1: Trace role inheritance

   Manager role inherits senior_analyst, which inherits analyst. So manager includes all permissions from senior_analyst and analyst.

2. Step 2: Determine user permissions

   User with manager role has permissions from manager, senior_analyst, and analyst roles.

3. Final Answer:

   Manager, senior_analyst, and analyst -> Option B

4. Quick Check:

   Role inheritance includes all ancestor roles [OK]

Hint: User gets all inherited roles' permissions [OK]

Common Mistakes:

• Assuming user only has direct role permissions
• Ignoring transitive inheritance in role hierarchy
• Confusing child and parent roles

4. You run this command:
GRANT ROLE analyst TO senior_analyst;
What is wrong with this command?

medium

A. Cannot grant a role to another role

B. Role names must be in quotes

C. The order of roles is reversed

D. Missing 'ROLE' keyword before 'senior_analyst'

Solution

1. Step 1: Check syntax for granting roles

   The correct syntax requires 'TO ROLE' before the role receiving the grant.

2. Step 2: Identify missing keyword

   The command misses 'ROLE' keyword before 'senior_analyst'. It should be: GRANT ROLE analyst TO ROLE senior_analyst;

3. Final Answer:

   Missing 'ROLE' keyword before 'senior_analyst' -> Option D

4. Quick Check:

   Grant syntax requires 'TO ROLE' [OK]

Hint: Always include 'TO ROLE' keyword in grant statements [OK]

Common Mistakes:

• Omitting 'ROLE' keyword after TO
• Thinking roles cannot be granted to roles
• Using quotes unnecessarily around role names

5. You want to create a role hierarchy where intern inherits from analyst, and analyst inherits from manager. Which sequence of commands correctly sets this up?

hard

A. GRANT ROLE manager TO ROLE analyst; GRANT ROLE analyst TO ROLE intern;

B. GRANT ROLE analyst TO ROLE intern; GRANT ROLE manager TO ROLE analyst;

C. GRANT ROLE manager TO ROLE analyst; GRANT ROLE intern TO ROLE analyst;

D. GRANT ROLE intern TO ROLE analyst; GRANT ROLE analyst TO ROLE manager;

Solution

1. Step 1: Understand inheritance direction

   Child role inherits from parent role by granting parent role TO child role.

2. Step 2: Apply to given roles

   Intern inherits analyst: GRANT ROLE analyst TO ROLE intern;
   Analyst inherits manager: GRANT ROLE manager TO ROLE analyst;

3. Step 3: Match sequence to options

   GRANT ROLE manager TO ROLE analyst; GRANT ROLE analyst TO ROLE intern; matches the correct order and syntax.

4. Final Answer:

   GRANT ROLE manager TO ROLE analyst; GRANT ROLE analyst TO ROLE intern; -> Option A

5. Quick Check:

   Grant parent TO child for inheritance [OK]

Hint: Grant parent role TO child role to inherit [OK]

Common Mistakes:

• Reversing parent and child roles in grants
• Granting child TO parent instead of parent TO child
• Mixing order of commands