Creating custom roles in Snowflake - Practice Exercises

Challenge - 5 Problems

🧠 Conceptual

intermediate

Understanding Role Hierarchy in Snowflake

In Snowflake, when you create a custom role and grant it to another role, what is the effect on the privileges of the parent role?

A The parent role can use privileges of the custom role only if the custom role is granted to it explicitly.

B The custom role inherits all privileges of the parent role automatically.

C The parent role inherits all privileges of the custom role automatically.

D Privileges are not shared between roles regardless of grants.

💡 Hint

Think about how role inheritance works in Snowflake.

In Snowflake, when a role is granted to another role, the parent role inherits all privileges of the granted role. This allows hierarchical privilege management.

❓ Configuration

intermediate

Creating a Custom Role with Specific Privileges

Which Snowflake SQL command correctly creates a custom role named data_analyst and grants it the privilege to select from the sales_data schema?

A CREATE ROLE data_analyst; GRANT SELECT ON DATABASE sales_data TO ROLE data_analyst;

B CREATE ROLE data_analyst; GRANT SELECT ON SCHEMA sales_data TO ROLE data_analyst;

C CREATE ROLE data_analyst; GRANT USAGE ON SCHEMA sales_data TO ROLE data_analyst;

D CREATE ROLE data_analyst; GRANT SELECT ON TABLE sales_data TO ROLE data_analyst;

💡 Hint

Remember that to select data, the role needs SELECT privilege on the schema or tables.

The correct way is to create the role and grant SELECT privilege on the schema. Granting on database or table named sales_data is incorrect because sales_data is a schema, not a database or table.

❓ Architecture

advanced

Designing Role Hierarchy for Separation of Duties

You want to design a Snowflake role hierarchy to separate duties between data engineers and data analysts. Which design best enforces that data engineers can manage tables but cannot query data, while data analysts can query data but cannot manage tables?

A Create a <code>data_engineer</code> role with CREATE and MODIFY privileges on tables, and a separate <code>data_analyst</code> role with SELECT privileges only; do not grant either role to the other.

B Create a <code>data_analyst</code> role with all privileges and grant it to <code>data_engineer</code> role.

C Create a single role with both CREATE and SELECT privileges and assign it to both engineers and analysts.

D Create a <code>data_engineer</code> role with SELECT privileges and grant it to <code>data_analyst</code> role.

💡 Hint

Think about how granting roles affects privilege inheritance and separation.

To enforce separation of duties, roles should be distinct without granting one to the other. Data engineers get management privileges; data analysts get query privileges only.

❓ security

advanced

Preventing Privilege Escalation with Custom Roles

Which practice helps prevent privilege escalation when creating custom roles in Snowflake?

A Use the PUBLIC role to grant all privileges to custom roles.

B Grant all privileges to custom roles to avoid permission errors during operations.

C Assign the ACCOUNTADMIN role to all users and then create custom roles for auditing.

D Grant only the minimum required privileges to each custom role and avoid granting powerful roles like ACCOUNTADMIN to users directly.

💡 Hint

Think about the principle of least privilege.

Granting only necessary privileges minimizes risk of privilege escalation. Avoid giving powerful roles like ACCOUNTADMIN to users directly.

❓ service_behavior

expert

Effect of Revoking a Role in a Role Hierarchy

Consider the following Snowflake role hierarchy: role_A is granted to role_B, and role_B is granted to role_C. If role_A is revoked from role_B, what is the effect on role_C?

A <code>role_C</code> loses all privileges inherited from <code>role_A</code> because the grant chain is broken.

B <code>role_C</code> retains privileges from <code>role_A</code> because it was granted directly.

C <code>role_C</code> gains additional privileges from <code>role_A</code> automatically.

D Revoking <code>role_A</code> from <code>role_B</code> has no effect on <code>role_C</code>.

💡 Hint

Consider how privilege inheritance works through role grants.

Since role_C inherits privileges from role_B, which in turn inherits from role_A, revoking role_A from role_B breaks the chain. Therefore, role_C loses privileges from role_A.