Course contents

SnowPro Core COF-C03 Study GuidePart 6: Database objects and platformStored Procedures

Stored Procedures

Programmability & Semi-Structured Data

Snowflake as an application platform: server-side logic, custom functions, and the semi-structured data types that let JSON, Avro, and Parquet live natively alongside relational columns.

Stored Procedures

Definition

A stored procedure encapsulates procedural logic, loops, conditionals, multiple statements, error handling, in JavaScript, Python, Java, Scala, or Snowflake Scripting SQL, invoked with CALL. Unlike a function, it can perform DDL and DML as a first-class part of its job.

CREATE PROCEDURE archive_old_orders(cutoff_date DATE)
  RETURNS STRING
  LANGUAGE SQL
  AS
  $$
  BEGIN
    INSERT INTO orders_archive
      SELECT * FROM orders WHERE order_date < :cutoff_date;
    DELETE FROM orders WHERE order_date < :cutoff_date;
    RETURN 'Archive complete';
  END;
  $$;

CALL archive_old_orders('2025-01-01');

Owner's rights vs caller's rights

By default, a stored procedure runs with the privileges of its owner (owner's rights), regardless of who calls it, useful for giving a limited role safe access to a narrow, controlled operation on objects it couldn't otherwise touch directly. Declaring EXECUTE AS CALLER flips this: the procedure runs with whatever privileges the calling role holds, appropriate when the procedure should only be able to do what the caller could already do themselves, and no more.

Exam-ready rule

Use caller's rights when the procedure needs to run with the privileges of the role that invoked it, so its access is bounded by whatever that caller already has. Use owner's rights (the default) when the procedure needs to operate on objects the caller doesn't have direct privileges on, a common pattern for giving a restricted role safe, narrow access to sensitive operations.

Amine, working as ANALYST, runs the same CALL archive_old_orders() under two settings. With owner's rights, the default, the body runs with the privileges of the owner role DATA_ENGINEER, so the DELETE succeeds. With EXECUTE AS CALLER the body runs as ANALYST, which has no DELETE privilege, so the DELETE is refused.OWNER'S RIGHTS · THE DEFAULTEXECUTE AS CALLERUSERAminerole ANALYSTCALL archive_old_orders()Body runs asDATA_ENGINEERthe procedure ownerDELETE succeedsthe owner may deleteBody runs asANALYSTthe callerDELETE refusedANALYST can’t deleteCALLCALL

USERAminerole ANALYSTCALL archive_old_orders()

  • CALL

    OWNER'S RIGHTS · THE DEFAULT

    Body runs asDATA_ENGINEERthe procedure owner

    DELETE succeedsthe owner may delete

  • CALL

    EXECUTE AS CALLER

    Body runs asANALYSTthe caller

    DELETE refusedANALYST can’t delete

Figure 6.8 Same CALL, two outcomes: the procedure's setting decides whose privileges the body uses. Owner's rights (the default) runs the body as DATA_ENGINEER, so the DELETE succeeds; EXECUTE AS CALLER runs it as ANALYST, which can't delete.

Owner's rights is controlled elevation: Thomas exposes one safe, narrow operation to roles that couldn't touch the tables directly, and the procedure body is the fence. Caller's rights is for utilities that should never do more than the person invoking them can, and it's the only mode that can read the caller's session state (current schema, session variables).