Creating custom roles in Snowflake - Visual Walkthrough

Process Flow - Creating custom roles

Start

↓

Define Role Name

↓

Execute CREATE ROLE SQL

↓

Role Created in System

↓

Grant Privileges to Role

↓

Assign Role to Users

↓

Users Can Use Role Permissions

↓

End

The flow shows creating a custom role, granting privileges, and assigning it to users so they can use the permissions.

Execution Sample

Snowflake

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

This code creates a role named 'analyst', grants it select access on a database, and assigns the role to user 'alice'.

Process Table

Step	Action	SQL Executed	System State Change	Result
1	Create role	CREATE ROLE analyst;	Role 'analyst' added to roles list	Role 'analyst' exists with no privileges
2	Grant privilege	GRANT SELECT ON DATABASE sales_db TO ROLE analyst;	Role 'analyst' granted SELECT on sales_db	Role 'analyst' can SELECT from sales_db
3	Assign role to user	GRANT ROLE analyst TO USER alice;	User 'alice' assigned role 'analyst'	User 'alice' can use 'analyst' permissions
4	End		No further changes	Setup complete

💡 All steps executed successfully; custom role created and assigned.

Status Tracker

Variable	Start	After Step 1	After Step 2	After Step 3	Final
roles_list	[]	['analyst']	['analyst']	['analyst']	['analyst']
analyst_privileges	{}	{}	{'SELECT on sales_db'}	{'SELECT on sales_db'}	{'SELECT on sales_db'}
alice_roles	[]	[]	[]	['analyst']	['analyst']

Key Moments - 2 Insights

Why does the role have no privileges immediately after creation?

Because creating a role only defines it; privileges must be granted separately as shown in step 2 of the execution_table.

Can a user use a role's privileges before the role is assigned to them?

No, as shown in step 3, the user must be assigned the role to use its permissions.

Visual Quiz - 3 Questions

Look at the execution_table, what is the system state after step 2?

A Role 'analyst' exists but has no privileges

B User 'alice' assigned role 'analyst'

C Role 'analyst' granted SELECT on sales_db

D No roles exist yet

💡 Hint

Check the 'System State Change' column in row 2 of execution_table.

At which step does user 'alice' gain the ability to use the 'analyst' role?

A Step 3

B Step 1

C Step 2

D Step 4

💡 Hint

Look at the 'Result' column for when 'alice' is assigned the role.

If we skip granting privileges in step 2, what would be the effect?

A User 'alice' could still SELECT from sales_db

B Role 'analyst' would have no privileges

C Role 'analyst' would have default privileges

D User 'alice' would be unable to log in

💡 Hint

Refer to the 'key_moments' about role privileges after creation.

Concept Snapshot

CREATE ROLE role_name;
GRANT privileges TO ROLE role_name;
GRANT ROLE role_name TO USER user_name;

Roles start empty; privileges must be granted.
Users gain permissions only after role assignment.

Full Transcript

Creating custom roles in Snowflake involves three main steps. First, you create the role with CREATE ROLE. This adds the role to the system but it has no permissions yet. Next, you grant privileges like SELECT on databases to the role. Finally, you assign the role to users so they can use those permissions. The execution table shows each step's SQL command and how the system state changes. Variables track roles, privileges, and user assignments. Key moments clarify that roles start empty and users need role assignment to gain permissions. The quiz tests understanding of when privileges are granted and when users gain access.