ARA-C01 Question 213
Single answerSchemasA global retail company uses a single Snowflake database named ANALYTICS with separate schemas for each domain team, including SALES, FINANCE, and MARKETING. The architect must let the FINANCE team create and manage tables, views, and stages only within the FINANCE schema, while preventing them from granting access to other roles. Security administrators must retain ownership and centrally manage privilege delegation. Which approach best meets these requirements with the least administrative overhead?
- A
Grant OWNERSHIP on the FINANCE schema to the FINANCE_ROLE so the team can fully manage all objects in that schema.
- B
Grant ALL PRIVILEGES on the ANALYTICS database to the FINANCE_ROLE and rely on future grants to limit access to the FINANCE schema.
- C
Grant USAGE on the ANALYTICS database and FINANCE schema, then grant CREATE TABLE, CREATE VIEW, and CREATE STAGE on the FINANCE schema to the FINANCE_ROLE without granting OWNERSHIP.
- D
Create a managed access schema for FINANCE and grant the FINANCE_ROLE MANAGE GRANTS so the team can administer object privileges within the schema without owning it.
Show answer and explanation
Correct answer: C
Explanation
The best answer is to grant the minimum required privileges for object creation within the specific schema: USAGE on the database, USAGE on the schema, and the schema-level CREATE privileges needed for the object types the team will manage. In Snowflake, creating objects inside a schema does not require schema OWNERSHIP; it requires the corresponding CREATE privilege on that schema and visibility via USAGE on the parent containers. This design satisfies least privilege and keeps ownership and grant administration with the security team.
The key architectural principle is separation of duties. If administrators must retain ownership and control privilege delegation, then business-domain roles should not receive OWNERSHIP or broad grant-management capabilities. OWNERSHIP would allow significant administrative control, and MANAGE GRANTS would permit privilege delegation, which the scenario explicitly prohibits.
Snowflake documentation and best practices distinguish between object creation privileges and ownership/grant management. Relevant areas include access control, schema privileges, OWNERSHIP semantics, and managed access schemas. Managed access schemas are designed to centralize privilege management, but in this scenario the requirement is specifically to prevent the FINANCE team from granting access, so directly assigning MANAGE GRANTS to that team is inappropriate.
- A. Incorrect.
Incorrect. Granting OWNERSHIP on the schema would allow the FINANCE_ROLE to control the schema and potentially transfer ownership or manage grants in ways the security team wants to centralize. OWNERSHIP is the highest privilege in Snowflake and is broader than required for simply creating and managing tables, views, and stages. This violates the requirement that security administrators retain ownership and centrally manage privilege delegation.
- B. Incorrect.
Incorrect. Granting ALL PRIVILEGES on the database is too broad and does not align with least privilege. It would expose capabilities beyond the FINANCE schema and does not solve the central requirement around restricting delegation. Future grants also do not act as a boundary mechanism for limiting a role that already has excessive privileges at the database level.
- C. Correct.
Correct. To create objects in a schema, a role needs USAGE on the parent database and schema plus the relevant CREATE privileges on the schema, such as CREATE TABLE, CREATE VIEW, and CREATE STAGE. This enables the FINANCE team to create and manage objects within FINANCE without giving them schema ownership. Because ownership remains with administrators, privilege delegation can stay centralized. This is the most targeted and operationally simple design.
- D. Incorrect.
Incorrect. A managed access schema is useful when object owners should not control grants and privilege management should be centralized with the schema owner or a role with MANAGE GRANTS. However, granting MANAGE GRANTS to the FINANCE_ROLE directly conflicts with the requirement to prevent the team from granting access to other roles. It also gives broader grant-management capability than necessary.