Bird
Raised Fist0
SnowflakeHow-ToBeginner · 3 min read

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

PrivilegeDescriptionCommon Object Types
SELECTAllows reading dataTABLE, VIEW
INSERTAllows adding dataTABLE
USAGEAllows using the objectDATABASE, SCHEMA, WAREHOUSE
OWNERSHIPFull control including grant rightsAny 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.