ARA-C01 Question 90
Single answerAccess control privilegesA company uses Snowflake managed access schemas to centralize object grant administration. In database PROD_DB, schema FINANCE is configured as a managed access schema and owned by role SECURITYADMIN. Role ETL_DEV owns table PROD_DB.FINANCE.GL_ENTRIES. The company wants role ANALYST_R to be able to query GL_ENTRIES, while preserving the managed access model and least privilege. Which action should the architect recommend?
- A
Grant OWNERSHIP on PROD_DB.FINANCE.GL_ENTRIES to ANALYST_R so the role can query the table directly.
- B
Have role ETL_DEV grant SELECT on PROD_DB.FINANCE.GL_ENTRIES to ANALYST_R because ETL_DEV owns the table.
- C
Have role SECURITYADMIN grant SELECT on PROD_DB.FINANCE.GL_ENTRIES to ANALYST_R, and ensure ANALYST_R also has USAGE on database PROD_DB and schema FINANCE.
- D
Grant USAGE on schema FINANCE to ETL_DEV with GRANT OPTION so ETL_DEV can pass through SELECT access on all tables in the schema.
Show answer and explanation
Correct answer: C
Explanation
This question tests understanding of Snowflake access control behavior in managed access schemas, which is a frequent architecture and governance scenario. In a regular schema, an object owner can typically grant privileges on that object. However, in a managed access schema, object privilege management is centralized: only the schema owner or a role with the global MANAGE GRANTS privilege can grant privileges on objects in the schema. Therefore, ETL_DEV cannot grant SELECT on GL_ENTRIES even though it owns the table. The correct solution is for SECURITYADMIN, as schema owner, to grant SELECT on the table and ensure the consuming role also has the required USAGE privileges on the containing database and schema. This aligns with Snowflake best practices for centralized access governance and least privilege. Relevant Snowflake documentation includes topics on access control, managed access schemas, object ownership, and required privileges for querying objects.
- A. Incorrect.
Incorrect. Granting OWNERSHIP is excessive and violates least privilege because ANALYST_R only needs read access. OWNERSHIP transfers control of the object and is not required to query a table. In addition, changing ownership can disrupt existing grant administration and object lifecycle controls.
- B. Incorrect.
Incorrect. In a managed access schema, object owners do not control grants on objects in that schema unless they also own the schema or hold an appropriate global privilege. This is a common misconception based on standard schemas, where object owners can usually grant privileges on their objects. Managed access shifts grant management to the schema owner or a role with MANAGE GRANTS.
- C. Correct.
Correct. In a managed access schema, privileges on objects are centrally administered by the schema owner or a role with the global MANAGE GRANTS privilege. Since SECURITYADMIN owns the managed access schema, it can grant SELECT on the table. To actually resolve and query the object, ANALYST_R also needs USAGE on the parent database and schema.
- D. Incorrect.
Incorrect. USAGE on a schema does not allow a role to grant data access on tables within that schema. GRANT OPTION applies to specific privileges that a role holds and can re-grant; it does not override managed access behavior. ETL_DEV owning the table still does not enable it to manage grants in a managed access schema.