Use Template UDF for Row-level Filter¶

With template-based row-level filtering, you write the row-filter logic once as a Template UDF in Ranger, and row-filter policies refer to it. For each table that has a template row-filter policy, PolicySync fills the template with the principals of the policy items, creates one row-filter function for that table, and attaches it as the table's row filter.

This approach provides:

• One template for many tables, even when the tables name their columns differently
• No hand-written SQL in policies: each policy item names a template, one of its conditions, and the table columns to use
• Template edits that reach every table using the template, without saving any policy
• Fail-closed behavior: if a table's filter cannot be generated, PolicySync leaves the previous filter in place and withholds the table's own grants (for access policies that cover a whole schema, see Limitations)

How it works¶

1. You create a Template UDF (udfType: 3) in Ranger. It declares its parameters (name and type of each column value it receives), its conditionNames (named branches such as CONDITION_US), and a body (udfText) with placeholders.
2. Each row-filter policy item references the template, one of its conditions (condition_name), and the table columns to bind to the parameters, in order.
3. PolicySync renders the template for the table: each referenced condition gets the principals of the items that use it, conditions that no item uses are left out, and the default branch gets every principal of the policy.
4. PolicySync creates the function <table>_row_filter in the table's schema and attaches it with SET ROW FILTER, before it grants access to the table.

Which row-filter mode to use¶

Template-based row filtering has its own flag and does not need CONNECTOR_DATABRICKS_UNITY_CATALOG_ENABLE_ROW_FILTER. A table uses one mode at a time; a service can use native and template row filters on different tables. Template row filters are native Unity Catalog row filters: view-based (secure view) row filtering does not support template policies, so set CONNECTOR_DATABRICKS_UNITY_CATALOG_ENABLE_VIEW_BASED_ROW_FILTER to "false" (see Prerequisites).

Prerequisites¶

• Column loading. PolicySync checks every bound column against the table's columns, so column metadata must be loaded: CONNECTOR_DATABRICKS_UNITY_CATALOG_LOAD_COLUMNS must be "true" (the default). Without it, every template row-filter policy fails with TEMPLATE_COLUMNS_NOT_LOADED.
• No view-based row filtering. CONNECTOR_DATABRICKS_UNITY_CATALOG_ENABLE_VIEW_BASED_ROW_FILTER must be "false" (its default in Privacera Manager is "true"). Secure views do not recognize template references, so a secure view must not be built from a template row-filter policy.
• UDF UI in the Portal. Set PORTAL_UDF_SUPPORTED_SERVICES and enable the User Defined Functions module as described in Portal: UDF-supported services and step 5 of Enable reusable master UDF RLS.

Enable template-based row filters (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:

   YAML
   1 2 3 4	CONNECTOR_DATABRICKS_UNITY_CATALOG_ENABLE_TEMPLATE_BASED_ROW_FILTER: "true" CONNECTOR_RANGER_ENABLE_IMPLICIT_UDF_ENRICHER: "true" CONNECTOR_DATABRICKS_UNITY_CATALOG_LOAD_COLUMNS: "true" CONNECTOR_DATABRICKS_UNITY_CATALOG_ENABLE_VIEW_BASED_ROW_FILTER: "false"

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

Create a template¶

Create the template from the User Defined Functions page in the Privacera Portal (UDF type Template), or with the Ranger UDF API.

Template body¶

The body is the SQL expression the generated function returns. It is not a CREATE FUNCTION statement: PolicySync builds the function around it.

• Wrap each condition's branch in <CONDITION_X> … </CONDITION_X> tags, one block per name in conditionNames. Blocks that no policy item references are removed from the generated function.
• Put exactly one ${PRINCIPAL_LIST} inside each block. PolicySync replaces it with the principals of the items that reference that condition, as a quoted, comma-separated list.
• Use ${ALL_PRINCIPAL_LIST} (required) outside the blocks. PolicySync replaces it with every principal of the policy. Use it for the default branch, which decides what a caller who matches none of the template's conditions sees.
• Refer to the table's columns only through the parameter names. The body can read other tables (for example a consent table) by their fully qualified names.

Example: filter by country¶

Template with one parameter and two conditions:

1
2
3
4
5
6
7
8
9

{
  "serviceName": "privacera_databricks_unity_catalog",
  "name": "country_filter",
  "udfType": 3,
  "description": "Row filter by country",
  "parameters": [ { "name": "p_country", "type": "STRING" } ],
  "conditionNames": [ "CONDITION_US", "CONDITION_UK" ],
  "udfText": "CASE WHEN NOT current_user() IN (${ALL_PRINCIPAL_LIST}) THEN TRUE ELSE (CASE <CONDITION_US>WHEN current_user() IN (${PRINCIPAL_LIST}) THEN p_country = 'US'</CONDITION_US> <CONDITION_UK>WHEN current_user() IN (${PRINCIPAL_LIST}) THEN p_country = 'UK'</CONDITION_UK> END) END"
}

Example: match groups and roles¶

current_user() returns the user, never a group. To let a condition match groups and roles as well as users, check membership in the body:

1
2
3
4
5

CASE WHEN NOT exists(array(${ALL_PRINCIPAL_LIST}), p -> p = current_user() OR is_account_group_member(p)) THEN TRUE
ELSE (CASE
  <CONDITION_US>WHEN exists(array(${PRINCIPAL_LIST}), p -> p = current_user() OR is_account_group_member(p)) THEN p_country = 'US'</CONDITION_US>
  <CONDITION_UK>WHEN exists(array(${PRINCIPAL_LIST}), p -> p = current_user() OR is_account_group_member(p)) THEN p_country = 'UK'</CONDITION_UK>
END) END

Service principals can be policy item principals: they run as themselves, so current_user() returns their application ID.

Reference a template from a row-filter policy¶

In a row-filter policy on the table, set each policy item's row-filter expression (rowFilterInfo.filterExpr) to a JSON reference instead of SQL:

1

{"type":"template","template":"country_filter","condition_name":"CONDITION_US","columns":["country"]}

Example row-filter policy with two items on sales.orders:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

"rowFilterPolicyItems": [
  {
    "accesses": [ { "type": "Select", "isAllowed": true } ],
    "users": [ "alice@example.com" ],
    "rowFilterInfo": { "filterExpr": "{\"type\":\"template\",\"template\":\"country_filter\",\"condition_name\":\"CONDITION_US\",\"columns\":[\"country\"]}" }
  },
  {
    "accesses": [ { "type": "Select", "isAllowed": true } ],
    "users": [ "bob@example.com" ],
    "rowFilterInfo": { "filterExpr": "{\"type\":\"template\",\"template\":\"country_filter\",\"condition_name\":\"CONDITION_UK\",\"columns\":[\"country\"]}" }
  }
]

Rules for a table's row-filter policy:

• All items reference the same template, and all items use the same columns.
• Template items cannot be mixed with SQL row-filter items on the same table.
• A principal can appear in only one condition. Several items can reference the same condition; their principals are merged.
• Another table can use the same template with its own column names, for example "columns":["region_code"], as long as the column types match the parameters.

What PolicySync creates in Unity Catalog¶

For the example above, PolicySync runs two statements on cat.sales.orders:

1
2
3
4
5
6
7

CREATE OR REPLACE FUNCTION `cat`.`sales`.`orders_row_filter` (`p_country` STRING)
COMMENT 'Managed by Privacera PolicySync (privacera-policysync:template-row-filter hash=<sha256>). Do not edit or drop.'
RETURN SELECT CASE WHEN NOT current_user() IN ('alice@example.com', 'bob@example.com') THEN TRUE
  ELSE (CASE WHEN current_user() IN ('alice@example.com') THEN p_country = 'US'
             WHEN current_user() IN ('bob@example.com') THEN p_country = 'UK' END) END;

ALTER TABLE `cat`.`sales`.`orders` SET ROW FILTER `cat`.`sales`.`orders_row_filter` ON (`country`);

• Name and location. The function is <table>_row_filter in the table's own schema. For foreign catalogs it is created in the Privacera-managed catalog that holds the connector's functions for that catalog.
• Ownership marker. The COMMENT marks the function as managed by PolicySync and records a hash of what was applied. PolicySync never replaces or drops a function of that name without the marker; see TEMPLATE_FUNCTION_NAME_CONFLICT.
• Principals. Principal names are the names PolicySync uses for them in Unity Catalog (for users, the name current_user() returns), sorted, so the same input always produces the same function.
• No churn. When the rendered function is unchanged, PolicySync runs no DDL. A body edit inside one condition changes only the tables whose policies use that condition.
• Attach before grant. The row filter is attached before PolicySync grants access to the table, so the table's grants never apply without its filter.

Lifecycle¶

Audits of a template update¶

A template can be used by thousands of tables. Two PolicySync audit events show what a template change did: each table's ACCESS event names the template and its version, and one TEMPLATE_UPDATE_RESPONSE event per changed template summarizes all of its tables.

Template version on each table's ACCESS event¶

These ACCESS events of a template row filter carry the template in extraInfo.udf_template:

• Creating or attaching the filter, COMPLETED or FAILED.
• Dropping the filter when the table no longer uses the template: its policy was removed, or the table switched to another row-filter mode.

A drop of the old function that PolicySync puts off to a later apply, because it could not yet confirm the function is its own or the first drop failed, does not name the template.

• version and updateTime are the template's own values in Ranger, not the version of the whole UDF catalog. A value Ranger does not provide is left out; a template that does not exist has only name.
• A FAILED event names the template only when the table's policy references exactly one. A policy that references two templates (TEMPLATE_MIXED_ITEMS) gives a FAILED event without udf_template.
• The template, binding, and applyMode fields are unchanged.

1
2
3
4
5
6

"extraInfo": {
  "template": "country_filter",
  "binding": "orders_row_filter(p_country) ON (country)",
  "applyMode": "function-only",
  "udf_template": { "name": "country_filter", "version": 42, "updateTime": "2026-10-19 09:00:00" }
}

A table whose rendered function did not change runs no DDL, so it gets no new ACCESS event. Its last ACCESS event still shows the version last applied to it, and the template update counts it as unchanged.

TEMPLATE_UPDATE_RESPONSE summary¶

When a template is edited, PolicySync re-applies every table that uses it in one run (changelog type TEMPLATE_UDF_REAPPLY). At the end of the run it sends one TEMPLATE_UPDATE_RESPONSE event per changed template, which shows whether the change reached every table.

• It is sent only for this run: not for a policy change, and not for a template that no table uses.
• Like POLICY_RESPONSE, it is sent only when the change processor progress report is on, which it is by default:

Set it in the connector instance's vars.connector.databricks.unity.catalog.yml and run the Privacera Manager update, as in Enable template-based row filters. Its default follows the Privacera Manager-wide CONNECTOR_CHANGEPROCESSOR_PROGRESS_REPORT_TRACKER_ENABLE.

The event's fields:

The event has no zoneName: templates are not in a security zone.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

{
  "eventType": "TEMPLATE_UPDATE_RESPONSE",
  "status": "GRANT_PARTIALLY_APPLIED",
  "msg": "Template country_filter (version 42): 1180 of 1200 tables re-created, 18 unchanged, 2 failed",
  "changeLogType": "TEMPLATE_UDF_REAPPLY",
  "extraInfo": {
    "udf_template": { "name": "country_filter", "version": 42, "updateTime": "2026-10-19 09:00:00" },
    "tableCount": { "attached": 1200, "recreated": 1180, "unchanged": 18, "failed": 2 },
    "response": [
      { "code": 4002, "msg": "Databricks rejected the row-filter function or SET ROW FILTER statement", "origin": "PRIVACERA", "serviceCode": "PERMISSION_DENIED", "count": 2 }
    ]
  }
}

response[] groups the failed tables like any other event: by code, origin, and serviceCode, at most ten entries, highest count first.

• A transient Databricks failure is 5001, which is worth retrying.
• A table whose re-apply stopped before its row filter ran, or whose filter applied but could not be recorded, has no code of its own and is also counted as 5001.

Find the failed tables¶

The summary does not list the tables. To find them, search the ACCESS events with:

• the same connector and changeLogId as the summary,
• status FAILED,
• the summary's template in extraInfo.udf_template.name. The same run also applies other templates changed with it, and retries tables pending from an earlier failure, so match the name.

Each event has the table, and its reason code in extraInfo.response[].

This search can find fewer tables than failed:

• A table counted under 5001 with no code of its own may have no FAILED ACCESS event.
• A table whose policy references more than one template fails with 4005 TEMPLATE_MIXED_ITEMS, and its event has no udf_template. Find these with a second search: same connector and changeLogId, status FAILED, extraInfo.response[].code 4005, and no extraInfo.udf_template. Each one is counted in the summary of every changed template the table uses.

Do not search for every FAILED event without udf_template. The run re-applies each table's whole permissions, so other grants that fail in it, such as a withheld grant now failing with 4503, are FAILED ACCESS events in the same changelog that no summary counts.

When a failed table is applied again¶

• On the next edit of any template. Each run also retries the tables still pending from an earlier failure, without counting them in its summary.
• When the table's row-filter policy changes. To retry a table now, save its row-filter policy again.
• On the periodic permission sync, only with the JDBC permission load (CONNECTOR_DATABRICKS_UNITY_CATALOG_USE_DATABASE_CONNECTION_API: "false", the default), and only in two cases:
  • CREATE OR REPLACE FUNCTION completed and a later statement, such as SET ROW FILTER, failed, on a table that already had a template filter. The function's hash then differs from the one PolicySync recorded.
  • The sync found the filter changed outside PolicySync, and its repair failed. PolicySync then has no recorded hash for the table, so every later sync retries it until an apply succeeds.

The periodic sync does not retry any other failure, because Unity Catalog still matches what PolicySync recorded. That includes a failure found before any DDL ran, such as a failed check of the function's ownership (4002 with no serviceCode), and Databricks rejecting CREATE OR REPLACE FUNCTION itself. With the API permission load, it retries no failed table.

What the summary does not cover¶

• Several edits of the same template before the run starts: one run applies the latest version, and the summary reports that version.
• An edit that arrives while a long run is in progress: some tables are rendered from the earlier version and some from the later one. The summary reports the highest version, and the next run applies the latest version to every table.
• A run that fails gets no summary. Normally, so does a run interrupted by a connector restart: it is queued again, but starts before the connector has loaded its tables and finds nothing to re-apply.
• In both cases the tables not yet re-applied keep their previous filter until the next edit of any template, which re-applies them without counting them in its summary. An edit whose tables PolicySync had not looked up yet is applied, and summarized, in that later run.
• A table that stops using the template during the run is not counted.

Errors and fail-closed behavior¶

Errors when saving in Ranger¶

Ranger rejects an invalid template or policy with HTTP 400. The error code is in messageList[0].name and the details in msgDesc.

Errors from the connector¶

Errors that depend on the table are found when PolicySync applies the policy. In every case PolicySync fails closed: the previous row filter stays in place, and PolicySync withholds the table's own grants (for access policies that cover a whole schema, see Limitations).

• Most errors are found before any DDL runs. UNDECLARED_COLUMN and DDL_FAILED come from Databricks rejecting the DDL.
• After you fix the cause, the table is applied again on its next policy change or the next edit of its template. To apply it now, save its row-filter policy again. The periodic permission sync retries only some failures; see When a failed table is applied again.
• For TEMPLATE_COLUMNS_NOT_LOADED, the column metadata arrives with the next resource sync.

The reason code is in the PolicySync audits, in extraInfo.response[] of the failed ACCESS event and of the policy's POLICY_RESPONSE event, with details in the event's message. See Codes in the PolicySync audits.

Codes in the PolicySync audits¶

A failure found when PolicySync applies the policy is an entry in extraInfo.response[] of the failed ACCESS event and of the policy's POLICY_RESPONSE event: a code from 4001 to 4014 (VALIDATION, not worth retrying), with origin PRIVACERA.

• DDL_FAILED and UNDECLARED_COLUMN come from Databricks rejecting a statement, and carry the Databricks error condition as serviceCode. A transient Databricks failure is 5001 SYSTEM_ERROR instead, which is worth retrying.
• A save that Ranger rejects (Errors when saving in Ranger) is answered by the Ranger API and is not in the audits.

For the other fields of an entry, see Response codes in Policy Tracing.

Ranger UDF API¶

Replace <ranger-host> with your Ranger admin host and use valid credentials. serviceName must match your Databricks Unity Catalog service in Ranger (commonly privacera_databricks_unity_catalog).

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

curl -X PUT "https://<ranger-host>/service/udf/service/privacera_databricks_unity_catalog/name/country_filter" \
  -H "Content-Type: application/json" \
  -u "<ranger-user>:<ranger-password>" \
  -d '{
    "serviceName": "privacera_databricks_unity_catalog",
    "name": "country_filter",
    "udfType": 3,
    "description": "Row filter by country",
    "parameters": [ { "name": "p_country", "type": "STRING" } ],
    "conditionNames": [ "CONDITION_US", "CONDITION_UK" ],
    "udfText": "CASE WHEN NOT current_user() IN (${ALL_PRINCIPAL_LIST}) THEN TRUE ELSE (CASE <CONDITION_US>WHEN current_user() IN (${PRINCIPAL_LIST}) THEN p_country = '\''US'\''</CONDITION_US> <CONDITION_UK>WHEN current_user() IN (${PRINCIPAL_LIST}) THEN p_country = '\''UK'\''</CONDITION_UK> END) END"
  }'

Limitations¶

• Principals only. Conditions match the caller with current_user() (and group membership when the body checks it). Attribute and tag macros are not supported.
• No public group. An item that uses the public group fails with TEMPLATE_RENDER_FAILED.
• One template per table. All template items of a table's row-filter policy reference the same template.
• Fail-closed applies to the table's own grants. If a table's template filter fails, PolicySync withholds the grants on that table. An access policy that covers a whole schema (table: *) is granted as SELECT on the schema, and that schema-level grant still reaches the table without a filter. For tables protected by a row filter, list the tables explicitly in the access policy.
• Reusable UDF to template. A table cannot switch directly from a reusable master UDF filter to a template filter. Remove the master UDF policy from the table and sync, then add the template policy and sync again; restrict access to the table between the two syncs.
• Changes made outside PolicySync are repaired at the next periodic permission sync, not immediately: PolicySync checks the attached filter and the function's marker during that sync, with one query per schema that contains tables with a template filter (no extra queries on other applies). Until then the table is filtered by whatever is in Unity Catalog; if the filter was detached, the table has no row filter. To detect a body edit, PolicySync records the function's definition at the first permission sync after it creates or updates the function, and compares later definitions with that record. An edit made before that first permission sync becomes part of the record and is not detected. Restrict who can modify functions and row filters in the table's schema.
• JDBC permission load only. Repair of changes made outside PolicySync is supported only with the JDBC permission load (CONNECTOR_DATABRICKS_UNITY_CATALOG_USE_DATABASE_CONNECTION_API: "false", the default), which is recommended for template row filters. With the API permission load ("true"), template filters are still applied on every policy or template change, but changes made outside PolicySync are not repaired until the next such change.