COF-C03 Question 38
Single answerStored proceduresA data engineering team uses a Snowflake stored procedure to load daily files, update several dimension tables, and write audit rows to a log table. Different analysts need to run the procedure on demand, but the analysts should not be granted direct INSERT, UPDATE, or DELETE privileges on the underlying tables. The procedure should run successfully for those analysts while still using the privileges of the role that owns the procedure. Which approach should the team use?
- A
Create the stored procedure with EXECUTE AS OWNER and grant USAGE on the procedure to the analysts' role.
- B
Create the stored procedure with EXECUTE AS CALLER and grant USAGE on the procedure to the analysts' role.
- C
Grant the analysts' role INSERT, UPDATE, and DELETE on all target tables, then create the procedure with EXECUTE AS CALLER.
- D
Create the stored procedure as SECURE so it automatically runs with the owner's privileges for any caller.
Show answer and explanation
Correct answer: A
Explanation
Snowflake stored procedures support caller's rights and owner's rights execution models. For administrative or controlled data-modification tasks where end users should not have direct access to underlying objects, best practice is to use an owner's rights stored procedure by specifying EXECUTE AS OWNER. This allows users to invoke approved logic without granting them broad table-level DML privileges. By contrast, EXECUTE AS CALLER requires the invoking role to already have the needed permissions. This question tests practical privilege design, a common SnowPro Core topic. See Snowflake documentation on stored procedures and caller's rights vs. owner's rights execution behavior.
- A. Correct.
Correct. An owner's rights stored procedure runs with the privileges of the procedure owner rather than the caller. This is the appropriate design when users need to execute business logic that modifies objects they should not access directly. Granting the calling role permission to use or call the procedure allows analysts to invoke it without broad DML rights on the underlying tables.
- B. Incorrect.
Incorrect. EXECUTE AS CALLER uses the privileges of the invoking role. In this scenario, analysts do not have direct DML privileges on the target tables, so the procedure would fail when attempting to modify those objects. This option reflects a common misconception that procedure logic itself bypasses underlying object permissions.
- C. Incorrect.
Incorrect. While this would allow the procedure to run under caller's rights, it violates the stated requirement that analysts should not have direct DML privileges on the underlying tables. It expands privileges unnecessarily and weakens least-privilege security.
- D. Incorrect.
Incorrect. SECURE is a concept used with objects such as secure views and secure UDFs to protect underlying logic or data exposure patterns, but it does not make a stored procedure run with owner privileges by default. Execution rights for stored procedures are controlled through EXECUTE AS OWNER or EXECUTE AS CALLER.