ADA-C01 Question 55
Single answerImplement and manage future grants including restrictionsA Snowflake administrator is onboarding a new role, ANALYST_R, for a finance data platform. The requirement is that ANALYST_R must automatically receive SELECT on all existing and future tables and views in schema FIN_DB.REPORTING. The security team also requires that if the schema is cloned to another environment, access must continue to be controlled through schema-level rules rather than broader database-level inheritance. Which approach best meets these requirements while avoiding future-grant conflicts?
- A
Grant SELECT on all existing tables and views in FIN_DB.REPORTING to ANALYST_R, then grant future SELECT on tables and views in the FIN_DB database to ANALYST_R.
- B
Grant SELECT on all existing tables and views in FIN_DB.REPORTING to ANALYST_R, then grant future SELECT on tables and views in schema FIN_DB.REPORTING to ANALYST_R.
- C
Grant imported privileges on FIN_DB to ANALYST_R, then grant future SELECT on schema FIN_DB.REPORTING to ANALYST_R.
- D
Grant OWNERSHIP on schema FIN_DB.REPORTING to ANALYST_R, then grant future SELECT on tables and views in that schema to ANALYST_R.
Show answer and explanation
Correct answer: B
Explanation
In Snowflake, future grants apply only to objects created after the future grant is established, so access to existing tables and views must be granted separately. For this scenario, the administrator should grant SELECT on all existing tables and views in FIN_DB.REPORTING and then define future grants at the schema level for tables and views in FIN_DB.REPORTING. This satisfies the requirement that access remain governed by schema-level rules.
A key Snowflake restriction and best practice is understanding future-grant scope and precedence. Future grants can be defined at both the database and schema levels, but for the same object type, schema-level future grants take precedence over database-level future grants. Therefore, if the organization wants control localized to a schema, schema-level future grants are the appropriate design. This also reduces unintended privilege propagation across unrelated schemas.
Relevant Snowflake documentation areas include GRANT
- A. Incorrect.
Incorrect. Existing object grants are necessary because future grants apply only to objects created after the grant is defined. However, using database-level future grants does not best satisfy the requirement to keep control at the schema level. In Snowflake, schema-level future grants take precedence over database-level future grants for the same object type, and relying on database-level future grants introduces broader scope than required and can create administration complexity when schema-specific restrictions are desired.
- B. Correct.
Correct. This approach combines grants on existing objects with schema-level future grants on the relevant object types. Future grants in Snowflake are not retroactive, so existing tables and views must be granted separately. Applying future grants at the schema level aligns with the requirement to keep access controlled through schema-level rules and avoids unnecessary broader database-wide future grants. This is also the cleanest way to prevent ambiguity or conflicts with broader database-level grant strategies.
- C. Incorrect.
Incorrect. Imported privileges are used for shared databases and are unrelated to granting access to regular tables and views in a standard database/schema you manage. They do not replace object-level privileges such as SELECT on tables and views. A candidate might choose this if confusing shared database access with normal RBAC administration.
- D. Incorrect.
Incorrect. Granting OWNERSHIP to an analyst role violates least-privilege principles and is unnecessary for read-only access. In addition, OWNERSHIP is a powerful privilege that transfers control of the object. Future SELECT grants can be defined by an authorized administrative role without making ANALYST_R the schema owner.