Bird
Raised Fist0
SnowflakeHow-ToBeginner · 3 min read

How to Grant Access on Table in Snowflake: Simple Guide

To grant access on a table in Snowflake, use the GRANT command followed by the privilege and the table name, then specify the role to receive access with TO ROLE. For example, GRANT SELECT ON TABLE my_table TO ROLE analyst; gives the analyst role permission to read the table.
📐

Syntax

The basic syntax to grant access on a table in Snowflake is:

  • GRANT <privilege> ON TABLE <table_name> TO ROLE <role_name>;
  • privilege: The type of access, like SELECT, INSERT, UPDATE, DELETE.
  • table_name: The name of the table you want to grant access to.
  • role_name: The Snowflake role that will receive the access.
sql
GRANT SELECT ON TABLE my_table TO ROLE analyst;
💻

Example

This example shows how to grant read access (SELECT) on a table named sales_data to a role called data_analyst. The role can then query the table.

sql
GRANT SELECT ON TABLE sales_data TO ROLE data_analyst;
Output
Grant succeeded.
⚠️

Common Pitfalls

Common mistakes when granting access on tables include:

  • Trying to grant privileges to a user instead of a role. Snowflake manages access by roles, so always grant to roles.
  • Forgetting to grant the USAGE privilege on the database and schema containing the table, which is needed to see the table.
  • Using incorrect privilege names or misspelling table or role names.
sql
/* Wrong: granting to user (not supported) */
-- GRANT SELECT ON TABLE sales_data TO USER john_doe;

/* Right: grant to role */
GRANT SELECT ON TABLE sales_data TO ROLE data_analyst;
📊

Quick Reference

PrivilegeDescription
SELECTRead data from the table
INSERTAdd new rows to the table
UPDATEModify existing rows in the table
DELETERemove rows from the table
REFERENCESCreate foreign key references to the table
✅

Key Takeaways

Always grant table access to roles, not directly to users.
Use the GRANT command with the correct privilege and table name.
Remember to grant USAGE on the database and schema for access to work.
Common privileges include SELECT, INSERT, UPDATE, and DELETE.
Check for typos in role and table names to avoid errors.