SnowPro Advanced: Administrator — Question 5

A user has built a view named DB.VWS.TEST_VIEW in Snowflake which only allows users having ROLE_XYZ to retrieve data from the view.
Roles Setup:
GRANT USAGE ON DATABASE DB TO ROLE ROLE_ALPHA;
GRANT USAGE ON SCHEMA DB.VWS TO ROLE ROLE_ALPHA;
GRANT SELECT ON VIEW TEST_VIEW IN SCHEMA DB.VWS TO ROLE ROLE_ALPHA;
GRANT ROLE ROLE_ALPHA TO ROLE ROLE_XYZ;
GRANT ROLE ROLE_XYZ TO ROLE ROLE_BETA;
GRANT ROLE ROLE_ALPHA TO USER USER1;
GRANT ROLE ROLE_BETA TO USER USER2;
GRANT ROLE ROLE_XYZ TO USER USER3;
View definition:

CREATE VIEW DB.VWS.TEST_VIEW AS -
(

SELECT * FROM TEST TABLE -
WHERE IS_ROLE_IN_SESSION('ROLE_XYZ')=TRUE
) ;
Which user and current role combination can retrieve data from the view? (Choose two.)

Answer options

• A. USER1 and ROLE_ALPHA
• B. USER2 and ROLE_BETA
• C. USER3 and ROLE_XYZ
• D. USER1 and ROLE_XYZ
• E. USER3 and ROLE_ALPHA

Correct answer: B, C

Explanation

USER2 with ROLE_BETA can access the view because ROLE_BETA inherits ROLE_XYZ, which is required to retrieve data. Similarly, USER3 with ROLE_XYZ can directly access the view. USER1 and ROLE_ALPHA cannot access the view as ROLE_ALPHA does not have the necessary privileges, while USER1 with ROLE_XYZ is also incorrect since USER1 does not have this role.