Hierarchy as an Ordinal

The RBAC model V6 introduced rests on one integer: LevelOrder. Access is not decided by enumerating which objects a user may touch — it is decided by comparing numbers. An employee has a highest level; a request targets a level; if the employee's number is high enough, access is granted, and it cascades to everything below. This post is about that decision — how CheckPermission actually works, why I chose an ordinal over per-object grants, and the real limits of the choice.

Access is a comparison

The five hierarchy levels carry an ordinal: Employee (1), Office (2), Region (3), Organization (4), Global (5). CheckPermission uses those ordinals to answer “may this employee do this at this level?” The heart of it is a numeric comparison:

-- inside CheckPermission and GetFilteredAnalyticalData
AND hl.LevelOrder <= EmployeeLevelOrder     -- can see own level and below
AND hl.LevelOrder >= RequiredLevelOrder
AND CheckPermission(p_EmployeeId, 'VIEW_ANALYTICS', af.HierarchyLevel)

The rule reads directly off the ordinals. An employee has a highest LevelOrder — the top level they have been granted. A row (or a request) has a level with its own LevelOrder. If the row's level is less than or equal to the employee's, they can see it. So a Global (5) user sees levels 1 through 5 — everything. A Region (3) manager sees 1 through 3 — their region, its offices, its employees — but not the organisation above them. An Employee (1) sees only level 1: themselves.

CheckPermission grabs the employee's maximum granted LevelOrder and compares numerically. That single comparison is the access decision. There is no per-row grant table, no access-control list, no object-level bookkeeping. Clearance is a number, and visibility is everything at or below it.

Why an ordinal

This is a deliberately coarse model, and I chose it on purpose over the more granular alternative. The obvious “correct” design is an access-control list: a table saying exactly which objects each user may see, row by row. ACLs are precise — you can grant access to one specific region and nothing else, express arbitrary exceptions, model any policy. They are also a lot of rows, a lot of joins, and a lot of maintenance, and they answer a question most org-shaped systems do not actually ask.

An org hierarchy has a natural property: authority cascades downward. A regional manager's authority over their region implies authority over its offices and their employees — that is what “manager” means. An ordinal encodes exactly that cascade for free. LevelOrder <= EmployeeLevelOrder is the whole policy, and it matches the shape of the org: higher in the tree sees more, and it composes without enumeration. For a system whose entire domain is a four-level hierarchy, the ordinal is not a shortcut around the real model — it is the real model, expressed as arithmetic.

The payoff is that permission checks are cheap and permission data is tiny. An employee needs one grant per (permission type, level); visibility of thousands of rows falls out of a single comparison. No ACL to populate, no per-object grant to keep in sync when the org reshuffles.

The matrix that makes it concrete

The clearest demonstration is a query that asks the same function about four employees at once and gets four different answers:

SELECT
    CheckPermission(1, 'VIEW_SALES',     'Employee') AS Emp1_Sales_Employee,
    CheckPermission(1, 'VIEW_ANALYTICS', 'Global')   AS Emp1_Analytics_Global,
    CheckPermission(2, 'VIEW_SALES',     'Office')    AS Emp2_Sales_Office,
    CheckPermission(3, 'VIEW_ANALYTICS', 'Region')    AS Emp3_Analytics_Region;

Employee 1, a sales manager with high clearance, comes back true for both — including VIEW_ANALYTICS at Global, because their ordinal reaches the top. Employees 2 and 3, lower in the hierarchy, come back true or false depending on whether their granted LevelOrder reaches the requested level. One function, four employees, four answers, all from ordinal comparisons. That query is the permission model in a single result set: access is not a lookup, it is a calculation.

The honest limits

An ordinal is coarse, and coarse has real costs I would not gloss over.

It cannot express exceptions. “This region manager may see their region and also one specific office in a neighbouring region” is inexpressible — the model has no way to grant a single out-of-hierarchy object. The moment your policy has exceptions to the cascade, the ordinal breaks and you need ACLs after all.

It cannot do lateral restriction. Two region managers at the same level both have LevelOrder = 3. The ordinal model, on its own, does not stop one from seeing the other's region — they are the same height. V6 handles this by also scoping to the employee's own subtree in the filtering functions, but the raw ordinal comparison alone is height-based, not branch-based, and conflating “how high” with “which branch” is a real hazard to keep in mind.

It flattens permission types. In practice the model leans heavily on the LevelOrder and treats the eight permission types somewhat uniformly. A richer system would cross permission type with level more carefully than the ordinal comparison naturally encourages.

I accepted all three because V6's job was to demonstrate permission-aware analytics on a hierarchy, not to be a general authorization engine. For the org-shaped domain it targets, the ordinal is genuinely the right altitude of complexity — precise enough to be useful, cheap enough to compute inline in every aggregate. If the requirements grew exceptions and lateral rules, I would move to ACLs and pay for them then; I would not pay for them speculatively.

From “may they” to “show them only”

CheckPermission answers a yes/no question. The more interesting move is applying that answer inside an aggregate, so a query returns different numbers to different people without either of them writing a WHERE clause. That is the aggregate that censors itself — GetFilteredAnalyticalData, where rows self-select by the caller's clearance, and the same query becomes many queries depending on who runs it.