Bird
Raised Fist0
SnowflakeHow-ToBeginner · 3 min read

How to Grant Access on Database in Snowflake: Simple Guide

To grant access on a database in Snowflake, use the GRANT command with the desired privileges on the database to a role, like GRANT USAGE ON DATABASE my_db TO ROLE my_role;. This allows the role to access the database and its objects based on the privileges granted.
📐

Syntax

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

  • GRANT <privilege> ON DATABASE <database_name> TO ROLE <role_name>;
  • privilege: The type of access, commonly USAGE to allow access to the database.
  • database_name: The name of the database you want to grant access to.
  • role_name: The role that will receive the access rights.
sql
GRANT USAGE ON DATABASE my_database TO ROLE my_role;
💻

Example

This example shows how to grant the USAGE privilege on a database named sales_db to a role named analyst_role. This lets users with analyst_role access the database and its objects.

sql
GRANT USAGE ON DATABASE sales_db TO ROLE analyst_role;
Output
Grant succeeded.
⚠️

Common Pitfalls

Common mistakes when granting access on a database in Snowflake include:

  • Granting privileges to a user instead of a role. Snowflake manages access through roles, so always grant to roles.
  • Forgetting to grant USAGE on the database before granting privileges on schemas or tables inside it.
  • Not assigning the role to the user after granting privileges, so the user cannot use the access.
sql
/* Wrong: Granting to user directly (not recommended) */
GRANT USAGE ON DATABASE sales_db TO USER analyst_user;

/* Right: Granting to role and then assigning role to user */
GRANT USAGE ON DATABASE sales_db TO ROLE analyst_role;
GRANT ROLE analyst_role TO USER analyst_user;
Output
Grant succeeded. Grant succeeded.
📊

Quick Reference

CommandDescription
GRANT USAGE ON DATABASE TO ROLE ;Allows role to access the database.
GRANT SELECT ON ALL TABLES IN SCHEMA TO ROLE ;Grants read access on all tables in a schema.
GRANT ROLE TO USER ;Assigns the role to a user to enable access.
✅

Key Takeaways

Always grant database access to roles, not directly to users.
Use GRANT USAGE ON DATABASE to allow a role to access the database.
Assign the role to users after granting privileges for access to work.
Grant USAGE on the database before granting privileges on schemas or tables inside it.
Check for successful grant messages to confirm access was given.