Row-level security restricts each regional manager to their own region's orders. The CFO now requires every manager's dashboard to show the company-wide total next to their region so they can see their share. The policies are correct and the total currently comes back as the manager's own region. Which design fits?
Show the full answer Hide the answer
The deciding property
What a manager may see in aggregate is not what they may see row by row, and a row filter cannot express the difference. A row policy answers one question - may this principal see this row - and the CFO's requirement is a different question: may this principal see a function of rows they cannot read individually. Once you see that, the design follows: the aggregate is a separate published object with its own grant, not a different way of querying the detail.
Why the aggregate product
Build a table of one row per region per day with order count and value, no row policy, and grant read to all managers. Across 30 regions that is a few thousand rows a year, refreshed with the detail, and the detail table stays in production with its policy untouched. The grain of the aggregate is the access decision, and it is reviewable: anyone can look at the definition and say whether region-by-day totals are acceptable to share internally. What it costs is one more model to own and the discipline to refuse requests to add dimensions to it.
The property to check before shipping is differencing. If the aggregate is published at region-by-day-by-product-by-store, a manager with their own detail can subtract their contribution and infer another region's single large customer. Decide the coarsest grain that answers the CFO's question - here, region by month is usually enough - and add a minimum-cohort rule for any cell built from few subjects.
Why the other options fail
- "A second role that drops the filter." This is standing access to every row, gated by a UI affordance. People forget which role they are in, extracts are taken under the wrong one, and the audit log cannot distinguish a total from a full-table read. It also fails the review question "who can see all orders", which now has to be answered with "everyone who can see totals".
- "Compute it in the BI tool under a service account." The commonest shortcut and the worst outcome: the policy is bypassed rather than satisfied, the BI tool becomes the enforcement point for a rule the platform was supposed to own, every scheduled export and drill-through inherits the service account's visibility, and the access log shows a robot instead of a person. A derived object refreshed under a privileged identity is how row-level security quietly stops applying.
- "Mask the region column." Masking hides the label and returns the rows. The manager still sees other regions' order values with the region blanked, which is more disclosure than before and less usable.
What would flip the decision
| If this changes | Choose | Because |
|---|---|---|
| Managers need the total broken down one more level | re-derive at that grain with a minimum-cohort rule | the differencing risk is in the grain not the policy |
| Managers need to see the leading region by name | a policy change and a sign-off | this is a disclosure decision not an engineering one |
| There are thousands of such tiles | a governed aggregate layer in the semantic layer | hand-built aggregate tables stop being reviewable |
When this is the wrong answer
If every manager is in fact allowed to see every order and the row policy exists only to keep dashboards focused, delete the policy. A filter for convenience that is implemented as a security control teaches everyone that controls are negotiable, and it generates exactly this class of exception request. Confirm what the restriction is for before designing around it.