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
USAGEto 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
USAGEon 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
| Command | Description |
|---|---|
| GRANT USAGE ON DATABASE | Allows role to access the database. |
| GRANT SELECT ON ALL TABLES IN SCHEMA | Grants read access on all tables in a schema. |
| GRANT ROLE | 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.
