cancel
Showing results for 
Search instead for 
Did you mean: 
Technical Blog
Explore in-depth articles, tutorials, and insights on data analytics and machine learning in the Databricks Technical Blog. Stay updated on industry trends, best practices, and advanced techniques.
cancel
Showing results for 
Search instead for 
Did you mean: 
dbkent
Databricks Employee
Databricks Employee

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.

sql-security-definer-ga-hero.png

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:

  • Over-permissioning. Teams needed to give broad 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.
  • Overhead caused by workarounds. Teams created shared service principals, use jobs that leverage these service principals, or try custom apps to perform writes on users' behalf. Every option adds overhead to manage and maintain, and makes it much harder to audit who is modifying the data.

What customers wanted was simple: let a user run this specific operation without giving them excessive permissions to the table.

The solution: SQL SECURITY DEFINER

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:

  • Stable name resolution. Unqualified names in the body resolve against the catalog and schema that were current when the procedure was created, not the caller's session. Session-scoped objects (temp views, temp tables, session variables, etc) are excluded from resolution and can be referenced only when using session schema qualifier.
  • Captured SQL configuration. Settings such as time zone are captured at creation time and apply on every call.
  • Accurate auditing. session_user() still returns the person who issued the CALL, so audit columns will record who actually ran the operation.
  • Works with fine-grained access control. SECURITY DEFINER procedures work alongside Unity Catalog row filters and column masks.

How it works

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.

CREATE OR REPLACE PROCEDURE finance.ops.apply_adjustment(
acct STRING,
amt DECIMAL(18,2)
)
SQL SECURITY DEFINER
COMMENT 'Governed write path for ledger adjustments'
BEGIN
INSERT INTO finance.ledger.adjustments
VALUES (acct, amt, session_user(), current_timestamp());
END; 

Step 2: Grant EXECUTE, not MODIFY.

GRANT USE CATALOG ON CATALOG finance TO `finance-analysts`;
GRANT USE SCHEMA ON SCHEMA finance.ops TO `finance-analysts`;
GRANT USE SCHEMA ON SCHEMA finance.ledger TO `finance-analysts`;
GRANT SELECT ON SCHEMA finance.ledger TO `finance-analysts`;
GRANT EXECUTE ON PROCEDURE finance.ops.apply_adjustment TO `finance-analysts`;

Analysts keep read access (SELECT) but do not require MODIFY privilege on the apply_adjustment table.

Step 3: Analysts call the procedure.

CALL finance.ops.apply_adjustment('account-001', -125.00);

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.

Getting started

SQL SECURITY DEFINER for Stored Procedures is Generally Available on:

  • Databricks SQL (Serverless and Classic warehouses)
  • Serverless compute
  • Classic / All-Purpose clusters on Databricks Runtime 19 and above

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.