ADA-C01 Question 59
Select 2Implement and manage managed access schemasA financial services company uses a managed access schema named FIN_SECURE to enforce centralized privilege administration. In this schema, analyst_role owns table FIN_SECURE.POSITIONS, and data_admin_role owns the schema. A user with role analyst_role needs the compliance_role to have SELECT on FIN_SECURE.POSITIONS. The company wants to preserve the managed access model and avoid transferring object ownership. Which role or roles can grant this privilege?
- A
analyst_role, because it owns the POSITIONS table
- B
data_admin_role, because it owns the managed access schema
- C
A role with the global MANAGE GRANTS privilege
- D
securityadmin only if it also owns the POSITIONS table
- E
Any role that has USAGE on the FIN_SECURE schema
Show answer and explanation
Correct answers: B, C
Explanation
Managed access schemas centralize privilege administration. Unlike regular schemas, object owners inside a managed access schema cannot independently grant privileges on their objects. Instead, grant management is restricted to the schema owner and roles with the global MANAGE GRANTS privilege. This design is commonly used to enforce stronger governance and separation of duties. In this scenario, data_admin_role can grant SELECT because it owns the managed access schema, and a role with MANAGE GRANTS can also do so. This aligns with Snowflake documentation on managed access schemas and access control, which distinguishes object ownership from grant authority inside managed access schemas.
- A. Incorrect.
Incorrect. In a managed access schema, object owners do not control grant decisions on objects inside the schema. This is a key difference from regular schemas. Even though analyst_role owns FIN_SECURE.POSITIONS, it cannot grant SELECT on that table unless it is also acting through an authorized schema-level grant administrator role such as the schema owner, or through a role with MANAGE GRANTS.
- B. Correct.
Correct. In a managed access schema, the schema owner is responsible for managing grants on objects in the schema. Because data_admin_role owns FIN_SECURE, it can grant SELECT on FIN_SECURE.POSITIONS to compliance_role without changing ownership of the table.
- C. Correct.
Correct. A role with the global MANAGE GRANTS privilege can grant object privileges, including on objects in managed access schemas, without needing to own the object. This privilege is intended for centralized grant administration and is consistent with the managed access operating model.
- D. Incorrect.
Incorrect. The requirement that securityadmin must also own the table is wrong. Ownership of the table is not what enables grants in a managed access schema. If a role is using the MANAGE GRANTS privilege, it can manage grants without owning the object. SECURITYADMIN commonly holds grant-management capabilities in practice, but the deciding factor is the privilege model, not table ownership.
- E. Incorrect.
Incorrect. USAGE on the schema only allows name resolution and access to objects when other required privileges exist; it does not confer authority to grant privileges to other roles. This distractor reflects a common misunderstanding between object access and privilege delegation.