ADA-C01 Question 60
Single answerImplement and manage managed access schemasA financial services company uses a managed access schema named FIN_DB.REPORTING to enforce centralized access control. The schema is owned by role FIN_SCHEMA_OWNER. Analyst role FIN_ANALYST creates a table named DAILY_POSITIONS in the schema. Later, FIN_ANALYST attempts to grant SELECT on DAILY_POSITIONS to role FIN_AUDITOR so auditors can query the table directly. The grant fails. The company wants to preserve centralized privilege management while allowing auditors to read the table. Which action should the Snowflake administrator take?
- A
Grant OWNERSHIP on DAILY_POSITIONS to FIN_ANALYST so the creator can manage object privileges in the managed access schema.
- B
Have FIN_SCHEMA_OWNER or a role with MANAGE GRANTS grant SELECT on FIN_DB.REPORTING.DAILY_POSITIONS to FIN_AUDITOR.
- C
Convert the schema from managed access to a regular schema so object owners can grant privileges on their own objects.
- D
Grant USAGE on schema FIN_DB.REPORTING to FIN_AUDITOR; this will implicitly allow SELECT on tables created by FIN_ANALYST.
Show answer and explanation
Correct answer: B
Explanation
Managed access schemas change Snowflake's standard grant model. In a regular schema, the object owner can typically grant privileges on the object. In a managed access schema, grant management is centralized: only the schema owner or a role with the global MANAGE GRANTS privilege can grant privileges on objects within the schema, regardless of who created the object. This is useful in regulated environments where access control must be tightly governed by designated administrative roles rather than by individual object creators. In this scenario, the correct administrative action is to have FIN_SCHEMA_OWNER, or another authorized grant-administration role with MANAGE GRANTS, issue the SELECT grant to FIN_AUDITOR. Auditors would also need the appropriate USAGE privileges on the database and schema to query the table, but USAGE alone is not sufficient. This behavior is documented in Snowflake's managed access schema documentation and in the access control guidance describing how object privilege grants are handled differently in managed access schemas.
- A. Incorrect.
Incorrect. In a managed access schema, object owners do not control grant management in the usual way. Privilege grants on objects in the schema are centrally controlled by the schema owner or by a role with the global MANAGE GRANTS privilege. Transferring OWNERSHIP of the table to FIN_ANALYST would not align with the purpose of managed access and does not solve the centralized administration requirement.
- B. Correct.
Correct. In a managed access schema, only the schema owner (the role with OWNERSHIP on the schema) or a role with the global MANAGE GRANTS privilege can grant privileges on objects in that schema, including objects created by other roles. This preserves centralized access governance while allowing FIN_AUDITOR to receive SELECT on the table.
- C. Incorrect.
Incorrect. Although converting to a regular schema would restore object-owner-based grant behavior, it would violate the stated requirement to preserve centralized privilege management. Managed access schemas are specifically designed for this governance model.
- D. Incorrect.
Incorrect. USAGE on the schema is necessary for accessing objects in the schema, but it does not provide SELECT on tables. Table-level privileges such as SELECT must still be granted explicitly. This option reflects a common misunderstanding between container access privileges and object access privileges.