Row and Column Security
An analyst sees EU rows only; a column comes back masked. Where that policy is evaluated decides whether it is a control or a convention — and a row filter silently changes what an aggregate means.
Who needs this, what one row is, and why the obvious build breaks
Every lesson starts from the consumer, because designing from the source outward is this domain's characteristic mistake.
When two analysts run the same query and get different numbers because of a row filter, which one is wrong?
An analyst who must see their region and no other, a support agent who needs a customer record without the payment details, and a partner-facing dashboard that must show one tenant's data from a shared table. All three want one dataset that behaves differently per reader, which is precisely what a single grant cannot express (Multi-Tenancy).
This is the lesson where grain becomes a security property. Under a row filter, one row of the result set is one row of the underlying table that this principal is permitted to see — so the population behind every aggregate is principal-dependent. count(*) no longer counts orders; it counts orders visible to whoever asked (Grain: What Does One Row Represent?).
Create a view per audience. v_orders_eu filters to EU rows, v_orders_masked replaces the email column with nulls, and grants are issued on views rather than on base tables. It works immediately, it is completely portable, and it is the right first answer for a handful of audiences.
View count grows with audiences multiplied by tables. Five regions and four sensitivity levels over thirty tables is six hundred views, all of which must be updated when a base table gains a column (Model Layering).
- View count grows with audiences multiplied by tables. Five regions and four sensitivity levels over thirty tables is six hundred views, all of which must be updated when a base table gains a column (Model Layering).
- Someone is granted on a base table for a legitimate reason — a debugging session, a pipeline, a migration — and every view-based control on that table is bypassed from then on, with no signal that it happened.
- The lake still holds the files. An engine reading Parquet directly sees every row and every column, whatever the warehouse's views say (Object Storage as Data Infrastructure).
- A filtered analyst runs a reconciliation against the source and it diverges every time, because their row count is a subset by design. They open a data-quality incident that is not one (Reconciliation).
- Two teams publish the same metric with different totals, both computed correctly from the same table, because their filters differ. Nobody can tell which number to put in the board deck (Two Dashboards, Two Numbers).
- The filter is implemented as a predicate the analyst is expected to add. Someone forgets, and the failure is silent and in the direction of more data (Fail Open vs Fail Closed).
- The filter predicate joins to an entitlement table, and that join fans out, so filtered users see duplicated rows and inflated sums (Joins: INNER, LEFT, RIGHT, FULL, CROSS, SELF).
What is actually happening
- Row-level security is a predicate the engine appends to every query against a protected table, derived from the current principal. Column-level security is an expression substituted for a column's value based on the principal and the column's classification. Both are rewrite rules applied by the engine, which is why the enforcement point is the engine and not the query.
- There are four places the rule can live, in decreasing order of how hard they are to bypass: native engine policy attached to the table or a tag, a view that is the only granted object, a policy layer in front of several engines, and convention — a predicate every analyst is told to write. The last is not enforcement; it is documentation with consequences (Where the Check Belongs).
- The entitlement itself is data. "Which regions may this principal see" lives in a table, and that table is a join in the hot path of every filtered query, so its grain matters enormously: one row per principal per region is safe, and one row per principal per region per product silently multiplies every filtered result (Grain: What Does One Row Represent?).
- Column masking and row filtering compose badly with aggregation in different ways. A masked column can still be grouped by if the mask is deterministic, which leaks the partition structure; a filtered row set changes every aggregate silently and produces no error at all.
- This is where governance becomes a correctness problem rather than only an exposure problem. Two principals querying the same table are querying different populations, and every downstream number inherits that (The Pipeline Succeeded. The Data Is Wrong.).
- Multi-tenant serving is the same mechanism with a much lower tolerance for error: a filter that leaks one row across a tenant boundary is a customer-visible breach rather than an internal over-share (Tenant Isolation).
The same query, two different populations
The point that gets missed about row-level security is that it does not deny anything. It quietly narrows. A denied query raises an error and someone asks a question; a filtered query returns a smaller, plausible, confidently-rendered result and nobody asks anything at all.
The consequence is that grain becomes principal-dependent. count(*) over fct_orders used to mean "orders". Under a policy it means "orders this principal may see", and every metric built on it inherits the qualifier without the name changing. The grain table below is the same physical table read by four principals, and the breaksIf column is where the incidents come from.
This is why row-level security belongs as much in a data-quality conversation as in a security one. It is the only governance mechanism that routinely produces wrong numbers, and it produces them without a single error, warning or failed check.
| Stage | One row is | Breaks if |
|---|---|---|
| Base table, unfiltered identity | One order, globally. The true population. | Anyone assumes their own filtered result matches it — including a reconciliation job that happens to run as a filtered principal. |
| EU analyst, region filter | One order whose billing region is in the EU. | They compute "total revenue" and present it as company revenue. The number is correct for a population nobody stated. |
| Support agent, customer filter + column mask | One order for a customer in an open ticket, with payment columns masked. | A masked column is grouped by, and a deterministic mask makes the groups line up with the real values. |
| Partner tenant, tenant filter | One order belonging to that tenant. | The tenant predicate is missing on one code path, and one row of another tenant appears — an internal over-share becomes a customer-visible breach (Tenant Isolation). |
| Any filtered principal, entitlement join fans out | One order per matching entitlement row — so, several copies of the same order. | It always breaks. Sums and counts inflate silently in proportion to the fan-out, and only for filtered users, which is why it survives so long (Duplicate Rows). |
| Pipeline identity, unfiltered by necessity | One order, globally — the pipeline must see everything to build the table. | The pipeline writes an export or a mart that filtered users can read, publishing the unfiltered population through the back door. |
Five of these six readers get a defensible answer to the same SQL. The failure is not that any of them is wrong — it is that the result carries no indication of which population it describes.
Where the rule lives decides whether it is a control
current_user() resolves under a BI tool's shared connection all differ per warehouse; some engines have no native policy at all and force the view form, which changes the guarantee rather than only the syntax.The SQL below shows the three implementations that people actually ship, in order of how hard they are to bypass. They are written against a generic dialect; every warehouse spells its policy syntax differently, and the structural difference between them survives the translation (Data Access Control).
Look at what each one depends on. The convention depends on the analyst remembering. The view depends on nobody holding a grant on the base table — a condition maintained by discipline over the lifetime of the platform. The policy depends only on itself, applies to tables created after it was written if it attaches to a tag, and survives someone querying the base table directly.
Note the entitlement join in the third example and the uniqueness test beside it. That join runs on every query against a protected table, so its grain is load-bearing: one duplicate row in the entitlement table multiplies results for every filtered user, and the symptom is inflated revenue rather than an error (Grain: What Does One Row Represent?).
1-- 1. By convention. Not a control: forgetting the predicate returns MORE2-- rows, silently, and nothing records that it happened.3select sum(amount) from fct_orders where region = 'EU'; -- please remember this4 5-- 2. By view. A control only while the base table is granted to nobody.6create view v_orders_eu as7 select order_id, customer_id, amount, region8 from fct_orders9 where region = 'EU';10grant select on v_orders_eu to role analyst_eu;11revoke select on fct_orders from role analyst_eu; -- the load-bearing line12 13-- 3. By engine policy, attached to a tag rather than to a table name, so14-- tables created tomorrow inherit it. Syntax is warehouse-specific;15-- the shape is not.16create row access policy region_scope as (region varchar)17 returns boolean ->18 exists (19 select 120 from governance.entitlements e21 where e.principal = current_user()22 and e.region = region23 );24-- applied to every table carrying the 'regional-data' tag25 26-- The entitlement table is now in the hot path of every filtered query,27-- so its grain is a correctness property. This test is not optional:28select principal, region, count(*) as n29from governance.entitlements30group by principal, region31having n > 1; -- any row here inflates every filtered aggregate on the platformThe third form is the only one whose guarantee does not depend on a grant that someone might add later. It is also the only one that covers a table created after the policy was written — which is what makes it survive schema growth.
Native row-access and column-masking policy support, and whether policies can bind to classification tags, differ between warehouses and change over time. Verify current documentation for the engine you are on before designing around either.
Filtering versus copying, and the number in the board deck
The alternative to filtering one table is materialising a copy per audience. It is a real option and it is chosen more often than anyone admits, usually implicitly, when filtering proves painful. Both approaches have a failure mode that shows up in a meeting rather than in a log.
Filtering keeps one source of truth and makes every aggregate principal-dependent, so two people can compute the same metric from the same table and disagree. Copying makes each audience's aggregate stable and creates n datasets that drift, refresh at different times and must each be reconciled (Data Marts).
The pattern that resolves most of it: filter for row-level access, and publish unfiltered aggregates as separate, deliberately coarse datasets for people who need totals. Finance needs the total, not the rows; a pre-aggregated regional summary serves that without granting anyone row access, and it gives the organisation one number to point at (The Metrics Layer).
Materialise `fct_orders_eu`, `fct_orders_us`, `fct_orders_apac` on separate schedules, each granted to its own group. Every audience gets stable aggregates and nobody sees a filtered result.
Keep one `fct_orders` with a row policy bound to a classification tag, and publish `rpt_orders_by_region` — pre-aggregated, unfiltered, no row access — for anyone who needs totals or reconciliation.
Copies drift: three tables refreshed by three schedules from one upstream will disagree at any given moment, and each must be separately reconciled, retained and deleted from. The filtered table has one lineage and one freshness, and the unfiltered aggregate answers the totals question without granting the rows — which is what people actually wanted when they asked for a copy.
How to build it
Most important first.
- Enforce in the engine where the engine supports it, and grant on nothing else. A native policy attached to a classification tag applies to new tables automatically, which is the property views do not have (ABAC and Policy-Based Authorization).
- If views are the mechanism, make them the *only* granted objects and revoke everything on base tables, including for engineers. A view-based control with a base-table grant somewhere is not a control (Least Privilege).
- Model the entitlement table at a grain of one row per principal per scope, test it for uniqueness, and treat a fan-out there as a production incident — because it inflates every filtered aggregate on the platform (Data Tests).
- Make filtering visible in the result. A query interface that shows "this result is filtered to EU" prevents the whole class of incidents where two people compare numbers that were never comparable.
- Publish unfiltered aggregates separately for the people who need totals. Most reconciliation and finance work needs the total and not the rows, and a pre-aggregated unfiltered table serves that without granting row access (Data Marts).
- Close the file path, or the policy is advisory. Row and column security is the strongest argument for humans not holding lake credentials (Data Access Control).
- Test policies as code: for a representative set of principals, assert the exact row count and the exact masked columns returned. Policies are logic and untested logic is wrong (Data Quality).
What this actually promises
Naming the guarantee you do not have is worth more than naming the one you do — everything downstream inherits the weakest promise in the chain.
- A native row policy guarantees that queries through that engine against that table return only permitted rows, including through views built on it. It guarantees nothing about the underlying files or about a different engine reading them.
- A view guarantees filtering only while it is the sole granted object. The guarantee is not a property of the view; it is a property of the grant configuration around it, and it is one
GRANTaway from being void. - Column masking guarantees that the masked expression is what the engine returns. It does not guarantee non-inference: a deterministic mask preserves grouping and equality, which can be enough to re-identify (Data Masking, Tokenisation & Encryption).
- Nothing guarantees that a filtered result is a correct answer to the analyst's question. The filter is applied to the data, not to the question, and the mismatch is entirely invisible in the result.
- Row filtering guarantees nothing about the totals a filtered user computes matching anyone else's. Divergence is the designed behaviour and it is routinely reported as a bug.
Can I trust it?
A green pipeline is evidence that code ran. These four fields are the evidence that the data is right.
- Policy tests: for each of a small set of representative principals, run a fixed query and assert the exact row count, the exact set of distinct values in the filtering column, and that masked columns return the masked form. Run them on every policy change and on a schedule.
- They miss paths that do not go through the tested engine — direct file reads, a federated connector, a replication job — and they miss inference: a masked column that is still groupable, or a filtered count that reveals the size of the population you cannot see (Federated Query).
- A separate uniqueness test on the entitlement table catches the fan-out failure, which is the one that silently inflates every filtered aggregate rather than exposing anything.
- The entitlement table has its own freshness, and it is on the critical path of correctness rather than of latency: a person who changed teams yesterday sees the wrong population until it updates.
- Policy changes take effect at the next query for engine-native policies, and at the next refresh for anything materialised. A dashboard with a cached extract keeps serving the old policy's result set (Caching Patterns).
- Adding a filter to an existing table changes historical query results immediately and retroactively, which will look to consumers like data disappearing overnight. Announce it as a data change, not as a permission change.
- A new column arrives and is unmasked, because masking was attached to columns by name. Policies attached to classification tags survive this; policies attached to names do not (Data Classification).
- A new table in a governed schema has no policy at all until someone adds one, unless policy attaches to tags. This is the single strongest argument for tag-based policy (Schema Evolution).
- Changing the filter predicate changes every historical result computed through it. Treat a policy change as a semantic change to every dependent metric and communicate it as one (Semantic Changes).
- A policy that was too permissive is fixed by tightening it, after which the access logs tell you what was read during the window. That is the only reason the window is answerable (Audit Logs for Privileged Actions).
- A policy that was too restrictive produced wrong numbers rather than errors, so recovery includes finding the reports built during the period and restating them. Filtered under-counting is a data incident with a governance cause (Data Incidents).
- An entitlement fan-out is repaired by fixing the grain and recomputing anything aggregated during the period. Because the inflation is proportional to the fan-out and only affects filtered users, it is unusually hard to spot and unusually easy to fix once found (Duplicate Rows).
What can go wrong
- The filter is applied by convention and someone forgets it. Silent, and in the direction of exposure.
- A base-table grant exists somewhere for an old reason, and every view-based policy on that table is void.
- The entitlement join fans out and every filtered user's totals are inflated, consistently, for months.
- Two teams compute the same metric under different filters and both are correct. The escalation is about the numbers and the cause is a policy (Two Dashboards, Two Numbers).
- A masked column is deterministic, so it can be grouped and joined, and a small group size re-identifies the individual anyway (Data Masking, Tokenisation & Encryption).
- A filtered analyst's reconciliation against the source diverges permanently, and the platform learns to ignore reconciliation alerts (Reconciliation).
- The policy is enforced in the warehouse and a downstream export job — running as the pipeline identity, which is unfiltered — writes the full dataset to a location the filtered users can read.
- "Row-level security is an access-control feature." It is also a correctness feature with no error path. Two principals get different answers to identical SQL and neither is warned (The Pipeline Succeeded. The Data Is Wrong.).
- "The view filters it, so we are safe." The view filters it for principals who can only reach the view. Check base-table grants and file access before believing it.
- "Masked means anonymous." A deterministic mask preserves equality and grouping, which is often enough to identify someone in a small group (Data Masking, Tokenisation & Encryption).
- "We can reconcile against the source." Not as a filtered principal. Reconciliation must run as an unfiltered identity or it will diverge by design, forever (Reconciliation).
- "Adding a filter is a permission change." It changes every number computed through it, retroactively. It is a data change with a permission trigger (Semantic Changes).
- Row and column policies are where classification becomes enforcement: they are the mechanism that consumes the tags the catalog holds, and a platform with classification and no policy has built the input to nothing (Data Classification).
- Because a filtered result is a different population, every filtered dataset needs its scope written into its documentation. "This table is filtered by entitlement" belongs in the contract, next to the grain (Data Contracts).
- Entitlement data is itself sensitive: knowing which principals may see which regions describes the organisation. Govern the entitlement table at least as strictly as the data it protects.
Operating it
- Row counts returned to distinct principal classes for the same table, over time. A sudden change in one class means a policy or an entitlement changed, whether or not anyone intended it.
- Entitlement-table uniqueness, monitored continuously as a data test, because its failure inflates results rather than raising errors (Data Tests).
- Queries against protected tables that did not go through the policy path — direct file access, federated connectors, replication — which is the metric that shows whether the enforcement point is actually the enforcement point.
- Policy-test results per release, treated as a build gate rather than as a monitoring signal (Contract Enforcement).
- At 10x audiences, per-audience views stop being maintainable and policy must be data-driven — one policy, one entitlement table, many principals.
- At 100x tables, policy must attach to tags rather than to objects, or new tables will be unprotected by default (The Data Catalog).
- High entitlement cardinality changes the shape: a filter over a handful of regions is a cheap predicate, and a filter over per-user entitlements is a join against a large table on every query (Cardinality: The Label That Took Down Monitoring).
- Multi-tenant serving at scale usually forces the filtering column into the physical layout, because a per-tenant predicate that does not prune is a full scan per tenant (Partitioning).
- A row filter is an additional predicate on every query against the table. Where the filtering column is also the partitioning or clustering column, it can reduce scanned bytes; where it is not, it adds a join to an entitlement table on every query (Partition Pruning).
- Column masking is a per-row expression on masked columns only, so it costs in proportion to how many masked columns a query projects — another reason
SELECT *is expensive in an analytical setting (Projection Pushdown). - View proliferation costs metadata and maintenance rather than compute, and the maintenance grows with schema change rate multiplied by view count.
- The largest cost is duplication driven by policy: when filtering is painful, teams build per-audience copies of tables, and each copy is a new dataset to govern, refresh and reconcile (Data Marts).
- Native engine policy is the strongest and the least portable. A platform that adopts it is choosing an engine for its governance model as much as for its query performance, and that is a legitimate reason to choose one.
- Views are portable, comprehensible and fragile, because their guarantee depends on the absence of a grant rather than on the presence of a policy.
- Filtering one shared table keeps a single source of truth and makes every aggregate principal-dependent. Per-audience copies keep aggregates stable and multiply the datasets you must govern and reconcile. There is no option that gives both.
- Making filtering visible in results reduces confusion and reveals the existence and shape of data a principal cannot see, which is occasionally itself a disclosure.
Where this applies
Almost nothing here is universal. These labels say what each claim is specific to, and where a different engine, format, warehouse or scale would differ.
- WAREHOUSE-SPECIFICNative row-access and masking policies exist in some warehouses and not others, and where they exist they differ in whether they attach to tags or to objects and in how they compose with views; a policy design built on one engine's model usually cannot be ported without becoming a view hierarchy.
- GENERALThe structural points — that enforcement must be in the engine rather than in the query, that a row filter changes the population behind every aggregate, and that the entitlement relation's grain determines whether filtered results are inflated — hold for any implementation.
- ENGINE-SPECIFICA federated or lake query engine reading the same files may not evaluate the warehouse's policies at all, so a table protected in the warehouse can be entirely unprotected through a second engine pointed at the same storage.
Where the depth lives
This domain teaches how data moves and how you know it arrived intact. It hands the rest off by name.
- — Distributed Systems owns why a policy change does not take effect everywhere at once when several engines and caches read the same data, and why the convergence window is a real exposure rather than a rounding error.