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
USAGEprivilege 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
| Privilege | Description |
|---|---|
| SELECT | Read data from the table |
| INSERT | Add new rows to the table |
| UPDATE | Modify existing rows in the table |
| DELETE | Remove rows from the table |
| REFERENCES | Create 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.
