In Snowflake, how to correctly grant read access to a role on database created and edited by another role?

user_4898141 0
Viewed 869

I’ve setup the user and role access as shown below. I only included the SYSADMIN tree, as the rest is default.

enter image description here

Problem description: I’m trying to set it up such that DEV_ROLE can write new data into DEV, and TABLEAU_ROLE should be able to READ data from DEV. Problem is when I create a new table in DEV (eg. DEV.LUDWIG.DELIVERY_STOCK_ITEM). This table is not visible from the TABLEAU_ROLE.

My role setup.

  • I created my warehouses and databases using SYSADMIN

  • I created my role using SECURITYADMIN, and granted access using SECURITYADMIN

  • Privileges granted to TABLEAU_ROLE are as shown below:

    GRANT USAGE ON DATABASE DEV TO ROLE TABLEAU_ROLE;

    GRANT USAGE ON ALL SCHEMAS IN DATABASE DEV TO ROLE TABLEAU_ROLE;

    GRANT USAGE ON FUTURE SCHEMAS IN DATABASE DEV TO ROLE TABLEAU_ROLE;

    GRANT SELECT ON ALL TABLES IN DATABASE DEV TO ROLE TABLEAU_ROLE;

    GRANT SELECT ON FUTURE TABLES IN DATABASE DEV TO ROLE TABLEAU_ROLE;

I am quite confused why TABLEAU_ROLE is not seeing the table created by DEV_ROLE in DEV, even though TABLEU_ROLE has been granted access to all current and future tables within DEV.

Any ideas?

snowflake-cloud-data-platform
1 Answers

Sorry guys. I found out the problem!

Turns out the "Table" I created wasn't a "table", but was a "view", and I never granted any privileges to the "view"

Added the following two lines and everything is working now.

GRANT SELECT ON ALL VIEWS IN DATABASE DEV TO ROLE TABLEAU_ROLE;

GRANT SELECT ON FUTURE VIEWS IN DATABASE DEV TO ROLE TABLEAU_ROLE;
user_48981410
Related