Bird
Raised Fist0
SnowflakeHow-ToBeginner · 3 min read

How to Create a Custom Role in Snowflake: Step-by-Step Guide

To create a custom role in Snowflake, use the CREATE ROLE role_name; command. Then, assign privileges to this role and grant it to users with GRANT ROLE role_name TO USER user_name;.
📐

Syntax

The basic syntax to create a custom role in Snowflake is:

  • CREATE ROLE role_name; - creates a new role with the specified name.
  • GRANT privilege ON object TO ROLE role_name; - assigns specific permissions to the role.
  • GRANT ROLE role_name TO USER user_name; - assigns the role to a user.
sql
CREATE ROLE role_name;
💻

Example

This example shows how to create a custom role called data_analyst, grant it SELECT permission on a database schema, and assign it to a user.

sql
CREATE ROLE data_analyst;
GRANT USAGE ON DATABASE my_database TO ROLE data_analyst;
GRANT USAGE ON SCHEMA my_database.public TO ROLE data_analyst;
GRANT SELECT ON ALL TABLES IN SCHEMA my_database.public TO ROLE data_analyst;
GRANT ROLE data_analyst TO USER alice;
Output
Role 'data_analyst' created. Privileges granted to role 'data_analyst'. Role 'data_analyst' granted to user 'alice'.
⚠️

Common Pitfalls

Common mistakes when creating custom roles in Snowflake include:

  • Not granting necessary privileges to the role, so users cannot perform expected actions.
  • Forgetting to grant the role to users, leaving them without access.
  • Granting privileges directly to users instead of roles, which complicates permission management.

Always grant privileges to roles and then assign roles to users for better control.

sql
/* Wrong: Granting privilege directly to user */
GRANT SELECT ON TABLE my_database.public.sales TO USER alice;

/* Right: Granting privilege to role and role to user */
GRANT SELECT ON TABLE my_database.public.sales TO ROLE data_analyst;
GRANT ROLE data_analyst TO USER alice;
📊

Quick Reference

CommandPurpose
CREATE ROLE role_name;Create a new custom role
GRANT privilege ON object TO ROLE role_name;Assign permissions to the role
GRANT ROLE role_name TO USER user_name;Assign the role to a user
REVOKE ROLE role_name FROM USER user_name;Remove role from a user
DROP ROLE role_name;Delete the role
✅

Key Takeaways

Create custom roles with CREATE ROLE to manage permissions easily.
Always grant privileges to roles, not directly to users.
Assign roles to users with GRANT ROLE to enable access.
Check that roles have all needed privileges before assigning.
Use REVOKE and DROP commands to manage roles lifecycle.