How to Create Role in Snowflake: Syntax and Example
To create a role in Snowflake, use the
CREATE ROLE role_name; command. This command adds a new role that you can assign privileges to and grant to users.Syntax
The basic syntax to create a role in Snowflake is simple:
CREATE ROLE: The command to create a new role.role_name: The name you want to give your new role.
This command must be run by a user with the SECURITYADMIN role or higher.
sql
CREATE ROLE role_name;Example
This example creates a role named data_analyst. After creating the role, you can grant it privileges or assign it to users.
sql
CREATE ROLE data_analyst;Output
Role 'DATA_ANALYST' successfully created.
Common Pitfalls
Common mistakes when creating roles include:
- Trying to create a role without the necessary privileges (you need
SECURITYADMINor higher). - Using invalid characters or spaces in the role name.
- Not granting the role to users after creation, so it has no effect.
Always check your current role with SELECT CURRENT_ROLE(); before creating roles.
sql
/* Wrong: Creating role without proper privileges */ CREATE ROLE new_role; /* Right: Switch to SECURITYADMIN role first */ USE ROLE SECURITYADMIN; CREATE ROLE new_role;
Quick Reference
| Command | Description |
|---|---|
| CREATE ROLE role_name; | Creates a new role with the specified name. |
| GRANT ROLE role_name TO USER user_name; | Assigns the role to a user. |
| SHOW ROLES; | Lists all roles in the account. |
| USE ROLE role_name; | Switches to the specified role. |
Key Takeaways
Use the command CREATE ROLE role_name; to create a new role in Snowflake.
You must have SECURITYADMIN role or higher to create roles.
Role names should be simple and avoid spaces or special characters.
After creating a role, grant it to users to apply permissions.
Check your current role with SELECT CURRENT_ROLE(); before creating roles.
