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
| Command | Purpose |
|---|---|
| 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.
