How to Use Future Grants in Snowflake for Automatic Privilege Management
In Snowflake, use
GRANT ... ON FUTURE to automatically assign privileges on objects created later in a database or schema. This ensures users or roles get access to new tables, views, or schemas without manual updates.Syntax
The GRANT ... ON FUTURE statement lets you assign privileges on objects that will be created in the future within a database or schema.
Parts explained:
privilege: The access right like SELECT, INSERT, USAGE.ON FUTURE TABLES: Applies to all tables created later in the specified schema.IN SCHEMAorIN DATABASE: Scope where future objects will be created.TO ROLE: The role receiving the privileges.
sql
GRANT privilege ON FUTURE object_type IN {SCHEMA schema_name | DATABASE database_name} TO ROLE role_name;
Example
This example grants the SELECT privilege on all future tables created in the sales schema to the role analyst_role. This means whenever a new table is added to sales, analyst_role can query it automatically.
sql
GRANT SELECT ON FUTURE TABLES IN SCHEMA sales TO ROLE analyst_role;
Output
Grant succeeded.
Common Pitfalls
Common mistakes when using future grants include:
- Granting on the wrong scope (database vs schema) which may not cover intended objects.
- Forgetting to grant
USAGEon the database or schema, which is needed to access objects. - Expecting future grants to apply retroactively to existing objects; they only apply to new objects created after the grant.
Example of a wrong and right approach:
sql
-- Wrong: Grants SELECT on future tables in database but tables are created in a schema GRANT SELECT ON FUTURE TABLES IN DATABASE sales_db TO ROLE analyst_role; -- Right: Grants SELECT on future tables in the specific schema GRANT SELECT ON FUTURE TABLES IN SCHEMA sales_db.public TO ROLE analyst_role;
Quick Reference
| Clause | Description |
|---|---|
| GRANT privilege | The access right to assign (e.g., SELECT, INSERT, USAGE) |
| ON FUTURE object_type | Applies to objects created later (TABLES, VIEWS, SCHEMAS) |
| IN SCHEMA schema_name | Scope limited to a specific schema |
| IN DATABASE database_name | Scope limited to entire database |
| TO ROLE role_name | Role receiving the privileges |
Key Takeaways
Use
GRANT ... ON FUTURE to automatically assign privileges on new objects.Future grants apply only to objects created after the grant, not existing ones.
Always grant
USAGE on the database or schema to enable access.Specify the correct scope (schema or database) to cover intended objects.
Future grants simplify privilege management for evolving databases.
