Skip to main content

Future Grants – Automating Permissions

Managing security manually in an environment where objects are constantly created is a nightmare. If a Data Engineer drops and recreates a table, or creates a new table every day for daily logs, an administrator shouldn't need to manually grant SELECT access to the Analyst role every single time.

This is the exact problem Future Grants solve.

What are Future Grants?​

A Future Grant is a rule that automatically applies privileges to new objects created in the future within a specific database or schema.

When you apply a future grant, any object created matching the grant definition will automatically be accessible to the specified role, saving significant administrative overhead.

Hands-On Demonstration: Implementing Future Grants​

Let's see how Future Grants behave in practice by setting up an environment where a Data Engineer creates tables, and a Data Analyst automatically receives read access to them.

1. Environment Setup​

Run this in your Snowflake worksheet to prepare the environment.

USE ROLE SECURITYADMIN;

-- Create our functional roles
CREATE OR REPLACE ROLE DE_ROLE;
CREATE OR REPLACE ROLE DA_ROLE;

USE ROLE SYSADMIN;

-- Create Database and Schema
CREATE DATABASE IF NOT EXISTS BEEMS_TRAINING_DB;
CREATE SCHEMA IF NOT EXISTS BEEMS_TRAINING_DB.SALES_DATA;

-- Grant basic USAGE so the roles can enter the schema
GRANT USAGE ON DATABASE BEEMS_TRAINING_DB TO ROLE DE_ROLE;
GRANT USAGE ON SCHEMA BEEMS_TRAINING_DB.SALES_DATA TO ROLE DE_ROLE;
GRANT CREATE TABLE ON SCHEMA BEEMS_TRAINING_DB.SALES_DATA TO ROLE DE_ROLE;

GRANT USAGE ON DATABASE BEEMS_TRAINING_DB TO ROLE DA_ROLE;
GRANT USAGE ON SCHEMA BEEMS_TRAINING_DB.SALES_DATA TO ROLE DA_ROLE;

2. The Problem: Manual Grants​

First, let's see what happens without future grants.

-- Data Engineer creates a new table
USE ROLE DE_ROLE;
CREATE OR REPLACE TABLE BEEMS_TRAINING_DB.SALES_DATA.Q1_REVENUE (
REGION STRING,
REVENUE NUMBER(10,2)
);
INSERT INTO BEEMS_TRAINING_DB.SALES_DATA.Q1_REVENUE VALUES ('North America', 50000);

-- Data Analyst tries to read it
USE ROLE DA_ROLE;
-- This will FAIL: "Object does not exist or not authorized"
-- SELECT * FROM BEEMS_TRAINING_DB.SALES_DATA.Q1_REVENUE;

For the analyst to read it, the owner (DE_ROLE) or an admin would need to explicitly run GRANT SELECT ON TABLE....

3. The Solution: Future Grants​

Let's apply a future grant so the Data Analyst (DA_ROLE) automatically gets SELECT access to all new tables created in the SALES_DATA schema.

USE ROLE SECURITYADMIN;

-- Apply the Future Grant
GRANT SELECT ON FUTURE TABLES IN SCHEMA BEEMS_TRAINING_DB.SALES_DATA TO ROLE DA_ROLE;

-- NOTE: Future grants ONLY apply to objects created AFTER the grant is issued.
-- We must still manually grant access to existing tables.
GRANT SELECT ON ALL TABLES IN SCHEMA BEEMS_TRAINING_DB.SALES_DATA TO ROLE DA_ROLE;

4. Testing the Automation​

Now the Data Engineer will create a new table, and we will verify that the Data Analyst can read it automatically.

-- Data Engineer creates a new table
USE ROLE DE_ROLE;
CREATE OR REPLACE TABLE BEEMS_TRAINING_DB.SALES_DATA.Q2_REVENUE (
REGION STRING,
REVENUE NUMBER(10,2)
);
INSERT INTO BEEMS_TRAINING_DB.SALES_DATA.Q2_REVENUE VALUES ('Europe', 75000);

-- Data Analyst tries to read it
USE ROLE DA_ROLE;
-- This will SUCCEED automatically because of the future grant!
SELECT * FROM BEEMS_TRAINING_DB.SALES_DATA.Q2_REVENUE;

Database vs Schema Level Grants​

Future grants can be applied at the Database level or the Schema level.

  • Schema Level: GRANT SELECT ON FUTURE TABLES IN SCHEMA <schema_name> TO ROLE <role_name>;
  • Database Level: GRANT SELECT ON FUTURE TABLES IN DATABASE <db_name> TO ROLE <role_name>;

Important Note on Interaction: Schema-level future grants take precedence over database-level future grants. If a database-level future grant grants SELECT to ROLE_A, but a schema-level future grant inside that database grants SELECT to ROLE_B, only ROLE_B will get the grant for objects in that schema. The database-level grant is ignored for that specific schema.

Viewing and Revoking Future Grants​

To audit what future grants exist, use the SHOW FUTURE GRANTS command.

USE ROLE SECURITYADMIN;

-- View future grants in a specific schema
SHOW FUTURE GRANTS IN SCHEMA BEEMS_TRAINING_DB.SALES_DATA;

-- Revoke the future grant
REVOKE SELECT ON FUTURE TABLES IN SCHEMA BEEMS_TRAINING_DB.SALES_DATA FROM ROLE DA_ROLE;

(Note: Revoking a future grant prevents new tables from automatically granting access, but it does NOT remove access from tables that were already created).

Summary​

  • Future Grants automate access control by automatically applying privileges to newly created objects.
  • They do not apply retroactively to existing objects. You must use GRANT ... ON ALL TABLES to catch up existing objects.
  • Schema-level future grants override database-level future grants.
  • Use SHOW FUTURE GRANTS to audit automated permission rules.