Unity Catalog functions

Dulce42 · ‎11-06-2025

Hi community,

Recent days I created a SQL function into my unity catalog for my trusted assets in my genie space, I gave permission to my users accounts for they saw these function, however yesterday I modified my functions using the command "CREATE OR REPLACE FUNCTION" and today a coworker said me what he needs permission again :S

How can I update my function without deleting its permissions? Someone can help me?

Louis_Frolio · ‎11-06-2025

Greetings @Dulce42 , this is a known gotcha with Unity Catalog functions: updating a function with CREATE OR REPLACE FUNCTION currently replaces the object and drops its grants, so downstream users lose EXECUTE permission and need to be re-granted. This behavior is tracked internally and differs from tables, where CREATE OR REPLACE preserves privileges.

Why this happened

CREATE OR REPLACE FUNCTION replaces the function object (keeping the name/signature but recreating the object), which resets grants on that function. This is a documented product issue under UC-5574.
In contrast, CREATE OR REPLACE TABLE explicitly preserves history and granted privileges, row filters, and column masks, which is why you don’t see this problem with tables.
To call a Unity Catalog UDF, users need three permissions: EXECUTE on the function and USE CATALOG and USE SCHEMA on its parent catalog and schema.

The durable fix (avoid losing permissions on updates)

Grant at the container level instead of per-function. Unity Catalog supports privilege inheritance, so granting EXECUTE at the schema or catalog level applies to all current and future functions in that scope—including after you replace a function.

Example pattern for your “genie” space: Grant baseline access to the container: GRANT USE CATALOG ON CATALOG genie_catalog TO group-or-user.

GRANT USE SCHEMA ON SCHEMA genie_catalog.trusted_assets TO group-or-user.
- Grant function-run access that survives replacements:
GRANT EXECUTE ON SCHEMA genie_catalog.trusted_assets TO group-or-user.

This way, any CREATE OR REPLACE FUNCTION you do inside that schema won’t require re-granting per function—the EXECUTE privilege is inherited for both existing and newly replaced functions.

If you must keep per-function grants

If policy requires per-function grants (not schema-level), then reapply grants after each replace. A simple operational pattern is: * Replace the function body without changing its signature (required by the syntax).

Immediately re-grant EXECUTE on the function:
- GRANT EXECUTE ON FUNCTION genie_catalog.trusted_assets.my_function TO group-or-user.

You can automate this by keeping the list of principals to grant and running the GRANT statements right after each deploy.

Example SQL snippets

Replace a function (signature unchanged) and rely on schema-level inheritance:

``sql
-- Ensure callers have container access
GRANT USE CATALOG ON CATALOG genie_catalog TO

team-group

; 
GRANT USE SCHEMA ON SCHEMA genie_catalog.trusted_assets TO

team-group`;

-- Ensure callers inherit EXECUTE on all functions in the schema GRANT EXECUTE ON SCHEMA genie_catalog.trusted_assets TO team-group;

-- Now safely update your function without worrying about per-function grants CREATE OR REPLACE FUNCTION genie_catalog.trusted_assets.calc_metric(x DOUBLE) RETURNS DOUBLE LANGUAGE SQL RETURN x * x; ```

If you stick to per-function grants: ```sql CREATE OR REPLACE FUNCTION genie_catalog.trusted_assets.calc_metric(x DOUBLE) RETURNS DOUBLE LANGUAGE SQL RETURN x * x;

GRANT EXECUTE ON FUNCTION genie_catalog.trusted_assets.calc_metric TO user@example.com; ```

Notes and checks

You cannot change the function’s parameter list or types when using OR REPLACE; only the body and return type can be updated if the signature stays the same.
If your coworker still can’t call the function even after EXECUTE, double-check they also have USE CATALOG and USE SCHEMA on the parent container.
This “grants drop on replace” problem is acknowledged for functions today; schema-level EXECUTE inheritance is the recommended mitigation until the product behavior changes.

Hope this makes things easier.

Cheers, Louis.

View solution in original post