How to Grant Privilege in Snowflake: Syntax and Examples
In Snowflake, you grant privileges using the
GRANT statement followed by the privilege type, the object, and the role to receive it. For example, GRANT SELECT ON TABLE my_table TO ROLE analyst; grants the SELECT privilege on a table to a role.Syntax
The basic syntax to grant a privilege in Snowflake is:
- GRANT: The command to give access.
- privilege: The specific right like SELECT, INSERT, or USAGE.
- ON object_type object_name: The object you want to grant access to, such as a table, schema, or database.
- TO ROLE role_name: The role that will receive the privilege.
sql
GRANT privilege ON object_type object_name TO ROLE role_name;Example
This example grants the SELECT privilege on a table named sales_data to a role called data_analyst. This allows users with the data_analyst role to read data from the table.
sql
GRANT SELECT ON TABLE sales_data TO ROLE data_analyst;
Output
Grant succeeded.
Common Pitfalls
Common mistakes when granting privileges include:
- Trying to grant privileges to a user instead of a role. Snowflake manages access through roles, so always grant to roles.
- Forgetting to specify the correct object type (TABLE, SCHEMA, DATABASE).
- Not having the necessary privileges yourself to grant access.
- Assuming privileges cascade automatically; some privileges need to be granted explicitly on each object.
sql
/* Wrong: Granting directly to a user (not allowed) */ GRANT SELECT ON TABLE sales_data TO USER john_doe; /* Right: Granting to a role */ GRANT SELECT ON TABLE sales_data TO ROLE data_analyst;
Output
Error: Cannot grant privileges to USER.
Grant succeeded.
Quick Reference
| Privilege | Description | Common Object Types |
|---|---|---|
| SELECT | Allows reading data | TABLE, VIEW |
| INSERT | Allows adding data | TABLE |
| USAGE | Allows using the object | DATABASE, SCHEMA, WAREHOUSE |
| OWNERSHIP | Full control including grant rights | Any object |
Key Takeaways
Always grant privileges to roles, not directly to users in Snowflake.
Use the GRANT statement with the correct privilege, object type, and role name.
Check you have the right permissions before granting privileges.
Privileges like SELECT or USAGE must be granted explicitly on each object.
OWNERSHIP privilege gives full control and can be transferred.
