Configure Row Security

Why row security matters

The most dangerous numbers in a business are the ones the wrong person sees. A regional sales manager seeing another region's revenue is a compliance incident. A customer-success account manager seeing a colleague's accounts is an HR incident. A "self-service" dashboard that shows every user every other user's data — even once — can cost a contract.

Traditional BI tools solve this by copying the model per audience. Finance gets one cube. Sales Europe gets another. Sales APAC gets a third. Every rename, every new measure, every join change has to be repeated across each copy, and drift between copies is the norm. One model a quarter gets out of step and a region sees something it should not.

Row security in Tessallite keeps a single model and filters it at query time per caller. One model, many audiences. The Query Router enforces the filter before any row crosses the network. The filter applies to every read path — JDBC for Tableau, XMLA for Excel, REST for a notebook, MCP for an agent — and cannot be bypassed by switching tool.

This page covers the two rule shapes, the tiny restricted DSL used to author role predicates, how rules combine, how the Router injects the filter at runtime, and the v1 limits every modeller must know before trusting a rule.

When to use row security

The typical cases:

• Regional sales model shared by regional managers. Each manager sees only their region.
• Customer-success model where every account-manager sees their assigned accounts. Needs per-user scoping, not per-role scoping.
• Self-service model where the caller should only see rows tagged with their own user identity. Typical for a HR/performance or personal-finance model.
• Multi-tenant model inside one workspace, where the "tenant" is a column on the fact table and the user is a member of exactly one tenant.

When not to use row security:

• When two audiences need two genuinely different models (different facts, different grain, different semantics). Copy the model.
• When the visibility rule is based on measure value, not dimension (for example, "hide rows whose revenue is below $X"). Row security filters on dimensions, not on computed values. Use a separate model, or a pre-filtered pocket.
• When the rule is time-varying and changes every query (for example, "only rows older than 30 days"). Row security rules are static predicates; time-relative behaviour lives in the calendar/time-intelligence layer, not the security layer.

Figure 1 — The Row Security panel. Rules are listed top-down; enabled rules are green-ringed; the protected-dimensions chip row gives the modeller a one-glance audit of what is and is not covered. Full description: row-security-panel-overview.txt.

The two rule shapes

A rule is one of two shapes. A model can mix shapes freely. Named-role grants that match the caller OR together (a person with two roles sees the union of those grants). Wildcard * rules AND with that union as a floor every caller must satisfy. Mapping-table rules also AND. Do not write two named-role rules expecting them to intersect for a multi-role user.

1. Role predicate

A rule authored in a small restricted DSL, scoped to one or more named roles. Use this when the visibility rule is the same for everyone in a role.

The DSL is deliberately tiny so every rule can be reasoned about without running it. Supported forms:

Anything outside this grammar is rejected at save time with a structured validation error. This is intentional — it means a rule cannot be a back door for arbitrary SQL, and the DSL is identical across PostgreSQL, BigQuery, and future connectors.

String literals are single-quoted. Escape a single quote by doubling it ('O''Brien').

Figure 2 — Authoring a role predicate. The dimension path field is a dropdown selector populated from the model's defined dimensions; select the dimension the rule filters on (for example region_code). The predicate expression field is a multi-line text area where you write the DSL expression; a helper hint below the field reminds you of the supported forms. The applies-to-roles field accepts a comma-separated list of role names. When the rule type is changed to user_mapping, the form adapts to show a mapping-table dropdown (populated from the model's tables) and column-name fields instead. Full description: row-security-rule-drawer.txt.

2. User mapping

A rule backed by a mapping table in the same model. Use this when the visibility rule is per user and the allowed values are stored as data.

Cross-model mapping tables are rejected at save time — a rule on model A cannot draw allowed values from a table that belongs to model B. This prevents accidental cross-tenant leaks.

A user with multiple rows in the mapping table — for example, an account manager mapped to three accounts — sees the union of those values. No additional configuration is needed.

The mapping table is just another model table, and it must belong to the same model as the rule. It can live in the same schema as the facts, or in a separate "security" schema, as long as that schema sits on the same data source as the facts it protects.

It cannot usefully live on a genuinely different data source. A model can be built by joining more than one source together — for example, a PostgreSQL source joined to a BigQuery source — and the rule form lets you pick a mapping table from any source in the model when you save the rule. But the moment a query actually needs that rule, Tessallite checks whether the mapping table and the fact table it filters share a source. If they do not, the query is refused with a clear error rather than silently joining rows across two different databases. Keep the mapping table on the same source as the fact table it protects.

How the Query Router applies rules at runtime

For every read query, the Query Router runs the same four-step procedure:

1. Resolve the principal. The caller's Principal is extracted from the JWT: user_identity from the sub / email claim, roles from the role claim. A single-string role is treated as a one-element frozen set so both role: "manager" and role: ["manager"] JWTs work.
2. Compile every matching enabled rule. Every role predicate whose applies_to_roles intersects the principal's roles. Every user mapping rule (these always match once per user). Disabled rules are skipped.
3. Constrain every table scan. If at least one rule compiled, the Query Router adds the combined predicate to the WHERE of every SELECT that reads a physical table — not as an outer wrapper around the whole query, but inside each scan. That includes each branch of a UNION / EXCEPT / INTERSECT, scalar subqueries, subquery-first FROM clauses, and the bodies of CTEs. Two consequences matter:
   • Because the filter lives in the same SELECT as each scan, it always applies before any LIMIT. So LIMIT 100 returns up to 100 allowed rows — never the first 100 physical rows that are then filtered down to far fewer (or none).
   • The combined predicate is AND(each wildcard, OR(named grants), each mapping). Two named-role grants for a caller who holds both roles union. A wildcard plus a named grant intersects. Mapping-table rules always intersect.
   A query whose shape the Router cannot prove it has fully constrained — one it cannot parse, or whose scopes it cannot resolve — is rejected with a 403 and the error code row_security_unsupported_shape, rather than being run unfiltered. Rewriting it as a plain SELECT (or a UNION of plain SELECTs) over the model resolves it. This fail-closed stance is the whole point: an unconstrainable query is refused, never leaked.
4. Use a fast path only when it can be proved safe. Tessallite keeps pre-computed copies of your data — aggregates and pocket tables — to answer common questions quickly. When a row-security rule fires, the Router does not simply skip them, and it does not simply trust them either. It asks one question per candidate: can this shortcut carry the exact same filter the source scan would carry?
   • An aggregate passes only when every column the security rule filters on is one of the columns that aggregate is grouped by. If the aggregate was pre-summarised in a way that threw the security column away, there is no honest way to filter it, so it is rejected.
   • A pocket table passes only when it is a straight copy of the rows (a SELECT *-style copy, optionally with its own WHERE filter — no joins, no grouping, no picked column list, no row limit) and Tessallite's own record of what that copy physically contains shows the security column is really there, spelled exactly the same way. That record is written every time the pocket is rebuilt, by reading the built table back from the database — it is never guessed from the model's design. A pocket copies only the columns its build made visible, so a hidden or joined-in security column can be genuinely missing from a perfectly valid pocket.
   When a candidate passes, the identical filter is added to each scan of that pre-computed table, so it is narrowed exactly as the source would have been — filtering only ever removes rows, never adds them. When a candidate cannot be proved, the query falls back to the source. The proof is re-checked one more time, against a fresh read, in the instant before the table is scanned, so a rebuild that lands mid-request is refused rather than served. Nothing unproved is ever read: this is a correctness guarantee first and a performance feature second.

When no rule matches, ordinary callers receive a deny-all predicate and see no rows. Only trusted interactive platform roles (tenant_admin, modeler, and system_admin) are exempt so administrators can inspect and repair governed models. A rule that explicitly names one of those roles still filters it. The KPI service role and bare embed sessions are not exempt.

An empty result is a valid answer, not an error

Row security is a filter condition on every governed query. It is not a gatekeeper that runs before the query and refuses it; it is part of the query that runs. So when a caller's rules match no rows, the query succeeds and returns nothing — exactly as WHERE region_code = 'FR' returns nothing in a dataset that holds no French rows.

That is deliberate, and it is why no error and no special signal is sent to the client. The filter is inside the query, so every consumer sees an ordinary empty result: Excel shows an empty PivotTable, Power BI shows an empty visual, a JDBC tool shows zero rows, and a total taken over no rows shows 0. A tool that asked a legitimate question got a legitimate answer for the audience it asked as. Tessallite does not distinguish "you are not allowed to see these rows" from "there are no such rows", because from the query's point of view they are the same condition, and answering differently would leak the existence of rows the caller may not see.

The practical consequence is that an empty result is not self-explaining. When a user reports that a report went empty, or that a figure they expect is showing 0, check these in order:

1. Which rules compiled for that person. Open Simulate as user with their email address and their exact JWT roles. A deny-all compiled string (0 = 1) means no rule matched, and an ordinary caller with no matching rule is denied every row.
2. Whether their role string is spelled as the rule expects. Roles are plain strings from the token. A typo in applies_to_roles, or a role the identity provider spells differently, matches nothing and reads as an empty report.
3. Whether their mapping row exists. A user_mapping rule with no row for that user allows nothing.
4. Whether the rule is narrower than intended. Wildcards and mapping rules intersect; only named grants union. Two rules that each look permissive can combine into a filter that matches no rows.
5. Whether the data really has rows for their scope. Run the Simulate probe query as a tenant administrator (SELECT <security_column>, COUNT(*) FROM <model> GROUP BY <security_column>). If the caller's allowed value is absent from that breakdown, the rule is right and the data is empty.

Steps 1 to 4 are policy problems and are fixed in the rule. Step 5 is a data problem and is not.

Preview compiled policy

The Row Security panel has a button labeled Simulate as user. It opens a dialog where you enter a candidate email address and a list of roles, then click Preview. Tessallite compiles every enabled rule against that hypothetical person and shows the resulting filter — one compiled string, the same predicate the Query Router would inject. There is no per-rule fire / not-fire tree: unmatched named grants simply do not appear in that string.

Optionally fill Probe query (the panel suggests SELECT <security_column>, COUNT(*) FROM <model> GROUP BY <security_column> LIMIT 50). When a tenant administrator supplies a probe, Simulate runs it through the real /execute path and shows the rows that principal would see. A modeller who is not a tenant administrator still gets the compiled predicate; the probe is refused rather than silently ignored. A blank probe is compiled-preview only.

Figure 3 — Preview compiled policy. Confirming a rule's effect before sending a JWT to a user's tool is the single highest-value habit in row-security authoring. Full description: row-security-simulate-drawer.txt.

The compiled string is a policy check. The probe table is the row proof. Review both after every rule change — named grants OR, so two rules that look like they tighten can instead widen a multi-role caller.

Everything built from the model inherits this filter

Row security does not just change what a plain query returns. It changes the model itself, for that caller. Think of it as a smaller model, built just for them.

Everything else comes from that smaller model too. This includes a named set, a saved Named Query, and a KPI. It also includes a summary table, a subtotal, a preview, and an export. It even includes an answer from the assistant. None of them can reach around it.

Set the rule once, here. It reaches every one of those automatically. There is no second list of "who can see what" to keep in step with this one.

What that means in practice:

• Named sets and Named Lists. Say a set ranks members by a measure you cannot see. You will not be offered that set at all. If a set or list cannot be built from your smaller model, it will not appear in the picker. It is not greyed out. It is simply not there.
• Named Queries. A Named Query keeps a stored copy of its answer. Tessallite uses that stored copy only when your row security can still be applied to it. Say the stored copy is missing the column your rule filters on. Then Tessallite runs the question live instead. It runs straight from the model as you see it, with the same filter. It never reads the stored copy unfiltered.
• KPIs, aggregates, and other summary tables. A summary table answers you only when your row security can still be applied to it. When it cannot, Tessallite computes the answer live instead. It will never hand you a number that was built for a wider audience.
• Subtotals and grand totals. A subtotal is a total taken over your filtered model. It is never a total taken over the unfiltered source with a filter added afterward.
• Previews and exports. A table preview, a drill-through, and a CSV or Excel export all read through the same filtered model. None of them reads the source table directly and applies the filter afterward.
• The assistant. The conversational agent is grounded on your model too. It is never told about a row, a measure, or a slice you could not query yourself. So it cannot describe, summarise, or chart something you are not allowed to see.

This is why one rule is enough. A derived answer can only reach what your own queries reach. It is always built on top of those queries.

Worked example — a two-region model

Context. A company has two regional sales audiences — EMEA and APAC. Each manager should see only their region's rows. A third group, "central analytics", should see every region.

Steps.

1. Open the model in Model Builder → Row Security.
2. Click New Rule. Fill in:

• Name: EMEA manager scope
• Rule type: role_predicate
• Dimension path: region.region_code
• Predicate expression: dimension_equals('region.region_code', 'EMEA')
• Applies to roles: add region_manager_emea as a chip.
• Enabled: on.

1. Save. Add a second rule mirroring the first for APAC (dimension_equals('region.region_code', 'APAC'), role region_manager_apac).
2. Add an explicit central-analytics rule covering every allowed region. An ordinary unmatched analytics_central role is denied every row.
3. Open Simulate as user, enter analyst@acme-demo.com with role region_manager_emea, and click Preview. The compiled string should constrain the region to EMEA. The APAC grant is simply absent from that string (the panel does not grey out unused rules).
4. Preview again with a user who has no roles. The compiled string should be deny-all (0 = 1). Then preview a tenant administrator: the compiled predicate is empty because privileged unmatched roles are exempt, not because the model is unprotected. Do not use admin@acme-demo.com as the everyday JDBC/Excel user when you want to see France-manager rows.

v1 limitations

Demo and simulation data

The acme-demo tenant seeds row-security rules. After a current reseed, regional-manager predicates use the compilable in(...) form (not the retired dimension_in). Live tenants that were never reseeded can still hold uncompilable text — Simulate and /execute then return a typed 422, not unfiltered rows.

admin@acme-demo.com is a tenant administrator. Privileged unmatched roles are exempt from coverage deny-all, so that JDBC/Excel login sees every region, including ones a France manager must not see. Use Simulate with role region_manager_emea (and a probe query) to inspect restricted rows. A dedicated non-privileged demo JDBC user is a seed follow-up.

To reseed the demo tenant (including its row-security rules), run bash scripts/reseed_acme_demo.sh from the tessallite/ directory.

Troubleshooting

Related

• Define dimensions
• Configure aggregates
• Configure pocket tables
• View diagnostics