ARA-C01 Question 226
Single answerProceduresA data engineering team has a stored procedure that performs nightly maintenance: it loads data into curated tables, updates audit tables, and then returns a status message. The procedure is owned by a central ETL role and is called by several analyst roles that do not have direct privileges on the target tables. Recently, the team added logic that writes the current caller's username into an audit record so they can see which user initiated the run. After deployment, they notice the audit table records the procedure owner's identity instead of the analyst who called it. The architect must preserve centralized privilege management while capturing the actual initiating user. Which approach should be used?
- A
Recreate the procedure with EXECUTE AS CALLER so the procedure can record the calling user's identity directly
- B
Keep the procedure as EXECUTE AS OWNER and pass the initiating username as an input parameter from the client or calling layer
- C
Convert the procedure to an external function so Snowflake automatically records the end user's identity in the audit table
- D
Wrap the procedure in a task, because task context preserves the original invoker even when the procedure runs with owner rights
Show answer and explanation
Correct answer: B
Explanation
This scenario tests the distinction between caller's rights and owner's rights for Snowflake stored procedures. When a procedure is created with EXECUTE AS OWNER, it runs with the owner's privileges, which is commonly used to let less-privileged users invoke controlled data operations without direct table access. However, the tradeoff is that execution context is based on the owner, so relying on in-procedure context to identify the invoker can produce the owner's identity rather than the initiating user. If the architect must keep owner-rights execution and still audit the initiator, the recommended design is to pass the initiating user or request identity explicitly as a parameter from the invoking application or orchestration framework. Rewriting the procedure as EXECUTE AS CALLER would alter the security model and likely fail because analyst roles lack direct privileges on the target objects. This aligns with Snowflake best practices around stored procedure execution rights and controlled privilege delegation.
- A. Incorrect.
Incorrect. EXECUTE AS CALLER causes the procedure to run with the caller's privileges, which would break the requirement to preserve centralized privilege management for analyst roles that do not have direct access to the target tables. While this would make current-user context reflect the caller, it changes the security model and is not appropriate for the stated requirement.
- B. Correct.
Correct. EXECUTE AS OWNER is the right model when the procedure owner should provide the required object privileges to callers indirectly. In that model, context functions and privilege evaluation occur in the owner's execution context, so if the business needs the initiating user's identity, it should be supplied explicitly, such as through an argument from the application, orchestration layer, or upstream caller. This preserves least-privilege access for analysts while still enabling accurate auditing of who initiated the run.
- C. Incorrect.
Incorrect. External functions are for calling remote services, not for replacing stored procedures that perform multi-step SQL maintenance inside Snowflake. They do not solve the privilege-context issue described here and would add unnecessary architecture complexity.
- D. Incorrect.
Incorrect. Tasks run on a schedule or when triggered and execute with task ownership semantics, not as a way to preserve the original end-user identity through an owner-rights procedure call. Using a task would not make the procedure automatically capture the initiating analyst's identity in the way described.