ARA-C01 Question 356
Single answerStored proceduresA data platform team uses a stored procedure to load daily partner files into a curated schema. The procedure is owned by role ETL_OWNER and is called by analysts through a task and occasionally on demand. The procedure must truncate and reload target tables in the curated schema, but analysts must not be granted direct INSERT, UPDATE, DELETE, or TRUNCATE privileges on those tables. The security team also wants to reduce the risk that callers can use the procedure to affect objects outside the intended curated schema. Which design is the MOST appropriate?
- A
Create the stored procedure with EXECUTE AS OWNER, qualify all object references to the curated schema inside the procedure, and grant callers only USAGE on the procedure.
- B
Create the stored procedure with EXECUTE AS CALLER, grant analysts DML privileges on the curated tables, and rely on masking policies to prevent unintended updates.
- C
Create the stored procedure with EXECUTE AS OWNER, leave table references unqualified so they resolve based on each caller's current schema, and grant analysts OWNERSHIP on the procedure.
- D
Create the stored procedure with EXECUTE AS CALLER, keep all target table references fully qualified, and grant analysts only USAGE on the procedure.
Show answer and explanation
Correct answer: A
Explanation
The best design is to use an owner's rights stored procedure, specified as EXECUTE AS OWNER, when the goal is to encapsulate privileged operations such as TRUNCATE and reload while preventing callers from receiving direct access to the underlying tables. This is a common architectural pattern in Snowflake for controlled delegation. To reduce risk and improve determinism, object names inside the procedure should be fully qualified rather than relying on the caller's current database or schema. Callers typically need USAGE on the procedure to invoke it, while the procedure owner role must hold the required privileges on the referenced objects. This aligns with Snowflake best practices for stored procedure rights models and secure object resolution in procedural code.
- A. Correct.
Correct. EXECUTE AS OWNER allows the procedure to run with the owner's privileges, which is the standard approach when callers should be able to perform controlled actions without having direct privileges on underlying objects. Fully qualifying object references helps constrain the procedure's behavior to the intended schema and avoids dependence on the caller's session context such as current database or schema. Granting callers USAGE on the procedure is the appropriate privilege to allow invocation without exposing direct DML on the target tables.
- B. Incorrect.
Incorrect. EXECUTE AS CALLER uses the caller's privileges, so analysts would need direct DML privileges on the curated tables for the procedure to succeed. That violates the requirement that analysts must not receive direct INSERT, UPDATE, DELETE, or TRUNCATE privileges. Masking policies also do not solve the authorization problem for write operations; they govern data visibility, not whether a role can modify tables.
- C. Incorrect.
Incorrect. Although EXECUTE AS OWNER is appropriate for privilege delegation, leaving references unqualified is a poor security and reliability practice because object resolution can depend on session context and may allow the procedure to operate on unintended objects if names overlap. Granting OWNERSHIP on the procedure to analysts is also inappropriate because OWNERSHIP is excessive and would let them alter or transfer control of the procedure.
- D. Incorrect.
Incorrect. Fully qualifying target tables is a good practice, but EXECUTE AS CALLER means the procedure still runs with the analyst's privileges. If analysts only have USAGE on the procedure and no DML privileges on the underlying tables, the procedure will fail. This option does not meet the requirement to let analysts invoke controlled data-loading actions without direct table privileges.