Articles · Snowflake
Snowflake Stored Procedure Interview Questions: Caller Rights, Owner Rights, and Session State
Practice Snowflake stored procedure interviews with caller and owner rights, nested-call context, session-state leakage, and a worked permissions matrix.
Snowflake stored procedure interview questions become easier when you separate three things: the privileges authorizing a statement, the context resolving its objects, and the state left in the caller's session. A procedure that runs successfully as its owner can still violate an application's expectations about tenant input or connection reuse.
This guide uses a restricted caller, a procedure owner, and a protected table to make those distinctions concrete.
Official facts are grounded in current Snowflake documentation checked on October 6, 2026. The permission ledger, traces, and interview advice are original examples. They describe Snowflake mechanisms, not a verified hiring process at Snowflake or any employer. The SQL is illustrative; local checks model the stated cases without executing a Snowflake account.
What changes between caller rights and owner rights?
The CREATE PROCEDURE reference supports explicit EXECUTE AS CALLER and EXECUTE AS OWNER; owner rights is the default when the ordinary execution mode is omitted. Use an explicit mode in this exercise; the documented default is not a substitute for inspecting an existing routine.
For a plain table access, a caller-rights procedure uses the caller's privileges. An owner-rights procedure can delegate a controlled operation using its owner's privileges. That does not mean the caller gains unrestricted access to the underlying table outside the procedure.
Consider SECURE.DATA.SECRETS, with a role PROC_ADMIN allowed to read it. The role ANALYST can use the procedure's database and schema and invoke its public entry point, but cannot select from the protected table directly. Assume an available warehouse and the necessary container privileges for each described execution path.
| Attempt by ANALYST | Execution mode | Result under this ledger | Reason |
|---|---|---|---|
| Direct SELECT on protected table | No procedure | Denied | Caller lacks table access. |
| CALL that performs the same SELECT | CALLER | Denied | A wrapper does not add owner privileges. |
| CALL that returns a bounded summary | OWNER | Allowed | PROC_ADMIN authorizes the ordinary table read. |
| Direct SELECT after the owner CALL | No procedure | Still denied | Invocation did not grant table SELECT. |
The third row assumes the handler actually restricts its operation. A procedure returning every secret row is not a bounded summary simply because its signature has a friendly name. Explain the permitted inputs and output, then show where the restriction is enforced.
Which grants let the caller invoke the procedure?
An incomplete answer is “grant SELECT to the analyst.” That may make the caller-rights example work, but it changes the exercise's security goal: the analyst was supposed to receive a limited operation without direct table reading.
For the owner example, grant invocation access to the intended procedure, alongside the necessary database and schema usage. The GRANT privileges reference requires argument types when identifying an overloaded procedure.
GRANT USAGE ON DATABASE APP_DB TO ROLE ANALYST;
GRANT USAGE ON SCHEMA APP_DB.PUBLIC TO ROLE ANALYST;
GRANT USAGE ON PROCEDURE APP_DB.PUBLIC.READ_SUMMARY(VARCHAR)
TO ROLE ANALYST;
These statements illustrate the entry-point grants, not a complete account setup. They assume an authorized grantor and existing objects. Warehouse access, table/container access for the owner, and the handler's other dependencies must also fit the operation.
Including VARCHAR identifies the overload whose invocation grant you are checking. READ_SUMMARY(VARCHAR) and READ_SUMMARY(NUMBER) are distinct signatures. When a call fails, compare the intended argument type, the actual overload, and the grant target before concluding that all procedure privileges are broken.
Our proposed handler accepts a validated summary category, queries a fully qualified protected table, and returns an aggregate. A category that controls presentation is not proof of tenant authorization. If categories map to sensitive tenants, validate that the caller is allowed to request the category; an owner-rights handler must not trust an arbitrary tenant identifier as an authorization decision.
How can references change the simple permission story?
Snowflake documents a specific mechanism for passing references to objects and queries. Operations on a supported reference can use the creating caller's authorization even within an owner-rights procedure. This is why “every operation inside OWNER always uses owner privileges” is too broad.
Separate the cases in your answer. A direct, ordinary read of SECURE.DATA.SECRETS follows our owner ledger. A handler designed to act on a caller-supplied reference needs its reference semantics examined independently. A plain string containing a table name is not automatically such a reference.
The distinction is useful when designing reusable procedures. You might want a common implementation to operate only on data the caller already has permission to access. Alternatively, you might want to delegate one tightly constrained operation on an owner-controlled object. Those are different authorization contracts, even if both are written as procedures.
In an interview, do not introduce references just to evade a failed permissions example. First predict the ordinary case, then explain how deliberately adopting the documented reference mechanism changes the design.
How do nested calls affect execution rights?
The official caller and owner rights guide states that caller-rights behavior requires the procedure and its entire ancestry to be caller-rights procedures. An owner-rights ancestor causes descendants to behave as owner-rights procedures.
Use a call-path classification before making a privilege prediction:
| Declared path from the root CALL | Effective classification of final node |
|---|---|
| CALLER → CALLER | Caller rights |
| CALLER → OWNER | Owner rights |
| OWNER → CALLER | Owner rights |
| CALLER → OWNER → CALLER | Owner rights |
This table classifies rights behavior; it is not a complete ledger of which role owns each procedure or every privilege available at each node. For a real nested failure, record each routine's owner, signature, grants, objects, and context. Do not collapse all owners into a fictitious shared administrator.
The important counterexample is the third row. Reading the child's declaration alone produces the wrong answer. The caller-rights child does not restore the original unrestricted caller environment beneath an owner-rights parent.
Our local classification model tracks whether an owner ancestor has been encountered. It checks these four paths against the documented rule. It does not emulate Snowflake's authorization engine or demonstrate that any particular nested SQL body will succeed.
What session state does the procedure inherit or change?
A change in execution identity does not make the call independent of its session. The rights guide distinguishes the caller's environment from the owner's procedure context: caller-rights procedures can use and change caller session state; owner-rights procedures use their creation database/schema, inherit the caller's warehouse, restrict session-variable access and mutation, and still depend on a documented subset of session parameters.
That is a compact rule, so test it with concrete inputs. Suppose the caller has current database WORK_DB, schema SCRATCH, and warehouse ANALYTICS_WH, while the procedure was created in APP_DB.PUBLIC.
A question about an unqualified table name is now a name-resolution question as well as a permissions question. Prefer fully qualified names when the operation is intended to target one fixed object. A qualified name removes one ambiguity; it does not supply missing privileges or validate a sensitive input.
For this exercise, the owner summary uses SECURE.DATA.SECRETS explicitly. Its intended behavior therefore does not change merely because a caller switches its current database. We can explain that design without asserting that owner-rights code ignores every session parameter.
Why does connection reuse create a separate failure case?
Snowflake's driver connection guidance describes stateful sessions and warns that shared or reused connections can affect database, schema, role, transactions, query context, and last-query tracking. Returning a connection to a pool does not create a new session by itself.
Our original caller-rights trace uses a session variable named PH_REQUEST_TAG. Request A temporarily changes it from baseline to request-A while running a calculation. The calculation throws before the normal reset. Request B later receives the same connection and reads request-A.
| Step | Unsafe handler's state | Handler with restoration |
|---|---|---|
| Borrow connection | baseline | baseline |
| Set temporary request value | request-A | request-A |
| Calculation fails | Error propagates | Error propagates |
| Cleanup completes | No restoration happened | Previous value restored |
| Next borrower reads variable | request-A | baseline |
Keep the original calculation failure visible so the next borrower is protected without disguising a failed request. The repair is not to suppress it and return a plausible summary. Restore the old state on every exit path, and preserve the distinction between a previously absent variable and an existing variable with a value.
A reset to an arbitrary default can also damage caller state. If the original variable was absent, cleanup should restore absence; if it held a value, cleanup should restore that value. An explicit argument often avoids the need to mutate session state at all.
The local model verifies success, exception, existing-value restoration, and absent-value restoration. It models the application's contract in Python; it is not a claim that a Python dictionary implements Snowflake SQL variable behavior.
How would you debug a failing CALL?
Start with an execution record: current role, warehouse, database/schema, procedure signature and mode, procedure owner, and the failing statement. A failure at invocation and a failure inside the handler are different boundaries.
Next, reproduce the failed invocation or qualified table operation with the intended test identity in a development account. If invocation is denied, inspect entry-point and container grants. If a caller-rights table read is denied, compare the caller's active privileges with the table's requirements. If an owner-rights read is denied, inspect the owner's ledger and the actual qualified object, including any reference path.
Then vary one input at a time. Keep the role fixed while changing the current schema. Keep the schema fixed while changing the role. Execute the same call before and after a failed request on a reused connection. These comparisons separate permissions, resolution, and stale state instead of treating every failure as “Snowflake permissions.”
Do not verify a repair only with an administrator. An administrator can hide the missing grant that the intended caller still needs. Check the allowed path and a deliberately forbidden direct-table path. The intended repair must preserve the allowed procedure call and the denied direct-table read under this ledger.
For pooled sessions, also verify that active transaction work is resolved before return, as the driver guidance requires. That is connection hygiene, not a blanket assertion that every procedure call is an independently atomic transaction.
Practice the boundaries with linked exercises
| Question | Transferable practice |
|---|---|
| Implement a Snowflake Query Client | Make connection and query context explicit. |
| Review getEvents endpoint for readability, performance, scalability, security | Review input validation, security boundaries, and precise failure behavior. |
| Design authorization and audit logging systems | Separate authorization from an auditable execution record. |
| Design an API to Deep-Copy a Dataset with Its Tasks, Model Responses and Grades | Define a controlled operation across related protected records. |
| Migrate a Continuously Used Database | Preserve state and behavior while a database system changes. |
Use Design authorization and audit logging systems to rehearse the final explanation: name the authorized identity, the limited operation, the denied operation, and the evidence recording both. Then return to the four-row procedure ledger and predict every result without changing the caller's privileges to make the example easier.