Why Snowflake future grants will eventually break

Photo by Masaaki Komori on Unsplash

TLDR

Future grants at the database level (e.g. grant select on future tables in database ...) work until you execute future grants at the schema level (e.g grant select on future tables in schema ...) within the same database.

Here’s the setup…

You’ve just created a new role called MyFirstRole .

create role MyFirstRole;

The plan is to give this role full read access to some database, call it MyDatabase . We’d like this role to select from all current tables and views in every schema within MyDatabase.

grant usage on database MyDatabase to role MyFirstRole;
grant usage on all schemas in database MyDatabase to role MyFirstRole;
grant select on all tables in database MyDatabase to role MyFirstRole;
grant select on all views in database MyDatabase to role MyFirstRole;

With these statements in place, our role can now select from everything in MyDatabase.

However, new tables and views will be created in the database as time passes. It’s impractical to manually grant select privileges to MyFirstRole every time a new object is created. Luckily, future grants can solve this issue for us.

grant usage on future schemas in database MyDatabase to role MyFirstRole;
grant…