SQL Stored Procedures in Databricks now support SQL SECURITY DEFINER, and it is Generally Available on SQL warehouses, Serverless compute and All-Purpose clusters (DBR 19+). A SECURITY DEFINER procedure runs with its owner's privileges, not the caller's. Administrators can grant EXECUTE on a procedure without granting broad MODIFY access on production tables.
The Problem: every caller needed table privileges
Before SECURITY DEFINER, stored procedures in Databricks ran only as SECURITY INVOKER. Invoker procedures require that every statement in the procedure body be checked against the permissions of the user who called it. So if a procedure inserted into an accounting table, every analyst allowed to run it also needed write access to that accounting table.
That forces a bad trade-off:
MODIFY privileges on tables so users who need to run the procedure could do so. Nothing prevents those users from writing to the table outside the procedure.What customers wanted was simple: let a user run this specific operation without giving them excessive permissions to the table.
With SECURITY DEFINER, Unity Catalog checks access for everything in the procedure body against the procedure owner, not the caller. Callers no longer need write privileges such as MODIFY on the tables the procedure changes. They do need EXECUTE on the procedure, USE CATALOG and USE SCHEMA on the catalogs and schemas involved, and SELECT on those catalogs or schemas.
A few behaviors make DEFINER procedures predictable and safe:
session_user() still returns the person who issued the CALL, so audit columns will record who actually ran the operation.SECURITY DEFINER procedures work alongside Unity Catalog row filters and column masks.Suppose a data engineering team owns a financial table that represents a ledger of adjustments. Analysts need to submit adjustments, but they shouldn't have direct write access to the ledger.
Step 1: The owner creates a DEFINER procedure.
|
Step 2: Grant EXECUTE, not MODIFY.
|
Analysts keep read access (SELECT) but do not require MODIFY privilege on the apply_adjustment table.
Step 3: Analysts call the procedure.
|
The insert succeeds even though finance-analysts has no MODIFY privilege on finance.ledger.adjustments. A direct INSERT INTO finance.ledger.adjustments by the same analyst would throw an insufficient permissions error.
SQL SECURITY DEFINER for Stored Procedures is Generally Available on:
For now, you must specify SECURITY INVOKER or SECURITY DEFINER explicitly in CREATE PROCEDURE. Soon SECURITY DEFINER will become the default behavior for new procedures.
To learn more, see the documentation for CREATE PROCEDURE and Authorized user.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.