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 3 / 10 · Domain 1: Accounts and Security

GRANT SELECT ON FUTURE TABLES IN DATABASE D1 TO ROLE R1 has been set on database D1. Afterwards, GRANT INSERT, DELETE ON FUTURE TABLES IN SCHEMA D1.S1 TO ROLE R2 was set on schema D1.S1 only. D1.S2 is a schema created WITH MANAGED ACCESS, and no schema-level future grant has been placed on it. Which of the following describes the privileges that apply when new tables are later created in S1 and S2, according to the official documentation?

  1. New tables in S1 get only INSERT and DELETE for R2, and the database-level grant does not reach S2 because it is a managed access schema
  2. New tables in S1 get SELECT for R1 and INSERT and DELETE for R2, and new tables in S2 get SELECT for R1
  3. New tables in S1 get SELECT for R1 and INSERT and DELETE for R2, and the database-level grant does not reach S2 because it is a managed access schema
  4. New tables in S1 get only INSERT and DELETE for R2, and new tables in S2 get SELECT for R1 from the database-level grant
Show the answer and explanation

Answer: D

Why this is correct
The documentation's considerations for future grants state that when future grants of the same object type are defined at both the database level and the schema level of a schema in that database, the schema-level grants take precedence and the database-level grants are ignored. This is true whether the grants go to the same role or to different roles. The documentation's example likewise grants SELECT to r1 at the database level and INSERT and DELETE to r2 at the schema level, and a new table in that schema receives only r2's privileges. New tables in S1 therefore get only INSERT and DELETE for R2, and not SELECT for R1. In addition, database-level future grants apply to both regular schemas and managed access schemas. S2 has no schema-level future grant, so R1 gets SELECT there.

Why the other options are wrong
"New tables in S1 get SELECT for R1 and INSERT and DELETE for R2, and new tables in S2 get SELECT for R1" … When a schema-level future grant of the same type exists, the database-level grant is ignored in that schema. The two do not stack: the only privileges new tables in S1 receive are INSERT and DELETE for R2.
"New tables in S1 get only INSERT and DELETE for R2, and the database-level grant does not reach S2 because it is a managed access schema" … The treatment of S1 is right, but S2 is wrong. The documentation states that database-level future grants apply to both regular and managed access schemas. S2 has no schema-level grant, so R1 gets SELECT.
"New tables in S1 get SELECT for R1 and INSERT and DELETE for R2, and the database-level grant does not reach S2 because it is a managed access schema" … Both points are wrong. In S1 the schema-level grant takes precedence, so the database-level SELECT for R1 is ignored, and the database-level grant does apply to the managed access schema S2.

Key point
A future grant determines only the initial set of privileges a new object receives. After the object exists, administrators can still add or revoke privileges on individual objects. Once a schema has even one future grant of a given object type, database-level grants no longer apply to objects of that type in the schema, so if the privileges handed out at the database level are still needed there, add them to the schema-level grant.

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

See the full practice set