snowflake-cloud-data-platformsnowflake-schema

How to access 2 roles at a time in snowflake

H All,

I'm encountering an issue with performing joins across tables that belong to different roles in our Snowflake environment.

Here's the scenario:

We have two roles: role_1, which has access to TableA, and role_2, which has access to TableB. We've assigned both role_1 and role_2 to a user. Now, the user needs to perform a join operation between TableA and TableB. However, due to the limitations of only being able to access one role at a time, they're encountering an error.

Solution

  • You could use the secondary roles. This helps you use the privileges available in two different roles within the same session through a single role.

    USE SECONDARY ROLES { ALL }

    https://docs.snowflake.com/en/sql-reference/sql/use-secondary-roles