COF-C03 Question 37
Single answerStored proceduresA data engineering team wants to create a stored procedure that truncates and reloads several staging tables every night. The procedure will be created by a DBA role, but it must be callable by an ETL role that does not have direct privileges to truncate or insert into those tables. The team also wants the procedure to run using the privileges of the procedure owner so the ETL role does not need additional object grants. Which configuration should be used when creating the stored procedure?
- A
Create the stored procedure with EXECUTE AS OWNER so it runs with the privileges of the role that owns the procedure.
- B
Create the stored procedure with EXECUTE AS CALLER so it inherits the ETL role's active privileges at runtime.
- C
Create the stored procedure as SECURE so it automatically runs with elevated privileges from the procedure owner.
- D
Grant USAGE on the database and schema to the ETL role only; stored procedures can perform DML regardless of underlying table privileges.
Show answer and explanation
Correct answer: A
Explanation
The correct answer is to create the stored procedure with EXECUTE AS OWNER. Snowflake supports stored procedures that run either with caller's rights or owner's rights. When the business requirement is to let a lower-privileged role perform controlled operations, such as TRUNCATE and INSERT on staging tables, without granting direct access to those tables, owner's rights is the proper approach. The ETL role still needs the ability to call the procedure, but it does not need the underlying object privileges used inside the procedure. By contrast, EXECUTE AS CALLER would require the ETL role to already hold the needed table privileges. This aligns with Snowflake documentation on stored procedure execution context and privilege delegation best practices.
- A. Correct.
Correct. In Snowflake, a stored procedure can be created to execute with the rights of the owner by using EXECUTE AS OWNER. This allows callers to run the procedure without needing the underlying privileges on the objects referenced inside the procedure, as long as they have permission to call the procedure itself. This is the appropriate design when a lower-privileged ETL role must perform controlled administrative or data-loading actions through a procedure.
- B. Incorrect.
Incorrect. EXECUTE AS CALLER causes the stored procedure to run with the privileges of the calling role, not the owner. In this scenario, the ETL role does not have direct privileges to truncate or insert into the staging tables, so the procedure would fail when attempting those operations. This option reflects a common misunderstanding between caller's rights and owner's rights procedures.
- C. Incorrect.
Incorrect. SECURE applies to specific object types for data protection and definition hiding, but it does not change a stored procedure's execution rights model in the way described here. Making a procedure secure does not automatically cause it to run with elevated owner privileges. Execution context for stored procedures is controlled by EXECUTE AS settings.
- D. Incorrect.
Incorrect. Granting only USAGE on the database and schema is not sufficient for a stored procedure to perform DML on behalf of a caller unless the procedure is explicitly configured with owner's rights. Stored procedures do not bypass object privileges by default. The ETL role would still need either direct table privileges for caller's rights execution or access through an owner's rights procedure.