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:
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
Read great educational content like this and a lot more !
Members get free exclusive access to content, new courses, and discounts. Signup for a free account to write a post / comment / upvote posts. Creating an account takes less than 5 seconds and you can start earning badges & points too