Unity Catalog Governance Patterns

Four columns, eight references. Read the second and third together: the scoping column decides which rows a reader is entitled to, and the masked columns decide what is left visible on the rows they do get. Each row header jumps to that reference’s own section.

Shorthand on this page: BICC is Business Intelligence Cloud Connector, Oracle Fusion's extract framework; PVO is public view object, the shape a BICC extract is defined against.

Every column of this table is a decision the ERP already made for you, and the lakehouse has to re-enforce.

Whatever the source system was enforcing stops at the extract boundary. SAP authorization objects don’t travel with a replicated table. Oracle EBS multi-org access control is a runtime filter applied by the application, and this library’s own EBS reference already states the consequence plainly: it never applies to a lakehouse extract. NetSuite’s role-based access decides what a login sees in NetSuite, not what a landed table holds. What you have is every client, every company, and every column, sitting in one place, with exactly the grants you wrote.

The goal, stated as an opinion rather than a principle: least privilege that analysts don’t feel. Humans read gold. Pipelines write bronze. The handful of genuinely sensitive columns are masked by exception, in place — never copied into a parallel “secure” schema that has to be kept in sync forever and silently won’t be.

Everything on this page is plain SQL against Unity Catalog. No framework, no governance product, no orchestration layer. Five patterns, five SQL blocks, and a per-reference list of what to scope and what to mask.

This page has a sibling. The ERP data quality checklist is the set of checks that make the numbers right; this one is the set of controls that make them safe to open up. They lean on the same column in every ERP, for different reasons.

Environments are catalogs

supply_chain_prod and supply_chain_dev — never a name suffix inside one catalog. Two things follow. Promoting a pipeline then rewrites a catalog name and nothing else, so no schema or table name ever has to change; and a production grant cannot leak into development, because they are different securables under different parents. Where storage has to be isolated too, a catalog can carry its own managed location.

Layers crossed with sources are schemas

bronze_sap, bronze_jde, bronze_netsuite — the shape this site’s own SAP boilerplate already assumes when it reads bronze_sap.mseg. Silver stays per-source, because silver is cleaned, conformed, still source-aligned entities. Gold is one schema, because the star schemas are the cross-source product and a conformed dimension has no source system to file it under.

The reason this grain is right is a grants argument

A privilege granted on a schema inherits to its current and future tables. So one GRANT SELECT on a schema covers a source’s whole layer forever, including the table the pipeline creates next quarter — and the alternative, per-table grants, is a maintenance burden that quietly rots into over-granting.

One practical note: every table page in this library parameterizes catalog and schema in its copy-SQL, so any convention works downstream. Pick one and hold it — the cost is in changing it, not in choosing it.

-- Pattern 1: environments are catalogs, layers-per-source are schemas.
-- Nothing here is clever. The value is that it never changes again: a schema
-- grant inherits to every table the pipeline creates later.
CREATE CATALOG IF NOT EXISTS supply_chain_prod
  COMMENT 'Production supply chain lakehouse. Dev is its own catalog.';

-- Bronze: one schema per source system, shaped the way that source publishes.
CREATE SCHEMA IF NOT EXISTS supply_chain_prod.bronze_sap
  COMMENT 'Raw SAP tables as landed — MANDT intact, DATS strings uncast.';
CREATE SCHEMA IF NOT EXISTS supply_chain_prod.bronze_jde
  COMMENT 'Raw JD Edwards tables as landed — Julian dates and implied decimals uncast.';
CREATE SCHEMA IF NOT EXISTS supply_chain_prod.bronze_netsuite
  COMMENT 'Raw NetSuite2.com tables as landed via SuiteAnalytics Connect.';

-- Silver stays per-source: cleaned, conformed, still source-aligned entities.
CREATE SCHEMA IF NOT EXISTS supply_chain_prod.silver_sap
  COMMENT 'Cleaned SAP entities. Conversions happen here, once.';
CREATE SCHEMA IF NOT EXISTS supply_chain_prod.silver_jde
  COMMENT 'Cleaned JD Edwards entities.';
CREATE SCHEMA IF NOT EXISTS supply_chain_prod.silver_netsuite
  COMMENT 'Cleaned NetSuite entities.';

-- Gold is ONE schema, not one per source: the star schemas are the cross-source
-- product, and a conformed dimension has no source system to file it under.
CREATE SCHEMA IF NOT EXISTS supply_chain_prod.gold
  COMMENT 'Conformed dimensions and facts. The layer humans read.';

-- Dev is a separate catalog, never a suffix inside this one — promoting a
-- pipeline then rewrites a catalog name and nothing else, and a prod grant
-- cannot leak into dev. Add MANAGED LOCATION on the catalog when the
-- environments have to sit on separate storage.

Three principals

A pipeline service principal per source, which writes bronze and silver — USE SCHEMA, SELECT, MODIFY, CREATE TABLE. An engineers group, which reads silver; bronze access is a time-boxed exception granted for an investigation and revoked after it, never a standing grant. An analysts group, which reads gold and nothing else.

Grant to groups, own with groups

Grants go to groups, never to individual users — that is the documented recommendation and also the only version that survives a reorganization. Ownership follows the same rule for a blunter reason: a securable owned by a person breaks the day that person leaves. And day-to-day grant administration can be delegated with MANAGE, without transferring ownership at all, which is the right answer for a steward who approves access requests but shouldn’t be able to drop the schema.

Two traps worth naming

First, SELECT without USE CATALOG and USE SCHEMA reaches nothing — traversal privileges are separate, and this is the reason most “I granted it and they still can’t see it” tickets exist. Second, there is no DENY. Absence of a grant is the deny, and an inherited catalog- or schema-level grant cannot be overridden on one table; it can only be narrowed at the parent. So never grant broad intending to claw back — the claw-back doesn’t exist.

Bronze is closed

Not masked, closed. It is the simpler alternative to maintaining a second set of policies over raw, unconverted, source-shaped tables, and it removes an entire class of question from every per-ERP section below.

-- Pattern 2: three principals, and the write path is not one of the human ones.
-- Traversal first, or nothing below it is reachable: SELECT alone reaches
-- exactly nothing without USE CATALOG and USE SCHEMA.
GRANT USE CATALOG ON CATALOG supply_chain_prod TO `svc_pipeline_sap`;
GRANT USE SCHEMA, SELECT, MODIFY, CREATE TABLE
  ON SCHEMA supply_chain_prod.bronze_sap TO `svc_pipeline_sap`;
GRANT USE SCHEMA, SELECT, MODIFY, CREATE TABLE
  ON SCHEMA supply_chain_prod.silver_sap TO `svc_pipeline_sap`;

-- Engineers read silver. Bronze access is a time-boxed exception granted for an
-- investigation and revoked after it, never a standing grant.
GRANT USE CATALOG ON CATALOG supply_chain_prod TO `data_engineers`;
GRANT USE SCHEMA, SELECT ON SCHEMA supply_chain_prod.silver_sap TO `data_engineers`;

-- Analysts read gold, and only gold. One grant covers every table the star
-- schema grows later, because a schema privilege inherits to future tables.
GRANT USE CATALOG ON CATALOG supply_chain_prod TO `analysts`;
GRANT USE SCHEMA, SELECT ON SCHEMA supply_chain_prod.gold TO `analysts`;

-- Own with a group, never a person: ownership held by an individual breaks the
-- day that individual leaves.
ALTER SCHEMA supply_chain_prod.gold OWNER TO `platform_team`;

-- Delegate day-to-day grant administration without handing over ownership.
GRANT MANAGE ON SCHEMA supply_chain_prod.gold TO `gold_stewards`;

-- There is no DENY. Absence of a grant IS the deny, and an inherited catalog-
-- or schema-level grant cannot be overridden on one table — it can only be
-- narrowed at the parent. So never grant broad intending to claw back.

The two mechanisms

A row filter is a BOOLEAN user-defined function attached to a table with ALTER TABLE … SET ROW FILTER … ON (col): rows for which it returns false are not there, as far as the reader is concerned. A column mask is a UDF attached with ALTER TABLE … ALTER COLUMN … SET MASK, transforming the value on the way out. Both are generally available.

The ERP twist

You mostly don’t have to design either binding, but you do have to pick the right column. Bind the filter to the ERP’s org column — the one that says which part of the business a row belongs to. Often that is the same column the data quality checklist already tells you to pin in every join and group-by: DATAAREAID, CONO, ORG_ID, LGNUM. Sometimes it isn’t. That list is a correctness list, not an entitlement list, and two references come apart on the difference — SAP, where the client key keeps two tenants from summing together but says nothing about who may read a company’s data, and NetSuite, where subsidiary scopes correctly and still leaves the finance documents on the spine visible. Each section below names the column that actually entitles. And the columns worth a mask are already documented, table by table, in this library’s own references.

Four facts to build on

Filters and masks govern every reader on every path — notebook, SQL editor, dashboard, Genie — unlike a dynamic view, which only governs the readers who come through the view. The policy function runs with the table owner’s authority, so a reader never needs access to whatever the function itself reads. Identity resolves through is_account_group_member(), which evaluates against account-level groups rather than workspace-local ones. And grants come first: a mask narrows what SELECT returns, it never grants — group membership without SELECT gets a reader nothing.

One limitation to check

Tables carrying table-level filters or masks can’t be shared out through Delta Sharing by open-sharing providers. Behind the dated banner at the top of this page: check the current limitations list before attaching policies to a table you also share externally.

Where to attach them

Silver and gold. Not bronze — bronze is closed instead, which is pipelines own the write path (pattern 2) and is why this section has nothing to say about raw tables.

-- Pattern 3a: a row filter is a BOOLEAN UDF bound to the table's org column.
-- Bind it to the ERP's org column — the one that says which part of the
-- business a row belongs to. Dynamics 365 F&O below; substitute CONO, ORG_ID,
-- or LGNUM, and read the SAP and NetSuite sections before assuming the column
-- you pinned for correctness is the one that entitles.
CREATE OR REPLACE FUNCTION supply_chain_prod.gold.company_row_filter(dataareaid STRING)
  RETURN is_account_group_member('all_companies')
      OR is_account_group_member(concat('company_', lower(dataareaid)));

ALTER TABLE supply_chain_prod.gold.fct_inventory_txn
  SET ROW FILTER supply_chain_prod.gold.company_row_filter ON (dataareaid);

-- Pattern 3b: a column mask is a UDF over the column's own value. It returns
-- the value to the named audience and something harmless to everyone else.
CREATE OR REPLACE FUNCTION supply_chain_prod.gold.cost_mask(cost DECIMAL(19,4))
  RETURN CASE WHEN is_account_group_member('costing_readers') THEN cost ELSE NULL END;

ALTER TABLE supply_chain_prod.gold.fct_inventory_txn
  ALTER COLUMN unit_cost SET MASK supply_chain_prod.gold.cost_mask;

-- Three things to hold on to:
--   1. The policy function runs with the table owner's authority, so the
--      reader never needs access to whatever the function itself reads.
--   2. Filters and masks govern EVERY reader on every path — notebook, SQL
--      editor, dashboard, Genie. A view only governs whoever comes through it.
--   3. Grants come first. A mask narrows what SELECT returns; it never grants.
--      Membership of costing_readers gets a reader nothing without SELECT.

Two kinds of tag, and they are not the same thing. Freeform tags are unmanaged: anyone with the right privilege writes any key and any value, and the vocabulary drifts. Governed tags are defined at the account level — the allowed keys and values are set centrally. Assigning a tag is itself a privilege in both cases: APPLY TAG on the securable, which is what keeps the vocabulary from being widened by whoever happens to be editing a table.

The vocabulary. Exactly two keys to start: classification (internal, confidential, restricted) and source_system (sap, jde, and the rest). That is the practice’s opinion, and the reason is downstream: a policy engine pointed at a sprawling vocabulary automates the wrong thing at scale, and a tag nobody can define precisely is a tag nobody should be enforcing on. Tag the specific columns the eight sections below name. The tag says what a column is; the mask enforces who sees it; they travel together and neither substitutes for the other.

-- Pattern 4: tag the column, then let the mask enforce it. Two keys only —
-- what the column is, and where it came from.
ALTER TABLE supply_chain_prod.silver_sap.lfa1
  ALTER COLUMN stcd1
  SET TAGS ('classification' = 'restricted', 'source_system' = 'sap');

Then, and only then, tag-driven policies. Attribute-based access control lets a policy target every column carrying a tag, rather than attaching a mask table by table. Databricks documents tag-based row-filter and column-mask policies as generally available and ABAC GRANT policies as beta, as of August 2026. That is the whole claim — check the current status before you build on it.

The recommendation. Start with filters and masks attached directly. Adopt tag-driven policies once the vocabulary has been stable for a few quarters — automation over a vocabulary that is still moving is how a governance program produces its first outage.

Where did this number come from?

system.access.table_lineage and system.access.column_lineage answer it as a query: source and target full names, captured automatically as work runs, not maintained by anyone. That is the trust chain behind every KPI this library defines — and column-level lineage is specifically how you prove that a masked column never leaked into a downstream table under a different name. It answers the question inside the lakehouse only: lineage starts at bronze, and what the source kept — or overwrote — before the first table landed is the subject of the source history guide, which names the daily balances, effective-dated parameters and plan vintages the lakehouse has to snapshot for itself.

Who touched the vendor master?

system.access.audit — “touched” rather than “read”, because what the log records are metadata and credential events that approximate a read rather than the read itself. It is an event stream, so filter event_date first, pin the actions your own workspace actually emits, and expect the request_params map to carry different keys for different event types. This table is also the evidence that the masking was worth building, and the only place an access review can start.

Two caveats

The system schemas have to be enabled, and access to them is not granted by default — a metastore admin grants USE CATALOG, USE SCHEMA and SELECT on system.access to the platform group. And retention is bounded: roughly a year by default, and it varies, so verify it. If compliance needs a longer horizon, copy the audit rows you care about into a table of your own on a schedule. That is a scheduled MERGE, not a product.

-- Pattern 5a: "where did this number come from" is a query, not a diagram.
-- Lineage is captured automatically; you read it, you don't maintain it.
SELECT
  source_table_full_name,
  source_type,
  MAX(event_time) AS last_seen
FROM system.access.table_lineage
WHERE target_table_full_name = 'supply_chain_prod.gold.fct_inventory_txn'
  AND event_date >= CURRENT_DATE() - INTERVAL 90 DAYS
GROUP BY source_table_full_name, source_type
ORDER BY last_seen DESC;

-- Pattern 5b: "who touched the vendor master" is the other query. This is an
-- event stream, so filter event_date first — it is what the table is
-- partitioned on, and an unfiltered scan of an audit log is an expensive way to
-- learn nothing.
SELECT
  user_identity.email AS who,
  action_name,
  event_time,
  request_params.commandText
FROM system.access.audit
WHERE event_date >= CURRENT_DATE() - INTERVAL 30 DAYS
  AND request_params.full_name_arg = 'supply_chain_prod.silver_sap.lfa1'
  -- Which actions to pin depends on which events your workspace emits, so
  -- inspect your own audit rows before fixing this list. These two approximate
  -- a read: metadata resolution and a credential handed out to read the data.
  AND action_name IN ('getTable', 'generateTemporaryTableCredential')
ORDER BY event_time DESC;

-- Two caveats before either query returns anything:
--   1. request_params is a map whose keys vary by event type — inspect the
--      keys your own events carry rather than assuming full_name_arg is set.
--   2. The system schemas have to be enabled, and access is NOT granted by
--      default. A metastore admin grants USE CATALOG, USE SCHEMA and SELECT on
--      system.access to the platform group. Retention is bounded (about a year
--      by default, and it varies) — verify it, and copy audit rows into a table
--      of your own on a schedule if compliance needs a longer horizon.

The five patterns above lean on documented behaviour. Each item below was instead verified hands-on, because the documentation either does not say it or says it too quietly to build on.

Every limit in this section: one workspace, 2026-08-27, one unreplicated run. Retest anything load-bearing in yours.

There is no column-level GRANT

GRANT SELECT (col) ON TABLE fails at parse time. Restricting a column means a mask, a view, or not exposing the column — which is why row filters and column masks (pattern 3) exists.

A masked-column inventory reads three surfaces

A column mask created through a tag-driven policy showed up in neither information_schema.column_masks nor DESCRIBE EXTENDED. An audit that reads only those two surfaces can certify a column open that is in fact masked — read the policy definitions too.

The owner is not exempt, and views do not launder

Row filters and column masks apply to the table’s owner, and reading through a view does not strip them. There is no privileged path around an attached policy short of dropping it.

Ordinary tags cannot drive ABAC

A tag-driven policy binds to governed tags; pointing one at a freeform tag fails with an unknown-tag-policy-key error. The two-key vocabulary in pattern 4 has to be governed tags before any of it enforces.

A type mismatch in a filter fails open

With spark.sql.ansi.enabled = false, a row filter declared on INT against a STRING column returned every row instead of filtering. Type the filter’s parameter to the column, and test with a reader who should see nothing.

Binding overrides grants

A catalog not bound to a workspace is denied there even to a principal holding explicit SELECT. Binding is the outer gate; a grant inside an unbound catalog entitles nobody.

Audit latency is minutes, not seconds

Rows appeared in system.access.audit roughly ten minutes after the action. Fine for the compliance queries in lineage and audit as queries (pattern 5); not a real-time alarm.

A Genie space is attached to a SQL warehouse its readers need access to, and data access is evaluated as the end user’s own Unity Catalog identity — the people asking questions need SELECT on the underlying objects in their own right.

Which means a Genie room never shows a user a row or a column that the grants, filters, and masks of catalog and schema layout (pattern 1) through governed tags and tag-driven policies (pattern 4) wouldn’t have shown them anyway. The governed layer is the AI security model. There is no second thing to build, and no AI-specific permission surface to reason about.

The inverse is the part worth acting on. An over-broad grant that one analyst might never have noticed — a schema-level SELECT that quietly included the supplier bank accounts — becomes reachable by anyone who can type a question in plain English. AI raises the price of a sloppy grant. It does not raise the price of a good one.

What the semantic layer adds on top — the metric definitions, the synonyms, and the certified queries a Genie room or a Cortex Analyst model reads — is only as right as the definitions it stores, which is why they are settled before anything is built. Those definitions, switch by switch, are in the KPI Dictionary.