Dynamic Views and Row-Level Security

Estimated read: 2 min

A familiar failure mode from the legacy warehouse: one region’s analysts need a sales table filtered to their territory, another region needs it filtered differently, finance needs it unfiltered, and rather than writing one access rule, someone creates sales_east_v, sales_west_v, sales_finance_v – and forty-seven more over the years. Each view is a hand-copied WHERE clause, and the moment the base table’s schema changes, only the views someone remembers to update stay correct. The rest silently drift, which is exactly how a well-meaning access-control pattern turns into an audit finding.

A dynamic view replaces the whole family of near-duplicate views with one view whose WHERE clause reads the querying user’s identity at run time, instead of being hard-coded per audience:

CREATE VIEW finance.sales.sales_secure AS
SELECT *
FROM finance.sales.sales_raw
WHERE region = CASE
  WHEN is_account_group_member('sales_finance') THEN region
  ELSE current_user_region()
END;

current_user(), is_account_group_member(), and is_member() are evaluated per query, so the same view returns different rows to different users without any per-audience copy to maintain – one object, one definition, no drift.

Unity Catalog’s native row filter does the same job one layer lower: instead of wrapping the base table in a view, the filter function binds directly to the table itself, so it’s enforced even for queries that never go through a view at all (a BI tool connecting straight to sales_raw, or a notebook doing spark.table(...)).

CREATE FUNCTION region_filter(region STRING)
RETURN is_account_group_member('sales_finance') OR region = current_user_region();

ALTER TABLE finance.sales.sales_raw SET ROW FILTER region_filter ON (region);

This is the direct successor to an Oracle Virtual Private Database (VPD) policy, as the security layer cake lecture noted – except a VPD policy required a PL/SQL package and a DBMS_RLS.ADD_POLICY call per table, while a Unity Catalog row filter is one ALTER TABLE statement referencing a reusable function.

Prefer a table-level row filter over a dynamic view whenever every consumer should see the same governed access, since the filter travels with the table itself; keep dynamic views for cases where a specific downstream tool genuinely needs a distinct, filtered shape of the data (fewer columns, renamed fields) rather than just a different row subset.

Row filters compose naturally with the ABAC tagging model from the previous lecture: instead of binding region_filter to one table with ALTER TABLE, an ABAC policy can bind the same filter function to every table tagged domain=sales across the metastore, which is where the next two lectures pick up. See the row and column filters documentation for the full syntax and current limitations.

---

| ← Previous: ABAC With Governed Tags: Tag at Schema, Not Table | Next: Tag-Based PII Masking → | |:—|—:|