Multi tenant analytics isolation so one exhibitor never sees another set of rows

The bug was two characters long. A new endpoint had been added to serve a summary tile on the exhibitor portal, the developer copied the query above it, and in the copy the WHERE exhibitor_id = ? clause was on the line that got trimmed. The tile rendered. It showed a lead count. The lead count was the whole show.

Nobody noticed for nine days, and the only reason anybody noticed then was that an exhibitor emailed to ask why their lead number had gone up by a factor of forty. Multi tenant analytics isolation is the discipline that makes that mistake impossible to write rather than possible to catch.

Three shapes of isolation, and the one everybody picks

There are three arrangements in common use and the choice between them is mostly about blast radius.

Shared tables with a tenant column. Every exhibitor's rows live in the same tables, distinguished by an identifier, and every query carries a predicate restricting to one identifier. Cheapest to build, cheapest to run, and one missing clause exposes everything.

A store per tenant. Each exhibitor gets their own database or schema, and a query executed against the wrong store returns nothing rather than somebody else's rows. Safest by construction, and the operational cost climbs fast: a schema change is a migration run once per tenant, and a portfolio with several thousand exhibiting companies is several thousand migrations.

A hybrid. Most tenants share, and the ones where exposure would be worst get their own store. That usually means the largest accounts, which is a reasonable proxy for the ones whose lawyers would be most engaged.

Almost everyone picks shared tables, and the choice is defensible. An event business with three or four thousand exhibitors across a portfolio cannot maintain three or four thousand databases, and the shared design is what makes a single report definition possible in the first place, which is the delivery model described in embedded analytics for exhibitors.

Why does the common choice also fail most often?

Because the correctness of the whole system rests on a clause that a person has to remember to write, and the failure produces no error.

Amazon Web Services published a whitepaper on this in 2020, "SaaS Tenant Isolation Strategies: Isolating Resources in a Multi-Tenant Environment", and its framing of the stakes is worth quoting: crossing the tenant boundary "in any form would represent a significant and potentially un-recoverable event for a SaaS business". The paper puts isolation among the foundational concerns a provider addresses, separate from and prior to the question of authentication.

That separation is the point people miss. Authentication proves who the exhibitor is. Authorisation decides whether they may open the report. Isolation is a third thing: the guarantee that the query executed on their behalf physically cannot return rows belonging to somebody else. A system can get the first two right and still hand over the whole show.

The wider evidence says this category of defect is ordinary. The OWASP Top 10 for 2021 moved broken access control to first place, mapping 34 distinct weakness types into the single category and counting more than 318,000 occurrences and 19,013 published vulnerabilities across the dataset behind the list. The specific pattern it names, an attacker viewing or modifying another user's record by supplying its unique identifier, describes an exhibitor portal exactly.

Move the predicate somewhere it cannot be forgotten

The design response is to stop relying on discipline at the point of query authorship.

The strongest version pushes the predicate into the data store, so the database applies a policy tied to a session variable set at connection time and no statement executed on that connection can see other tenants' rows regardless of what it asks for. A developer who forgets the clause gets their own tenant's data. A developer who writes a malicious clause gets their own tenant's data.

The next best version is a data access layer that constructs every query, with no path from application code to raw SQL. The tenant identifier enters once, at the point the session is established, and the builder refuses to emit a statement without it. This is weaker than a database policy because the bypass exists, and it is far stronger than a code review convention because the bypass is visible in a diff.

The version I would argue against is the one most teams have: a shared helper function that adds the predicate, used by every query written by somebody who remembered to use it. That is a convention with good ergonomics and no enforcement, and the endpoint written at eleven at night before a show opens will not use it.

When a hybrid earns its cost

The hybrid arrangement gets dismissed as fence-sitting and it is often the right answer for an exhibitions group, because the tenant population is genuinely lopsided.

A portfolio where the top thirty accounts represent a large share of contracted value has thirty relationships whose loss would be material and several thousand whose loss would be regrettable. Giving those thirty their own store means thirty migrations to run on a schema change instead of several thousand, which is a Tuesday rather than a programme, and it caps the worst case for the accounts where the worst case is worst.

The trigger for moving a tenant is worth writing down in advance so the decision is not made under pressure. Contract value above a threshold, a contractual clause the account negotiated, a regulator in their sector, or a request from their own security team. Any of those, and they move. Without a written trigger the answer defaults to whoever asks most loudly, which correlates poorly with actual risk.

Auditing 200 queries and what the result actually proves

Sampling is the control teams reach for, and it is worth being honest about what it buys.

Take an edition's query log, 84,000 queries served to the exhibitor portal, and sample 200 at random. Check each for a tenant predicate. Suppose all 200 are clean.

Work out what that permits. If the true rate of unfiltered queries were 1.5 per cent, the chance of drawing 200 in a row with none of them bad is 0.985 to the power of 200, which is about 0.049, or roughly one in twenty. A clean sample of 200 is therefore entirely consistent with a real failure rate of 1.5 per cent, and 1.5 per cent of 84,000 queries is up to 1,260 exposures in one edition.

Pushing the ceiling down to a tenth of a per cent needs about 3,000 clean samples, because 0.999 to the power of 3,000 is also close to 0.05. Sampling scales badly against rare events, which is the general shape of this problem.

None of which makes the audit worthless. A sample of 200 will catch a systemic failure quickly and cheaply, and it will tell you whether a newly added subsystem is behaving. Treat it as a smoke detector rather than as evidence of safety, and spend the real effort on the structural control that makes the predicate unforgettable.

What about exports, caches and saved views?

The query path usually gets the attention. The paths around it are where the leaks that reach the press tend to originate.

A cache keyed without the tenant identifier will serve one exhibitor's aggregate to the next requester, and it will do so intermittently, which makes it hard to reproduce and easy to dismiss. A saved view or a scheduled export stores a query and re-runs it later under whatever identity the scheduler holds, which is frequently a service account with no tenant scope at all. A file dropped in shared object storage under a predictable name is readable by anybody who guesses the next number, which is the identifier-guessing pattern in its plainest form.

Enumerate every path by which a number reaches an exhibitor, and check each one separately: the interactive report, the export, the scheduled email, the API, the cache, and any file the system writes to disk. The interactive report is usually the one that was designed carefully. The scheduled email is usually the one written in an afternoon.

Where this stops

Isolation prevents one tenant reading another's rows. It says nothing about inference, and in exhibition data inference is a live risk.

An exhibitor who sees their own scan count alongside a hall total and a category total can often work out a competitor's position, particularly in a category with four exhibitors. No tenant predicate is violated. Every row returned belongs to them. The disclosure happens in the arithmetic, and the defence is a minimum group size before any aggregate is displayed rather than anything in the data access layer.

The second limit is that all of this assumes the identifier arriving with the request is trustworthy. If the tenant id travels from the browser and the backend believes it, every control described here is enforcing a value the attacker chose. That is the whole subject of embedded report authentication tokens and it sits upstream of everything in this post.

The first step is a grep rather than a project. Search your codebase for the table holding exhibitor-grain facts and list every query that touches it. Against each, mark whether the tenant predicate is applied by the query, by a shared helper, or by the database. Anything in the first category is a line somebody has to keep remembering, and the count of those lines is the honest measure of how much of your exhibitor reporting currently depends on nobody being tired. Proving the internal equivalent works uses the same matrix discipline applied one layer up.