Role hierarchy in Snowflake - Practice Problems & Coding Challenges

Challenge - 5 Problems

🧠 Conceptual

intermediate

Understanding Role Inheritance in Snowflake

In Snowflake, roles can inherit privileges from other roles. If Role A is granted to Role B, which of the following statements is true about Role B?

A Role B automatically has all privileges of Role A plus any additional privileges assigned directly to Role B.

B Role B only has the privileges explicitly assigned to it, not those of Role A.

C Role B can only use privileges of Role A if Role A is active at the same time.

D Role A loses its privileges once granted to Role B.

💡 Hint

Think about how inheritance works in everyday situations, like a child inheriting traits from a parent.

When Role A is granted to Role B, Role B inherits all privileges of Role A plus any privileges assigned directly to Role B. This is how Snowflake manages role hierarchies.

❓ Architecture

intermediate

Role Hierarchy Design for Least Privilege

You want to design a role hierarchy in Snowflake to follow the principle of least privilege. Which approach best supports this goal?

A Create multiple roles with specific privileges and grant lower-level roles to higher-level roles as needed.

B Create a single role with all privileges and assign it to all users.

C Assign privileges directly to users instead of using roles.

D Use only the ACCOUNTADMIN role for all users to simplify management.

💡 Hint

Think about how giving only what is needed helps keep things secure.

Creating multiple roles with specific privileges and granting lower-level roles to higher-level roles allows fine-grained control and supports least privilege by limiting access to only what is necessary.

❓ service_behavior

advanced

Effect of Revoking a Role in a Hierarchy

If Role A is granted to Role B, and Role B is granted to User U, what happens when Role A is revoked from Role B?

A User U keeps privileges from Role A permanently even after revocation.

B User U retains privileges from Role A until they log out and log back in.

C User U loses all privileges that came from Role A immediately.

D Revoking Role A from Role B has no effect on User U's privileges.

💡 Hint

Consider how removing a key from a chain affects access.

Revoking Role A from Role B immediately removes the privileges inherited by Role B and thus User U loses those privileges right away.

❓ security

advanced

Preventing Privilege Escalation in Role Hierarchy

Which practice helps prevent privilege escalation through role hierarchy in Snowflake?

A Granting the SECURITYADMIN role to all users so they can manage roles.

B Limiting the assignment of powerful roles like SECURITYADMIN and ACCOUNTADMIN to trusted administrators only.

C Allowing users to create and grant roles freely to manage their own access.

D Using the PUBLIC role to assign all privileges to new users.

💡 Hint

Think about who should have the keys to the kingdom.

Limiting powerful roles to trusted administrators prevents unauthorized users from escalating privileges by granting themselves or others high-level roles.

✅ Best Practice

expert

Analyzing Role Hierarchy for Access Troubleshooting

You notice a user cannot access a table even though they have a role that should grant access. The role hierarchy is complex. Which step is best to troubleshoot this issue?

A Assume the table is unavailable and restart the Snowflake service.

B Immediately grant the user the ACCOUNTADMIN role to bypass access issues.

C Remove all roles from the user and reassign them randomly to refresh privileges.

D Check if the user has the role active in their current session and verify the role hierarchy grants the needed privilege.

💡 Hint

Think about verifying the path of access step-by-step.

Access depends on the active role in the user's session and the privileges granted through the role hierarchy. Checking these helps identify missing grants or inactive roles.

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