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.