3
0
During this post we will discuss about Primary roles and Secondary Roles. Primary Role is your current role. The Role we select by using USE <<ROLE>> or the default role assigned to the user at the time of creation. Sometimes when there is no role assign a default PUBLIC role assigns to the user.
In addition to it, Snowflake has introduced the concept of Secondary roles as well. A user can have one or more Roles. The user has access to all the objects in the union of all the Roles assigned. For example, you could assign a sales manager both the Sales Manager Role and the Field Sales Representative role.
Syntax for using Secondary roles:
USE SECONDARY ROLES { ALL | NONE }
Parameters
ALL: All roles that have been granted to the user in addition to the current active primary role. Note that the set of roles gets reevaluate when each SQL statement executes. If additional roles grants to the user, and that user executes a new SQL statement, the newly granted roles are active secondary roles for the new SQL statement.
NONE: Disables secondary roles. The authorization for all SQL actions is provided via the primary role.
Consider the scenario we have three different roles Analyst_Role ,Tester_Role and Developer Role. Base on Business Requirement, every role is authorized to query its own table only. All three Roles are assigned to ADMIN_USER.
ADMIN_USER wants to query the table belongs to TESTER_ROLE. In this case user has to switch the Role from current Role i.e ANALYST_ROLE to TESTER_ROLE. On the similar lines if user wants to view the table belongs to DEVLOPER_ROLE ,user has to select the desired Role. As per the below Screenshot, even though all three roles are assign to User but querying the table belongs to Secondary Role issue the error.
Multiple Roles:
Assuming we have multiple Roles in system and each Role holds multiple tables then it would be tedious for User to switch the Role frequently. Here the Secondary Roles plays a significance Role.
Run the Following Command in WEBUI:
We can clearly see DEVELOPER and TESTER Roles are assign to user as Secondary Roles.
Now try to run the same CUSTOM_TABLE query from the WEBUI and verify the result.
Therefore, User has access to all the objects by UNION of all ROLES assign to it. User need not to remember which particular Role holds which table. He can directly query the table without switching to multiple roles.
