How to grant select on all future tables in a schema and database level

To Nha Notes | July 1, 2022, 1:10 p.m.

We must grant access to the object, to all of the containers in the object, and to a warehouse that will do the heavy lifting involved in executing a query.

GRANT <PRIVILEGE> ON <OBJECT TYPE> <OBJECT NAME> TO ROLE <ROLE NAME>;

GRANT USAGE ON DATABASE PROD TO ROLE SNOWFLAKE_USER;

GRANT USAGE ON SCHEMA HR TO ROLE SNOWFLAKE_USER;

GRANT USAGE ON WAREHOUSE HR_WAREHOUSE TO ROLE SNOWFLAKE_USER;

GRANT SELECT ON TABLE PROD.HR.EMPLOYEES TO ROLE SNOWFLAKE_USER;

For example: Let's assume we have a database MY_DB, schema MY_SCHEMA  and a test role MY_ROLE already created, then
 

USE ROLE ACCOUNTADMIN;

-- database grants
GRANT USAGE ON DATABASE MY_DB TO ROLE MY_ROLE;

-- schema grants
GRANT ALL ON ALL SCHEMAS IN DATABASE MY_DB TO ROLE MY_ROLE;
GRANT ALL ON FUTURE SCHEMAS IN DATABASE MY_DB TO ROLE MY_ROLE;

-- table grants
GRANT ALL ON ALL TABLES IN DATABASE MY_DB TO ROLE MY_ROLE;
GRANT ALL ON ALL TABLES IN SCHEMA MY_DB.MY_ROLE TO ROLE MY_ROLE;

GRANT ALL ON FUTURE TABLES IN DATABASE MY_DB TO ROLE MY_ROLE;
GRANT ALL ON FUTURE TABLES IN SCHEMA MY_DB.MY_ROLE TO ROLE MY_ROLE;

-- warehouse grants
GRANT USAGE ON WAREHOUSE MY_DB TO ROLE MY_ROLE;

The above will grant select on all tables within a schema but to grant select to all tables in a database one should:

use role accountadmin;
grant usage on database MY_DB to role TEST_ROLE;
grant usage on all schemas in database MY_DB  to role TEST_ROLE;
grant select on all tables in database MY_DB to role TEST_ROLE;

Data Engineering Platform Privileges

• CREATE STAGE

• CREATE FILE FORMAT

• CREATE SEQUENCE

• CREATE FUNCTION

• CREATE PIPE

• CREATE STREAM

• CREATE TASK

• CREATE PROCEDURE

• CREATE MASKING POLICY*

• CREATE ROW ACCESS POLICY*

References:

https://community.snowflake.com/s/article/How-to-grant-select-on-all-future-tables-in-a-schema-and-database-level