Authorized user and session user

Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime

When Azure Databricks runs a SQL statement it tracks two distinct users:

  • Session user — the user who is connected to Azure Databricks and issued the statement. The session user is fixed for the lifetime of the SQL connection. It does not change while a statement is running, even when the statement reaches into views, SQL UDFs, or stored procedures.
  • Authorized user — the user whose privileges are checked when the currently executing statement reads or writes objects. The authorized user can change as execution crosses into a view body, a SQL UDF body, or a SQL SECURITY DEFINER procedure body.

The session_user function returns the session user.

Warning

The current_user and user functions are aliases that also return the session user, not the authorized user. In standard SQL, CURRENT_USER returns the authorized user; in Azure Databricks it does not. Use session_user to refer to the session user explicitly.

How the authorized user evolves

Each invocation of a view, SQL UDF, or stored procedure pushes a new entry on the call stack. The authorized user for a statement is determined by the topmost stack entry that fixes an owner:

Object Authorized user inside the body
View The view owner
SQL UDF The function owner
CREATE PROCEDURE ... SQL SECURITY DEFINER The procedure owner (definer)
CREATE PROCEDURE ... SQL SECURITY INVOKER The authorized user of the calling statement (inherited)

Views, SQL UDFs, and SQL SECURITY DEFINER procedures set the authorized user to their owner for all statements in the body. A SQL SECURITY INVOKER procedure inherits the authorized user from its caller.

When a body finishes executing and control returns to the caller, the authorized user pops back to whatever it was at the call site.

The session user, in contrast, never changes during a single connection. Inside a SQL SECURITY DEFINER procedure body, session_user() still returns the user who issued the original statement.

Privileges checked at each layer

Azure Databricks checks privileges against the authorized user at each layer of execution:

  • The session user must hold EXECUTE on the top-level procedure or function and SELECT on the top-level view. The session user must also have USE CATALOG and USE SCHEMA on the parent containers.
  • Inside a SQL SECURITY INVOKER procedure body, all statements use the session user's privileges, including any nested EXECUTE calls to other routines.
  • Inside a SQL SECURITY DEFINER procedure body, a view body, or a SQL UDF body, all statements use the owner's privileges. The session user only needs the privilege to invoke the outer routine, not privileges on the objects the body references.

Example

This example chains views, SQL UDFs, and stored procedures owned by different users. Each layer reads from the layer below, so authorization traverses the full stack. The example uses three users (Athos, Porthos, and Aramis) and traces what happens when Aramis, who has access only to the top-level procedures, invokes the chain.

Setup

Athos creates a two-column table and grants Porthos read access:

-- Run as Athos.
> CREATE TABLE t(a INT, b INT);
> INSERT INTO t VALUES (1, 10), (2, 20), (3, 30);

> GRANT SELECT ON TABLE t TO `porthos@musketeers.fr`;

Porthos creates a view over Athos's table and grants Athos read access on the view:

-- Run as Porthos.
> CREATE VIEW v_p AS
    SELECT a, b * 100 AS b100 FROM t;

> GRANT SELECT ON VIEW v_p TO `athos@musketeers.fr`;

Athos creates a SQL UDF that reads from Porthos's view, and grants Porthos EXECUTE on it:

-- Run as Athos.
> CREATE FUNCTION f_a(p INT) RETURNS INT
    RETURN (SELECT b100 FROM v_p WHERE a = p);

> GRANT EXECUTE ON FUNCTION f_a TO `porthos@musketeers.fr`;

Porthos creates a SQL UDF that aggregates results from Athos's UDF, and a SQL SECURITY DEFINER procedure that exposes the result:

-- Run as Porthos.
> CREATE FUNCTION f_p() RETURNS INT
    RETURN f_a(1) + f_a(2) + f_a(3);

> CREATE PROCEDURE p_def()
    LANGUAGE SQL
    SQL SECURITY DEFINER
    AS BEGIN
      SELECT f_p();
    END;

Athos creates a SQL SECURITY INVOKER procedure that calls Porthos's procedure:

-- Run as Athos.
> CREATE PROCEDURE p_inv()
    LANGUAGE SQL
    SQL SECURITY INVOKER
    AS BEGIN
      CALL p_def();
    END;

Finally, Athos and Porthos each grant Aramis what he needs to invoke the chain. Because p_inv is a SQL SECURITY INVOKER procedure, its body runs as the user who called it (Aramis), and so Aramis must independently have EXECUTE on p_def:

-- Run as Athos.
> GRANT EXECUTE ON PROCEDURE p_inv TO `aramis@musketeers.fr`;

-- Run as Porthos.
> GRANT EXECUTE ON PROCEDURE p_def TO `aramis@musketeers.fr`;

Aramis has no privileges on t, f_a, v_p, or f_p.

Aramis invokes the chain

Aramis issues:

-- Run as Aramis.
> CALL p_inv();

The call chain unfolds as follows, each arrow crossing into a new body:

Aramis's session
   │
   │ CALL
   ▼
p_inv()      (Athos, SQL SECURITY INVOKER)
   │
   │ CALL
   ▼
p_def()      (Porthos, SQL SECURITY DEFINER)
   │
   │ SELECT
   ▼
f_p()        (Porthos's SQL UDF)
   │
   │ invokes
   ▼
f_a(p)       (Athos's SQL UDF)
   │
   │ SELECT
   ▼
v_p          (Porthos's view)
   │
   │ SELECT
   ▼
t            (Athos's table)

The following table walks through the statements as they execute.

Step Statement Where it runs Authorized user session_user()
1 CALL p_inv() Aramis's session Aramis Aramis
2 CALL p_def() Body of p_inv (SQL SECURITY INVOKER) Aramis (inherited from caller) Aramis
3 SELECT f_p() Body of p_def (SQL SECURITY DEFINER) Porthos (procedure owner) Aramis
4 RETURN f_a(1) + f_a(2) + f_a(3) Body of SQL UDF f_p Porthos (function owner) Aramis
5 RETURN (SELECT b100 FROM v_p WHERE a = p) Body of SQL UDF f_a Athos (function owner) Aramis
6 SELECT a, b * 100 AS b100 FROM t Body of view v_p Porthos (view owner) Aramis

When execution unwinds, the authorized user pops back layer by layer until control returns to Aramis's session at step 1.

  • Last updated on