ARA-C01 Question 214
Single answerSchemasA global retailer uses a single Snowflake database named ANALYTICS with separate schemas for each domain team, including SALES, FINANCE, and MARKETING. The Architect must allow a contractor role to create and modify transient tables only in the SALES schema for a 3-month project. The contractor must not be able to create permanent tables, alter objects in other schemas, or grant access to other roles. Which approach best meets these requirements while following least-privilege design?
- A
Grant OWNERSHIP on the SALES schema to the contractor role for the duration of the project, then transfer ownership back afterward.
- B
Grant USAGE on database ANALYTICS and schema SALES, plus CREATE TABLE on schema SALES, and ensure the role does not have privileges such as MANAGE GRANTS or schema-level ownership.
- C
Grant CREATE TABLE on database ANALYTICS so the contractor can create tables in SALES, and rely on object naming conventions to keep work isolated.
- D
Grant CREATE TRANSIENT TABLE on schema SALES and REVOKE CREATE TABLE so the contractor can create only transient tables in that schema.
Show answer and explanation
Correct answer: B
Explanation
The best answer is to grant only the minimum privileges required for schema-scoped object creation: USAGE on the database, USAGE on the schema, and CREATE TABLE on the SALES schema. In Snowflake's privilege model, object creation privileges for tables are schema-level privileges. Also, Snowflake does not distinguish privileges for creating permanent versus transient tables; both are created with CREATE TABLE, with TRANSIENT specified in the DDL. Therefore, you cannot enforce transient-only creation through a separate privilege type. To satisfy the business requirement in practice, architects typically combine least-privilege grants with process controls, code review, deployment automation, or controlled DDL execution patterns. This aligns with Snowflake best practices around role-based access control, schema-level object creation privileges, and careful avoidance of broad privileges such as OWNERSHIP or MANAGE GRANTS unless administration is explicitly required.
- A. Incorrect.
Incorrect. Granting OWNERSHIP on the schema is far broader than required. OWNERSHIP is the highest privilege and would allow the contractor role to alter the schema, manage grants on the schema and potentially its contained objects depending on ownership and future object creation, which violates least privilege. This also introduces operational risk because transferring ownership can disrupt grant management and requires careful handling of existing outbound privileges.
- B. Correct.
Correct. To create tables in a schema, a role needs USAGE on the parent database and schema, and CREATE TABLE on the target schema. In Snowflake, transient versus permanent is chosen at table creation time; there is no separate CREATE TRANSIENT TABLE privilege. Without elevated privileges such as OWNERSHIP on the schema or MANAGE GRANTS, the contractor cannot administer access broadly. This approach confines object creation to the SALES schema and avoids unnecessary administrative capabilities.
- C. Incorrect.
Incorrect. CREATE TABLE is granted at the schema level, not at the database level. Granting database USAGE alone does not permit table creation anywhere, and naming conventions are not a security boundary. This option reflects a common misconception that database-level create privileges control schema-contained object creation.
- D. Incorrect.
Incorrect. Snowflake does not provide a distinct CREATE TRANSIENT TABLE privilege. Table type is specified in the CREATE TABLE statement, but the privilege required is still CREATE TABLE on the schema. Because permanent and transient tables use the same creation privilege, revoking a non-existent CREATE TRANSIENT TABLE privilege would not enforce the stated requirement.