SnowPro Advanced: Architect — Question 153

A company has data in two databases, DB1 and DB2, with the following roles:

Role R1 has select access to all tables in database DB1.
Role R2 has select access to all tables in database DB2.

Users should, by default only, be allowed to access data from one of the databases to ensure that the DEFAULT_ROLE setting should be used. A limited number of users that have select access to DB1 also need select access to DB2 in the same query.

How can the access be provided for this limited number of users using the LEAST amount of operational overhead?

Answer options

• A. Alter the users and set the DEFAULT_SECONDARY_ROLE property to R2.
• B. Grant R2 to the users and use the USE_SECONDARY_ROLES setting for SELECT.
• C. Grant R2 access to the R1 role so the users can take advantage of privilege inheritance.
• D. Grant R2 access to the users and have them activate it using the USE_SECONDARY_ROLES command.

Correct answer: D

Explanation

The correct answer is D because it allows users to activate R2 when needed while maintaining their default access to DB1, thus minimizing operational overhead. Options A and B do not effectively manage the default access requirement, while C complicates the access by introducing privilege inheritance, which is unnecessary for this scenario.