Bird
Raised Fist0
SnowflakeHow-ToBeginner · 3 min read

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 USAGE or ALL 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 USAGE alone 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 OWNERSHIP or 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

PrivilegeDescription
USAGEAllows access to the schema to see and use objects inside.
CREATEAllows creating new objects in the schema.
ALL PRIVILEGESGrants 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.