ARA-C01 Question 225
Single answerProceduresA data engineering team has created a stored procedure named LOAD_SALES() that truncates a staging table, copies new files from an internal stage into the table, and then merges the staged data into a curated schema. Analysts need to run this procedure on demand, but they should not have direct privileges to truncate, copy into, or merge into the underlying tables. The architect also wants to minimize ongoing privilege administration as table objects evolve. Which approach best meets these requirements?
- A
Create LOAD_SALES() as an owner's rights stored procedure, grant USAGE on the procedure to the analyst role, and keep the required table privileges with the procedure owner role.
- B
Create LOAD_SALES() as a caller's rights stored procedure, grant USAGE on the procedure to the analyst role, and rely on the analyst role inheriting the procedure owner's table privileges at runtime.
- C
Create LOAD_SALES() as an owner's rights stored procedure, but also grant the analyst role TRUNCATE, INSERT, and MERGE privileges on all referenced tables so the procedure can execute successfully.
- D
Create LOAD_SALES() as a caller's rights stored procedure and grant only USAGE on the warehouse to the analyst role, because stored procedures encapsulate all object privileges automatically.
- E
Create LOAD_SALES() as a secure view instead of a stored procedure so analysts can invoke the data loading logic without needing DML privileges on the base tables.
Show answer and explanation
Correct answer: A
Explanation
The best design is to use an owner's rights stored procedure. In Snowflake, stored procedures can run either with caller's rights or owner's rights. When a procedure is defined to execute as owner, it uses the privileges of the role that owns the procedure, which is the appropriate design when consumers should be allowed to run a controlled workflow without receiving direct access to the underlying objects. This is a common architectural pattern for delegated operations such as data loads, maintenance routines, and governed DML. By contrast, caller's rights procedures require the invoking role to have the needed privileges on every referenced object. For this scenario, that would force the architect to grant analysts direct DML access, violating the requirement and increasing administration. Snowflake documentation on stored procedures and rights models supports this distinction: owner's rights procedures are intended for privilege delegation through controlled execution, while caller's rights procedures are appropriate when execution should respect the caller's existing object access.
- A. Correct.
Correct. Owner's rights stored procedures execute with the privileges of the procedure owner rather than the caller. This is the standard pattern when users need to perform controlled administrative or data modification tasks without being granted direct access to the underlying objects. Granting the analyst role USAGE on the procedure allows invocation, while the owner role retains the necessary privileges on the stage, schemas, and tables. This also reduces privilege sprawl because analysts do not need direct DML privileges on each object touched by the procedure.
- B. Incorrect.
Incorrect. A caller's rights stored procedure runs with the caller's active privileges, not the owner's privileges. Snowflake does not automatically let the caller inherit the procedure owner's table privileges at runtime. If analysts lack TRUNCATE, COPY, and MERGE-related access to the referenced objects, the procedure will fail.
- C. Incorrect.
Incorrect. This undermines the core requirement that analysts must not have direct privileges on the underlying tables. Although an owner's rights procedure can run successfully with owner privileges, separately granting analysts TRUNCATE, INSERT, or other direct object privileges defeats the security objective and increases administrative overhead.
- D. Incorrect.
Incorrect. Caller's rights procedures do not encapsulate object privileges. The caller still needs the necessary permissions on referenced objects. Warehouse USAGE alone is insufficient for truncating tables, loading staged files, or performing merges.
- E. Incorrect.
Incorrect. A secure view is for controlled data access, not for encapsulating procedural DML workflows such as TRUNCATE, COPY INTO, and MERGE. Views cannot replace stored procedures for multi-step data loading and transformation logic.