How to Grant Access on Schema in Snowflake: Simple Guide
To grant access on a schema in Snowflake, use the
GRANT command with the desired privileges on the schema to a role. For example, GRANT USAGE ON SCHEMA mydb.myschema TO ROLE myrole; allows the role to use the schema.Syntax
The GRANT command in Snowflake is used to give privileges on database objects like schemas. The basic syntax to grant access on a schema is:
GRANT <privilege> ON SCHEMA <database>.<schema> TO ROLE <role_name>;- privilege: The type of access, such as
USAGEorALL PRIVILEGES. - database.schema: The full name of the schema you want to grant access to.
- role_name: The Snowflake role that will receive the access.
sql
GRANT USAGE ON SCHEMA mydatabase.myschema TO ROLE myrole;Example
This example shows how to grant the USAGE privilege on a schema named sales in the database company_db to a role called analyst_role. The USAGE privilege allows the role to access objects inside the schema but not modify them.
sql
GRANT USAGE ON SCHEMA company_db.sales TO ROLE analyst_role; -- To verify, you can run: SHOW GRANTS ON SCHEMA company_db.sales;
Output
privilege | granted_on | name | granted_to | grantee_name
----------|------------|----------------|------------|--------------
USAGE | SCHEMA | company_db.sales | ROLE | analyst_role
Common Pitfalls
Common mistakes when granting schema access include:
- Granting
USAGEalone does not allow reading or writing data; you must also grant privileges on the tables inside the schema. - For full control, use
ALL PRIVILEGES, but be careful as it grants broad rights. - Not specifying the correct role or schema name causes errors.
- Remember that roles must have the
OWNERSHIPor be granted privileges by a role with sufficient rights.
sql
/* Wrong: Grants USAGE but no table access */ GRANT USAGE ON SCHEMA company_db.sales TO ROLE analyst_role; /* Right: Also grant SELECT on tables */ GRANT SELECT ON ALL TABLES IN SCHEMA company_db.sales TO ROLE analyst_role;
Quick Reference
| Privilege | Description |
|---|---|
| USAGE | Allows access to the schema to see and use objects inside. |
| CREATE | Allows creating new objects in the schema. |
| ALL PRIVILEGES | Grants all available privileges on the schema. |
| SELECT (on tables) | Allows reading data from tables inside the schema. |
Key Takeaways
Use GRANT USAGE ON SCHEMA to allow a role to access the schema.
Grant additional privileges on tables inside the schema for data access.
Specify the full database and schema name when granting access.
Roles must have proper rights to grant privileges.
ALL PRIVILEGES grants broad rights; use carefully.
