A Permission Gate Inside Every Measure

The most V9-Simplified thing about V9 Simplified is where it puts the permission check. Not in a group-resolution layer, not in a cache-backed function, not in a separate filtering pass — inside the measure calculation itself. The CASE expression that computes an aggregate has a HasPermission call sitting right in it, so the value is either computed or withheld in the same breath. This post is that inlined gate: how it reads, why read-time filtering is the right call, and the access level the view figures out on the fly.

The gate, inline

Here is the pattern, straight from the simplified fact calculation. A measure is computed only if the viewer is cleared for it, in one expression:

MAX(CASE WHEN cf.MeasureName = 'OverallTotal'
         AND HasPermission(p_EmployeeId, 'ViewOverallMetrics')
         THEN cf.FactValue END) AS OverallTotal,

Read it as one thought: the OverallTotal column is this measure's value if the employee has ViewOverallMetrics, and otherwise nothing. The HasPermission check is a term in the CASE, right next to the value it guards. There is no separate authorization step that runs first and hands filtered data to a calculation step — the authorization is part of the calculation. When the check fails, the CASE falls through to its implicit ELSE NULL, and the column comes back NULL — redacted, not zero, preserving the aggregate-safe semantics V9 established.

The elegance is that this is just SQL. HasPermission(employee, name) reads the employee's direct permissions and returns a boolean; dropping it into a CASE is the most ordinary thing in the world. No group lookup, no cache protocol, no permission-aware function variant — the same measure calculation every version has had, with one boolean term added. The entire permission model, inlined into the arithmetic.

Row filtering, the other half

The inline gate handles column masking — which measures you see. GetFilteredSalesData handles row filtering — which sales you see — with the same direct-permission checks driving a WHERE:

-- conceptually, inside GetFilteredSalesData:
WHERE (HasPermission(p_EmployeeId, 'ViewAllSales'))
   OR (HasPermission(p_EmployeeId, 'ViewOnlineSales') AND s.SaleType = 'Online')
   OR (HasPermission(p_EmployeeId, 'ViewStoreSales')  AND s.SaleType = 'Store')

A viewer with ViewAllSales passes every row. A viewer with only ViewOnlineSales passes online rows and nothing else. Same HasPermission function, same direct grants, applied to row visibility instead of column visibility. Between the inline CASE gates and this WHERE, the whole of permission-aware analytics is expressed with one helper function used in two ordinary SQL positions. That is the simplification's real achievement: a capability that took V9 groups, mappings, and a cache is here just HasPermission in a CASE and a WHERE.

An access level, derived on the fly

Because permissions are so directly readable, the views can describe a viewer's access in human terms without any extra state. SalesDataWithPermissionsV9 computes an AccessLevel label on the fly from what the employee holds:

CASE
    WHEN HasPermission(EmployeeId, 'ViewAllSales')    THEN 'Full'
    WHEN HasPermission(EmployeeId, 'ViewOnlineSales') THEN 'Online Only'
    WHEN HasPermission(EmployeeId, 'ViewStoreSales')  THEN 'Store Only'
    ELSE 'Limited'
END AS AccessLevel

The view also derives a QualityLevel (High/Medium/Low) from the DataQualityScore the same way. These labels are not stored anywhere — they are computed from the underlying permissions and scores each time the view is read. That is only possible because the permissions are directly on the employee; deriving an access level under V9's group model would mean resolving groups first. Direct permissions make the model self-describing: the system can tell you, in words, what a viewer's access amounts to, because that access is a plain fact about the employee rather than the output of a group computation.

Why read-time, and what it costs

Putting the gate inside the calculation means filtering happens at read time, every time — the same choice V6 made and the opposite of what V9's caching optimised away. I want to defend it, because read-time filtering has a bad reputation for being slow and here it is the right call.

The upside is correctness that needs no maintenance. Because the check runs on every read, a permission change takes effect immediately — revoke someone's ViewCommissionData and their very next query returns NULL for commission, with no cache to invalidate, no stale window, no “did we remember to flush.” The revocation-staleness hazard that V9's permission cache introduced simply does not exist here, because nothing is cached between the grant and the read.

The cost is that every read evaluates HasPermission, which touches the permission table. For the handful-of-viewers common case V9 Simplified targets, that cost is trivial — a small indexed lookup against a tiny table, dwarfed by the actual measure computation. Read-time filtering only becomes a problem at the scale where V9's caching earned its keep, and that scale is exactly the “very specific requirement” V9 Simplified tells you to use V9 Complex for. For everyone else, always-fresh-and-simple beats fast-but-stale.

The whole model, in one function

Step back and the achievement is that V9 Simplified's entire authorization model reduces to one function — HasPermission — used in CASE for columns, WHERE for rows, and label-deriving CASE for description. There is no permission subsystem; there is a helper and a discipline of calling it in the right places. That is what “simplified” earns you: not a smaller version of a complex thing, but a genuinely different shape where the capability lives in plain SQL instead of dedicated machinery.

There is one thing V9 Simplified still caches, and it made a smarter choice about what than V9 did. Instead of caching yes/no permission answers, it caches finished calculation results — sidestepping the revocation hazard entirely. That better-chosen cache is next.