4
0
Future Grants Management : While working on the Database and Tables inside the Snowflake, a question comes to our mind, Without having to grant permission manually/programmatically again and again on new objects, How can I give a USER read access to all or specific tables in schema, including all tables that will be created in the future.
Snowflake Future grants allow defining an initial set of privileges to grant on new (i.e. future) objects of a certain type in a database or a schema. As soon as the new objects created inside the Database/schema the predefined set of privileges would be assign on the object automatically without any manual intervention.
Future grants only define the initial set of privileges and after an individual object is created, administrators can explicitly grant additional privileges or revoke privileges on the object.
Points to remember:
Points to remember:
- Future grant defined at Database level on object (Table, Schema, Views etc..) would be applicable to all the objects created in future.
- When future grant defines at both the database and schema level, the schema level grants take precedence over the database level grant.
The following permissions are require to grant or revoke privileges on future objects:
Future grants are just a template for future object creation and information is not available in the views of INFORMATION_SCHEMA or ACCOUNT_USAGE. Future grants given to a role ,then as soon as the new object is created, the grant information will be automatically available in INFORMATION_SCHEMA or ACCOUNT_USAGE but before the creation of objects, this information cannot be seen in these views.
To get the information on Future Grant, use SHOW command….
SHOW FUTURE GRANTS IN SCHEMA “schema_name”
SHOW FUTURE GRANTS IN DATABASE “Database_name”
Now try to understand the Behavior Of Future Grants with practical example:
Create new Database, Test Schema and Roles.
create database FUTURE_DB;
create or replace role REP;
create schema FUTURE_SCHEMA1;
Grant required privileges along with SELECT ON FUTURE TABLES at Database level.
grant usage,monitor on database FUTURE_DB to role REP;
grant usage on warehouse COMPUTE_WH to role REP;
grant usage on schema FUTURE_DB.FUTURE_SCHEMA1 to role REP;
grant select on all tables in database FUTURE_DB to role REP;
grant select on future tables in database FUTURE_DB to role REP;
Create a future table.
create table test (col1 varchar);
insert into test values (‘abc’);
Change the Role to REP:
Run SELECT on table TEST. This will work because at this moment, the future grants are defined only at the Database level:
To validate the FUTURE GRANTS at SCHEMA level:
To validate the FUTURE GRANTS at SCHEMA level:
Create new Role FLM.
create or replace role FLM;
GRANT SELECT ON FUTURE TABLES at SCHEMA level to new role FLM.
grant usage,monitor on database FUTURE_DB to role FLM;
grant usage on warehouse COMPUTE_WH to role FLM;
grant usage on schema FUTURE_DB.FUTURE_SCHEMA1 to role FLM;
grant select on future tables in schema FUTURE_DB.FUTURE_SCHEMA1 to role FLM;
grant role FLM to role accountadmin;
Create another future table.
create table test1 (col1 varchar);
insert into test1 values (‘abc’);
Change the Role to FLM:
Run SELECT on table TEST1. This will work because FLM has required privilege’s.
Now Change the Role to REP:
Run SELECT on table TEST1. This will not work even Role REP has future grants:
Conclusion:
Above Future Grants Management scenario explains that even though REP has future grant at database level, the role was not able to do select on future tables i.e. TEST1. Since future grant also defined on schema to role FLM and as per the behavior, the schema level grants take precedence over the database level grants. Hence we are able to run SELECT on future tables using FLM but not with REP Role.
is it possible to have a future access on a single table i.e., we have provided DML access on a table to a role and using some other role (with DDL access) it was recreated and lost the access.
Can we have a solution for such type of scenarios where we tend provide access on restricted at object level to role for business users.
Hi Rajeh, FUTURE grants can not restrict to specific table. As FUTURE grants indicates that the tables created in future under schema or DB would be granted minimum privileges. Also beforehand how we come to know that the name of Table created by ROLE . The name can be vary and can not control at first step. Yes with future grants you can provide the minimum or restricted privileges like SELECT only.