DEA-C02 · Question #106
The following stored procedure has been created to load orders for a single date into a target table: create or replace procedure uspLoadOrdersByDate () returns string language javascript execute as…
The correct answer is A. Change the procedure to CALLER. D. Change the procedure to accept $dateToProcess as an input parameter so that no session. Snowflake's OWNER's rights procedures execute in an isolated security context that deliberately blocks access to the caller's session variables - this is a security boundary, not a bug. Option A (changing to EXECUTE AS CALLER) works because caller's rights procedures inherit…
Question
The following stored procedure has been created to load orders for a single date into a target table:
create or replace procedure uspLoadOrdersByDate () returns string language javascript execute as owner as ' sqlCmd = INSERT INTO ORDERS_BY_DATE_TGT SELECT * FROM ORDERS_BY_DATE WHERE O_ORDERDATE = $dateToProcess; sqlStmt = snowflake.createStatement({sqlText: sqlCmd}); res = sqlStmt.execute(); res.next(); return res.GetColumnValue(1); '; Upon executing the following statements, the following error is returned:
set dateToProcess = '2020-08-02'; call uspLoadOrdersByDate(); Execution error in store procedure USPLOADORDERSBYDATE: Use of session variable '$DATETOPROCESS' is not allowed in owners rights stored procedure At Statement.execute, line 4 position 14 Making what changes will allow the procedure to execute successfully? (Choose two.)
Options
- AChange the procedure to CALLER.
- BCreate a scheduled task to execute the procedure each day, and set dateToProcess =
- CModify the body of the procedure to cast $dateToProcess to DATE within the WHERE clause of
- DChange the procedure to accept $dateToProcess as an input parameter so that no session
- ERename the session variable to upper case to allow the procedure to read from it.
How the community answered
(28 responses)- A75% (21)
- B4% (1)
- C14% (4)
- E7% (2)
Explanation
Snowflake's OWNER's rights procedures execute in an isolated security context that deliberately blocks access to the caller's session variables - this is a security boundary, not a bug. Option A (changing to EXECUTE AS CALLER) works because caller's rights procedures inherit the calling session's context, making $dateToProcess visible. Option D works because passing the date as an input parameter bypasses session variables entirely - the value travels as a named argument, which is permitted in any procedure type.
Why the distractors fail:
- B is wrong because scheduling a task doesn't change how the procedure resolves session variables; the same ownership restriction applies at execution time.
- C is wrong because the error is about access to the session variable, not its data type - wrapping it in a
CASTstill references a forbidden variable. - E is wrong because variable name casing has no effect on Snowflake's owner's rights restriction; the block is categorical, not case-sensitive.
Memory tip: Think of OWNER's rights as a "clean room" - the procedure runs isolated from the caller's session state for security. If you need session context, either let the caller run it (CALLER rights) or pass the data through the door as an explicit parameter.
Topics
Community Discussion
No community discussion yet for this question.