Use Reusable UDF for Row-level Filter¶

In native row filtering, whenever a policy changes, the existing row filter attached to the table is typically dropped and recreated based on the updated policy logic.

With the reusable UDF approach, a stable master UDF is attached to the table only once. The master UDF reads entitlement data and routes the request to the appropriate sub-UDF for filtering. When policies change, only the sub-UDF logic or entitlement mappings are updated, without changing the row filter attached to the table.

This approach provides:

• No need to drop and recreate the table row filter for every policy change
• Reusable master and sub-UDFs across multiple tables and policies
• Centralized policy management through entitlement tables and UDF metadata

Prerequisites¶

Complete the following in Unity Catalog before you enable reusable master UDF RLS on the connector or rely on PolicySync to apply row filters.

These two items are required:

1. Entitlement table

   • What it does. Stores which principal (user or group) gets which filter UDF on which protected table. The master UDF reads this table at query time to resolve the caller’s assignment, so the table must already exist and stay in sync with policy before row filters that depend on it are evaluated.

   • Which property it uses. CONNECTOR_DATABRICKS_UNITY_CATALOG_RLS_ENTITLEMENT_TABLE_FQDN — set this to the fully qualified name of the entitlement table. The master UDF and connector must use the same FQDN.

   • How to create it. Create and populate the table in Unity Catalog with five columns: who (principal_id, principal_type), which table (table_fqn), which filter UDF (filter_name), and when the row last changed (updated_at). Replace <catalog_name>, <schema_name>, and <table_name> with identifiers that match the FQDN in CONNECTOR_DATABRICKS_UNITY_CATALOG_RLS_ENTITLEMENT_TABLE_FQDN.

     SQL
     1 2 3 4 5 6 7	CREATE TABLE <catalog_name>.<schema_name>.<table_name> ( principal_id STRING, table_fqn STRING, filter_name STRING, updated_at TIMESTAMP, principal_type STRING );

2. Registry catalog and privacera_security schema

   • What it does. Provides the catalog and schema where the connector creates and replaces reusable master and sub-UDF objects in Unity Catalog.

   • Which property it uses. CONNECTOR_DATABRICKS_UNITY_CATALOG_REUSABLE_UDF_REGISTRY_CATALOG — set this to the catalog that acts as the root for UDF registration. The schema name privacera_security under that catalog is fixed (not configurable).

   • How to create it. Under the registry catalog, create the privacera_security schema before UDFs can be deployed. Replace <catalog_name> with the same value you use for CONNECTOR_DATABRICKS_UNITY_CATALOG_REUSABLE_UDF_REGISTRY_CATALOG.

     SQL
     1	CREATE SCHEMA IF NOT EXISTS <catalog_name>.privacera_security;

Portal: UDF-supported services¶

Set this on the Privacera Manager in custom-vars/vars.portal.yml. If that file is not already under custom-vars, copy it from sample-vars first (the -n flag skips the copy when the file already exists).

1. SSH to the instance where Privacera Manager is installed.

2. Navigate to the configuration directory:

   Bash
   1	cd ~/privacera/privacera-manager/config

3. Copy vars.portal.yml into custom-vars if it is not already there:

   Bash
   1	cp -n sample-vars/vars.portal.yml custom-vars/vars.portal.yml

4. Edit custom-vars/vars.portal.yml and set:

   YAML
   1	PORTAL_UDF_SUPPORTED_SERVICES: "databricks_unity_catalog"

Leave PORTAL_UDF_SUPPORTED_SERVICES unset or empty to show UDF options for all supported services. Use a comma-separated list to include multiple services.

Enable reusable master UDF RLS (connector)¶

These settings are Databricks Unity Catalog connector variables in Privacera Manager.

On self-managed Privacera Manager, use the procedure below.

1. SSH to the instance where Privacera Manager is installed.

2. Run the following command to open the .yml file to be edited.

   Multiple connector instances

   If you have multiple connectors, replace instance1 with the appropriate connector instance name.

   Bash
   1	vi ~/privacera/privacera-manager/config/custom-vars/connectors/databricks-unity-catalog/instance1/vars.connector.databricks.unity.catalog.yml

3. Set the following properties to enable the reusable master UDF row filter flow for the Databricks Unity Catalog connector (replace <catalog_name>.<schema_name>.<table_name> with your entitlement table FQDN and <catalog_name> for the UDF registry catalog):

   YAML
   1 2 3 4 5	CONNECTOR_DATABRICKS_UNITY_CATALOG_ENABLE_ROW_FILTER: "false" CONNECTOR_DATABRICKS_UNITY_CATALOG_ENABLE_REUSABLE_MASTER_UDF_RLS: "true" CONNECTOR_DATABRICKS_UNITY_CATALOG_RLS_ENTITLEMENT_TABLE_FQDN: "<catalog_name>.<schema_name>.<table_name>" CONNECTOR_DATABRICKS_UNITY_CATALOG_REUSABLE_UDF_REGISTRY_CATALOG: "<catalog_name>" CONNECTOR_RANGER_ENABLE_IMPLICIT_UDF_ENRICHER: "true"

4. Once the properties are configured, run the following commands to update your Privacera Manager platform instance:

   Step 1 - Setup which generates the helm charts. This step usually takes few minutes.

   Bash
   1 2	cd ~/privacera/privacera-manager ./privacera-manager.sh setup

   Step 2 - Apply the Privacera Manager helm charts.

   Bash
   1 2	cd ~/privacera/privacera-manager ./pm_with_helm.sh upgrade

   Step 3 - (Optional) Post-installation step which generates Plugin tar ball, updates Route 53 DNS and so on. This step is not required if you are updating only connector properties.

   Bash
   1 2	cd ~/privacera/privacera-manager ./privacera-manager.sh post-install

5. After the Privacera Manager update completes, turn on the User Defined Functions (UDF) UI in the Privacera Portal (in addition to PORTAL_UDF_SUPPORTED_SERVICES in vars.portal.yml, if you set it) so operators can use this feature from the UI:

   1. Log in to the Privacera Portal.
   2. Open Settings → System Configurations.
   3. Add or enable the UI feature entry for the User Defined Functions module (for example Enable UI Feature: User Defined Functions Module, or the equivalent control shown for your Portal version). Save the change if the screen prompts you to.

Example¶

1. Consent and protected tables — Illustrative only: create any consent, dimension, or fact tables your sub-UDFs reference, using catalog and schema names that fit your environment (for example <catalog_name>.<schema_name>.consent_table and your protected fact table). You are not required to copy names from the walkthrough later on this page.

Entitlement table¶

See Prerequisites for what the table does, which connector property sets its FQDN (CONNECTOR_DATABRICKS_UNITY_CATALOG_RLS_ENTITLEMENT_TABLE_FQDN), and how to create it. Privacera keeps rows in sync with policy.

Typical values in filter_name correspond to consent tiers, for example:

The values stored in filter_name must exactly match the string literals in the master UDF CASE expression (see Master UDF)—for example bare UDF names such as check_7216_only, or fully qualified names, depending on what your connector writes into filter_name. Use one convention consistently.

Table on which Row Filter Applies¶

1
2
3
4
5

CREATE TABLE IF NOT EXISTS acc_catalog.compliance.customer_transactions (
  customer_id STRING,
  amount DOUBLE,
  transaction_date DATE
);

Consent table (used by filter UDFs)¶

1
2
3
4
5

CREATE TABLE IF NOT EXISTS acc_catalog.compliance.consent_table (
  customer_id STRING,
  consent_7216_global BOOLEAN,
  c2_consent BOOLEAN
);

Example data: consent_table¶

Each sample table on this page lists 10 rows for brevity.

Example data: customer_transactions¶

Sub-UDFs (consent checks)¶

When you define UDFs in Ranger or in SQL that the connector will deploy, use only the function name in CREATE OR REPLACE FUNCTION (no catalog.schema. prefix). The connector creates the function under <registry_catalog>.privacera_security.<udf_name>, where <registry_catalog> is the value of CONNECTOR_DATABRICKS_UNITY_CATALOG_REUSABLE_UDF_REGISTRY_CATALOG.

Inside the function body, continue to use fully qualified names for tables such as the consent table when they are not in the same schema as the UDF (the examples below still use acc_catalog.compliance.consent_table as illustration only).

The strings you store in filter_name and compare in the master CASE must match whatever naming convention you choose (here, bare UDF names).

Sub-UDF 1: 7216-only logic¶

1
2
3
4
5
6

CREATE OR REPLACE FUNCTION check_7216_only(cust_id STRING)
RETURNS BOOLEAN
RETURN EXISTS (
  SELECT 1 FROM acc_catalog.compliance.consent_table
  WHERE customer_id = cust_id AND consent_7216_global = true
);

Sub-UDF 2: C2-only logic¶

1
2
3
4
5
6

CREATE OR REPLACE FUNCTION check_c2_only(cust_id STRING)
RETURNS BOOLEAN
RETURN EXISTS (
  SELECT 1 FROM acc_catalog.compliance.consent_table
  WHERE customer_id = cust_id AND c2_consent = true
);

Sub-UDF 3: Hybrid (7216 or C2)¶

1
2
3
4
5
6
7

CREATE OR REPLACE FUNCTION check_7216_or_c2(cust_id STRING)
RETURNS BOOLEAN
RETURN EXISTS (
  SELECT 1 FROM acc_catalog.compliance.consent_table
  WHERE customer_id = cust_id
    AND (consent_7216_global = true OR c2_consent = true)
);

Master UDF¶

The master UDF resolves the caller’s principal and table to a single filter_name, then invokes the matching sub-UDF. It uses current_user() and is_account_group_member(principal_id) so both users and account-level groups can appear in principal_id.

Use a bare function name in CREATE OR REPLACE FUNCTION when the connector deploys it; sub-UDF calls in the THEN clauses can use the same bare names if all functions are deployed in privacera_security.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19

CREATE OR REPLACE FUNCTION master_rls_filter(cust_id STRING, t_name STRING)
RETURNS BOOLEAN
RETURN (
  WITH current_assignment AS (
    SELECT filter_name
    FROM acc_catalog.compliance.table_entitlements
    WHERE (principal_id = current_user() OR is_account_group_member(principal_id))
      AND table_fqn = t_name
    LIMIT 1
  )
  SELECT
    CASE
      WHEN filter_name = 'acc_catalog.privacera_security.check_7216_only' THEN acc_catalog.privacera_security.check_7216_only(cust_id)
      WHEN filter_name = 'acc_catalog.privacera_security.check_c2_only' THEN acc_catalog.privacera_security.check_c2_only(cust_id)
      WHEN filter_name = 'acc_catalog.privacera_security.check_7216_or_c2' THEN acc_catalog.privacera_security.check_7216_or_c2(cust_id)
      ELSE FALSE
    END
  FROM current_assignment
);

If no row exists in current_assignment, the SELECT ... FROM current_assignment returns no row; treat that as access denied in your deployment (some environments wrap this in COALESCE or an outer default—follow your security standard).

Row filter metadata (Privacera / policy side)¶

Policy or connector metadata can reference the concrete filter UDF and the master UDF, plus the column(s) used as the row key. Example:

1
2
3
4
5

{
  "filter": "check_7216_only",
  "master_udf": "master_rls_filter",
  "columns": ["customer_id"]
}

The connector can resolve filter and master_udf to the full Unity Catalog name under your registry catalog and privacera_security schema. If your integration expects fully qualified names in this JSON instead, use the same form everywhere (metadata, filter_name column, and CASE branches).

Policy lifecycle and Unity Catalog behavior¶

You define an RLF policy with master UDF, sub-UDF (filter), and column(s). PolicySync pushes changes to Unity Catalog. The master row filter stays on the table; most edits update entitlements or UDF SQL instead of dropping the row filter each time.

Query path: row filter → master UDF → entitlement lookup → sub-UDF on row key column(s).

Example: populate entitlements and attach the row filter¶

Manual equivalent of Created above. Example uses acc_catalog.compliance.table_entitlements; align FQDN and principal_type (USER / GROUP) with your deployment.

1
2
3
4
5
6
7
8
9

INSERT INTO acc_catalog.compliance.table_entitlements
  (principal_id, table_fqn, filter_name, updated_at, principal_type)
VALUES (
  current_user(),
  'acc_catalog.compliance.customer_transactions',
  'acc_catalog.privacera_security.check_7216_only',
  current_timestamp(),
  'USER'
);

Attach the master function as the table’s row filter. Pass the same fully qualified table name string that entitlement rows use in table_fqn as the second argument to master_rls_filter (Unity Catalog evaluates the row filter in the context of the protected table):

1
2
3

ALTER TABLE acc_catalog.compliance.customer_transactions
SET ROW FILTER acc_catalog.privacera_security.master_rls_filter
ON (customer_id, 'acc_catalog.compliance.customer_transactions');

Adjust the ON (...) clause to match your Databricks version’s row filter signature requirements and your master UDF parameter order.

Ranger UDF registry API¶

Register UDF definitions in Ranger so PolicySync (or related services) can create or update the corresponding functions in Unity Catalog. Replace <RANGER_HOST> with your Ranger admin host and use valid credentials or a service token. serviceName must match your Databricks Unity Catalog service definition in Ranger (commonly privacera_databricks_unity_catalog).

Register a master UDF (udfType: 1)¶

udfText must be valid Databricks SQL: the CTE must select the same column names the outer CASE references (for example filter_name from table_entitlements). For the function being created, use a bare name in CREATE OR REPLACE FUNCTION (no catalog.schema.); the connector deploys it under <registry_catalog>.privacera_security. Prefer fully qualified names in the function body for tables such as table_entitlements and consent tables unless they resolve by search path.

1
2

POST https://<RANGER_HOST>/service/public/v2/api/udf
Content-Type: application/json

1
2
3
4
5
6
7
8

{
  "serviceName": "privacera_databricks_unity_catalog",
  "name": "master_rls_filter",
  "udfType": 1,
  "description": "Master RLS filter: resolve entitlement then dispatch to consent sub-UDFs",
  "udfText": "CREATE OR REPLACE FUNCTION acc_catalog.privacera_security.master_rls_filter(cust_id STRING, t_name STRING) RETURNS BOOLEAN RETURN (WITH current_assignment AS (SELECT filter_name FROM acc_catalog.compliance.table_entitlements WHERE (principal_id = current_user() OR is_account_group_member(principal_id)) AND table_fqn = t_name LIMIT 1) SELECT CASE WHEN filter_name = 'acc_catalog.privacera_security.check_7216_only' THEN acc_catalog.privacera_security.check_7216_only(cust_id) WHEN filter_name = 'acc_catalog.privacera_security.check_c2_only' THEN acc_catalog.privacera_security.check_c2_only(cust_id) WHEN filter_name = 'acc_catalog.privacera_security.check_7216_or_c2' THEN acc_catalog.privacera_security.check_7216_or_c2(cust_id) ELSE FALSE END FROM current_assignment)",
  "subUdfNames": ["check_7216_only", "check_c2_only", "check_7216_or_c2"]
}

Use subUdfNames to list dependent Ranger UDF names that must exist (or be created) for this master definition. Adjust the array to match the resources you registered.

Register a sub-UDF (udfType: 2)¶

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

curl -X POST "https://<RANGER_HOST>/service/udf/" \
  -H "Content-Type: application/json" \
  -u "<RANGER_USER>:<RANGER_PASSWORD>" \
  -d '{
    "serviceName": "privacera_databricks_unity_catalog",
    "name": "check_7216_only",
    "udfType": 2,
    "description": "Consent 7216 filter",
    "udfText": "CREATE OR REPLACE FUNCTION check_7216_only(cust_id STRING) RETURNS BOOLEAN RETURN EXISTS (SELECT 1 FROM acc_catalog.compliance.consent_table WHERE customer_id = cust_id AND consent_7216_global = true);"
  }'

Update a UDF (PUT)¶

Use PUT when you change the SQL body or metadata of an existing Ranger UDF record. Replace {id} with the numeric UDF id from Ranger.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

curl -X PUT "https://<RANGER_HOST>/service/udf/{id}" \
  -H "Content-Type: application/json" \
  -u "<RANGER_USER>:<RANGER_PASSWORD>" \
  -d '{
    "serviceName": "privacera_databricks_unity_catalog",
    "name": "master_rls_filter2",
    "udfType": 1,
    "description": "Master RLS filter (removed hybrid filter)",
    "udfText": "CREATE OR REPLACE FUNCTION acc_catalog.privacera_security.master_rls_filter2(cust_id STRING, t_name STRING) RETURNS BOOLEAN RETURN (WITH current_assignment AS (SELECT filter_name FROM acc_catalog.compliance.table_entitlements WHERE (principal_id = current_user() OR is_account_group_member(principal_id)) AND table_fqn = t_name LIMIT 1) SELECT COALESCE((CASE WHEN filter_name = '\''acc_catalog.privacera_security.check_7216_only'\'' THEN acc_catalog.privacera_security.check_7216_only(cust_id) WHEN filter_name = '\''acc_catalog.privacera_security.check_c2_only'\'' THEN acc_catalog.privacera_security.check_c2_only(cust_id) ELSE FALSE END), FALSE) FROM current_assignment);"
  }'