Creating custom roles in Snowflake - Interactive Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to create a new role named 'data_analyst'.

Snowflake

CREATE ROLE [1];

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

Adata_analyst

Banalyst_data

Crole_data

DdataRole

Attempts:

3 left

💡 Hint

Common Mistakes

Using an incorrect role name.

Forgetting the CREATE ROLE statement.

The command CREATE ROLE data_analyst; creates a new role named 'data_analyst'.

2fill in blank

medium

Complete the code to grant the 'SELECT' privilege on the 'sales' schema to the role.

Snowflake

GRANT SELECT ON SCHEMA sales TO ROLE [1];

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

Adata_analyst

Bguest

Cuser_role

Dadmin

Attempts:

3 left

💡 Hint

Common Mistakes

Granting privileges to the wrong role.

Using incorrect syntax for the GRANT statement.

The role 'data_analyst' is granted SELECT privilege on the 'sales' schema.

3fill in blank

hard

Fix the error in the code to assign the role 'data_analyst' to the user 'alice'.

Snowflake

GRANT ROLE [1] TO USER alice;

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

Aanalyst_data

Bdata_analyst

CdataAnalyst

Danalyst

Attempts:

3 left

💡 Hint

Common Mistakes

Using incorrect role names with different casing.

Misspelling the role name.

The correct role name is 'data_analyst' to assign it to user 'alice'.

4fill in blank

hard

Fill both blanks to revoke the 'SELECT' privilege from the role on the 'marketing' schema.

Snowflake

REVOKE [1] ON SCHEMA marketing FROM ROLE [2];

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

ASELECT

BINSERT

Cdata_analyst

Dadmin

Attempts:

3 left

💡 Hint

Common Mistakes

Revoking the wrong privilege.

Using the wrong role name.

The command revokes the SELECT privilege from the 'data_analyst' role on the 'marketing' schema.

5fill in blank

hard

Fill all three blanks to create a custom role 'report_viewer', grant it USAGE on the 'reports' database, and assign it to user 'bob'.

Snowflake

CREATE ROLE [1];
GRANT USAGE ON DATABASE reports TO ROLE [2];
GRANT ROLE [3] TO USER bob;

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

Areport_viewer

Ddata_analyst

Attempts:

3 left

💡 Hint

Common Mistakes

Using different role names in the commands.

Forgetting to grant USAGE privilege before assignment.

This sequence creates the role 'report_viewer', grants it usage on the 'reports' database, and assigns it to user 'bob'.

Practice

1. What is the main purpose of creating a custom role in Snowflake?

easy

A. To create a new user account

B. To store data in a new database

C. To run SQL queries faster

D. To control and manage user access permissions

Solution

1. Step 1: Understand role purpose

   Custom roles in Snowflake are designed to manage what users can do by controlling permissions.

2. Step 2: Identify correct function

   Creating a role is not about storing data, creating users, or speeding queries, but about access control.

3. Final Answer:

   To control and manage user access permissions -> Option D

4. Quick Check:

   Role purpose = Access control [OK]

Hint: Roles control permissions, not data or users [OK]

Common Mistakes:

• Confusing roles with users
• Thinking roles store data
• Assuming roles speed up queries

2. Which of the following is the correct syntax to create a new role named data_analyst in Snowflake?

easy

A. CREATE ROLE data_analyst;

B. NEW ROLE data_analyst;

C. MAKE ROLE data_analyst;

D. ADD ROLE data_analyst;

Solution

1. Step 1: Recall Snowflake role creation syntax

   The correct command to create a role is CREATE ROLE role_name;.

2. Step 2: Match syntax with options

   Only CREATE ROLE data_analyst; uses the correct CREATE ROLE syntax.

3. Final Answer:

   CREATE ROLE data_analyst; -> Option A

4. Quick Check:

   CREATE ROLE = correct syntax [OK]

Hint: Use CREATE ROLE to make new roles [OK]

Common Mistakes:

• Using NEW ROLE instead of CREATE ROLE
• Using MAKE or ADD which are invalid
• Missing semicolon at the end

3. Given the commands:

CREATE ROLE analyst;
GRANT USAGE ON DATABASE sales_db TO ROLE analyst;
GRANT ROLE analyst TO USER alice;

What permission does user alice have on sales_db?

medium

A. No permissions on sales_db

B. Only USAGE permission on sales_db

C. ALL permissions on sales_db

D. SELECT permission on sales_db

Solution

1. Step 1: Analyze role creation and grants

   The role analyst is created and given USAGE permission on sales_db.

2. Step 2: Check role assignment to user

   User alice is granted the analyst role, so she inherits its permissions.

3. Final Answer:

   Only USAGE permission on sales_db -> Option B

4. Quick Check:

   Role grants USAGE, user inherits USAGE [OK]

Hint: User gets permissions of assigned roles [OK]

Common Mistakes:

• Assuming no permissions without direct grant
• Confusing USAGE with SELECT permission
• Ignoring role inheritance

4. You run this command:

CREATE ROLE analyst

but get an error. What is the likely cause?

medium

A. You must create a user before creating a role

B. Role name analyst is reserved and cannot be used

C. Missing semicolon at the end of the command

D. CREATE ROLE command is not supported in Snowflake

Solution

1. Step 1: Check command syntax

   Snowflake commands require a semicolon at the end to execute properly.

2. Step 2: Evaluate other options

   Role names like analyst are allowed, users are independent of roles, and CREATE ROLE is supported.

3. Final Answer:

   Missing semicolon at the end of the command -> Option C

4. Quick Check:

   Semicolon ends command [OK]

Hint: Always end SQL commands with a semicolon [OK]

Common Mistakes:

• Forgetting semicolon causes syntax error
• Thinking role names are restricted
• Believing users must exist first

5. You want to create a custom role report_viewer that can only read data from the reports schema in the analytics database. Which sequence of commands correctly achieves this?

hard

A. CREATE ROLE report_viewer;
GRANT USAGE ON DATABASE analytics TO ROLE report_viewer;
GRANT USAGE ON SCHEMA analytics.reports TO ROLE report_viewer;
GRANT SELECT ON ALL TABLES IN SCHEMA analytics.reports TO ROLE report_viewer;

B. CREATE ROLE report_viewer;
GRANT SELECT ON DATABASE analytics TO ROLE report_viewer;

C. CREATE ROLE report_viewer;
GRANT ALL ON SCHEMA analytics.reports TO ROLE report_viewer;

D. CREATE ROLE report_viewer;
GRANT USAGE ON DATABASE analytics TO ROLE report_viewer;
GRANT SELECT ON SCHEMA analytics.reports TO ROLE report_viewer;

Solution

1. Step 1: Create the role

   Use CREATE ROLE report_viewer; to make the role.

2. Step 2: Grant USAGE on database and schema

   Granting USAGE on the database and schema allows access to objects inside.

3. Step 3: Grant SELECT on all tables in the schema

   Grant SELECT on all tables to allow read access to data.

4. Step 4: Evaluate options

   CREATE ROLE report_viewer; GRANT USAGE ON DATABASE analytics TO ROLE report_viewer; GRANT USAGE ON SCHEMA analytics.reports TO ROLE report_viewer; GRANT SELECT ON ALL TABLES IN SCHEMA analytics.reports TO ROLE report_viewer; correctly grants USAGE and SELECT at proper levels. CREATE ROLE report_viewer; GRANT SELECT ON DATABASE analytics TO ROLE report_viewer; misses schema and USAGE grants. CREATE ROLE report_viewer; GRANT ALL ON SCHEMA analytics.reports TO ROLE report_viewer; grants ALL which is too broad. CREATE ROLE report_viewer; GRANT USAGE ON DATABASE analytics TO ROLE report_viewer; GRANT SELECT ON SCHEMA analytics.reports TO ROLE report_viewer; grants SELECT on schema which is invalid; SELECT must be on tables.

5. Final Answer:

   CREATE ROLE report_viewer; GRANT USAGE ON DATABASE analytics TO ROLE report_viewer; GRANT USAGE ON SCHEMA analytics.reports TO ROLE report_viewer; GRANT SELECT ON ALL TABLES IN SCHEMA analytics.reports TO ROLE report_viewer; -> Option A

6. Quick Check:

   Grant USAGE + SELECT on tables = read access [OK]

Hint: Grant USAGE on DB/schema, SELECT on tables for read-only role [OK]

Common Mistakes:

• Granting SELECT on schema instead of tables
• Missing USAGE grants on database or schema
• Granting ALL permissions instead of SELECT only