Cannot access new tables in Snowflake as it doesn't inherit the Roles

In Snowflake, Granting the SELECT privilege on all tables in a schema only applies to existing tables. When new tables are added, privileges on those tables must be granted to the role. The privileges aren't granted automatically. 

So after creating the table, you will also have to run the GRANT command:

For example,

GRANT SELECT ON ALL TABLES IN SCHEMA dbtest.schema1 TO ROLE role_name;

For example: If we have Sales_DB and a ROLE Sales_DB_READONLY,

GRANT SELECT ON ALL TABLES IN SCHEMA dbtest.Sales_DB TO ROLE Sales_DB_READONLY;

Note: If you alter a table to modify columns, any privileges granted on the table are preserved.

To avoid the need to run additional GRANT commands, you could create a role hierarchy in which schema objects created by one role are available to another role through privilege inheritance

Atori

posted on 24 Oct 18