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¶

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 are reported by Databricks when it rejects the DDL. After you fix the cause, the table is applied again on its next policy or template change, or at the latest at the next periodic permission sync (CONNECTOR_DATABRICKS_UNITY_CATALOG_PERMISSION_SYNC_INTERVAL, default 2 days). For TEMPLATE_COLUMNS_NOT_LOADED, the column metadata arrives with the next resource sync.

The reason code is in the PolicySync audits: in extraInfo.errorCode of the failed ACCESS row and of the policy's POLICY_RESPONSE row, with details in the message.

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.