SnowPro Advanced: Architect Practice Questions

SnowPro Advanced: Architect (ARA-C01) exam preparation

SnowPro Advanced: Architect practice questions in English: 171 questions with explanations. The mock exam has 65 questions and 115 minutes.

SnowPro Advanced: Architect free question 1 / 10 · Domain 1: Accounts and Security

User U1 ran USE SECONDARY ROLES ALL with ANALYST as the primary role. U1 has also been granted ETL_ROLE. ANALYST has only USAGE on schema STG. ETL_ROLE has CREATE TABLE on STG and SELECT on table RAW.ORDERS. Starting from this state, which of the following statements would succeed? (Choose two.)

  1. With ALL still in effect, SELECT from table RPT.SUMMARY, on which only BI_ROLE, granted to U1 later by an administrator, holds SELECT
  2. After replacing ALL with USE SECONDARY ROLES ETL_ROLE, SELECT from a table on which only BI_ROLE, granted to U1 later by an administrator, holds SELECT
  3. SELECT from table RAW.ORDERS, where ETL_ROLE is the only one of U1's roles that holds SELECT on RAW.ORDERS
  4. CREATE TABLE in schema STG, where ETL_ROLE is the only one of U1's roles that holds CREATE TABLE on STG
Show the answer and explanation

Answer: A, C

Why this is correct
USE SECONDARY ROLES decides which secondary roles are active in the session, and the privileges of those roles are combined with the privileges of the primary role to decide whether a SQL statement can run. SELECT on RAW.ORDERS is allowed by the privileges of ETL_ROLE, so it succeeds. When ALL is specified, the grants are not checked up front; the set of active secondary roles is worked out each time a statement runs. A BI_ROLE granted to U1 later therefore becomes active from the next statement without re-issuing the command, so a table on which only BI_ROLE holds SELECT can also be read.

Why the other options are wrong
"CREATE TABLE in schema STG, where ETL_ROLE is the only one of U1's roles that holds CREATE TABLE on STG" … Privileges that create objects are not taken from secondary roles. Even with ETL_ROLE active as a secondary role, the primary role ANALYST has no CREATE TABLE on STG, so the statement fails. To create the table, run it with ETL_ROLE as the primary role.
"After replacing ALL with USE SECONDARY ROLES ETL_ROLE, SELECT from a table on which only BI_ROLE, granted to U1 later by an administrator, holds SELECT" … When roles are named, the command checks the grants of those roles at that moment and records them as the set of secondary roles wanted for the session. Roles granted later become active automatically only with ALL; BI_ROLE is not in the named set, so the statement fails.

Key point
With ALL, a role that has been revoked also stops being active from the next statement, without re-issuing the command. Naming roles and specifying ALL differ in when changes to grants are picked up.

Go to the SnowPro Advanced: Architect practice app (10 free questions; purchase unlocks all questions)

See the full practice set