ADR 0005 — Group / role / permission → JWT claim mapping#

Status: Accepted (Phase 1, 2026-07-14) — revised 2026-08-18 against real legacy schema in cubedb/ Depends on: 0001 — Authentication model

Context#

Phase 0 stubbed roles and permissions on the JWT as empty arrays. To progress on Phase 1 we need a real mapping from the legacy security model to JWT claims.

The legacy security model, taken directly from cubedb/CUBE/Tables/:

aspnet_Users ──< aspmnx_groupUsers >── aspmnx_Groups ──< aspmnx_GroupRoles >── aspnet_Roles
                                                                                    │
                                                                                    ├── ModuleId (FK → MnxModule)
                                                                                    └── IsSpecial (bit)

aspnet_Profile (per-user module admin flags)
   ├── CompanyAdmin, AcctAdmin, ProdAdmin, ScmAdmin, CrmAdmin, EngAdmin
   ├── CAPAAdmin, SuperUser, ProjectsTasks (EQS)
   └── IsItarAuthorized

MnxModule
   ├── ModuleId, ModuleName, Abbreviation
   └── IsPermission (bit) — distinguishes permission-check nodes from nav-only nodes

Concrete column casing (from cubedb/CUBE/Tables/):

• aspmnx_Groups — groupId, groupName, createDate
• aspmnx_groupUsers — groupusersID, fkuserid, fkgroupid
• aspmnx_GroupRoles — GroupRoleID, fkRoleId, fkGroupId
• aspnet_Roles — RoleId, RoleName, LoweredRoleName, Description, ModuleId, IsSpecial — no IsPermission column
• aspnet_Profile — the admin flags above plus IsItarAuthorized
• MnxModule — ModuleId, ModuleName, IsPermission

Runtime resolution today goes through aspmnx_GetRoles4UserModules(userId, moduleId, parentModule, childModule) (cubedb/CUBE/Procedures/dbo.aspmnx_GetRoles4UserModules.sql). It:

1. Resolves @moduleId from parent/child navigation if not supplied.
2. Returns roles for that ModuleId where the user has them via aspmnx_groupUsers → aspmnx_GroupRoles.
3. Unions in all IsSpecial=1 roles the user is granted via a group — special roles are module-agnostic at resolution time.
4. Overrides Assigned=1 when the user has the matching module admin flag on aspnet_Profile:
   • CompanyAdmin → any module
   • SGA → AcctAdmin
   • MFG → ProdAdmin
   • SCM → ScmAdmin
   • SALES → CrmAdmin
   • ENG → EngAdmin
   • EQS → ProjectsTasks
5. Filters out rows where Assigned=0 (unassigned permissions).

Legacy runtime caches the result in HttpContext.Current.Session["GetRolesOfUser"] per session.

Two role flavours in aspnet_Roles:

• Regular module roles — IsSpecial=0. Belong to a specific ModuleId. Names look like SalesOrder.Create, Inventory.Read.
• Special roles — IsSpecial=1. Cross-cutting; unioned in when granted via a group, regardless of module context. IsItarAuthorized is not used to gate special roles — it’s a row-level data-scope concern used elsewhere (WO/inventory SPs), surfaced as a separate claim.

Decision#

Populate five claim types on the JWT at login time, resolved by the auth service in Manex.Web from the legacy tables. Server-side authorization uses standard ASP.NET Core [Authorize] / policies against these claims.

Claim shape#

• role uses the standard role claim type so [Authorize(Roles = "SalesOrder.Create")] works out of the box (.NET’s JwtSecurityTokenHandler maps role to ClaimTypes.Role).
• grp is included for auditability + downstream services that need the group ID rather than the resolved roles.
• mod_admin is a flat array (not a nested object) so authorization policies can check membership cheaply: User.HasClaim("mod_admin", "ENG").
• itar is a data-scope claim, not a role gate. Enforce it in row-level filters (e.g. WHERE ITAR = 0 OR @itar = 1) — the same pattern the legacy SPs use. Do not use it to gate special roles.

Admin-flag expansion#

Rather than encoding the CompanyAdmin=1 → all roles logic in every endpoint policy, UserAuthService.BuildUserInfoAsync expands admin flags into the role claim at token-issue time:

• CompanyAdmin=1 → include every role in aspnet_Roles where MnxModule.IsPermission=1.
• AcctAdmin=1 → include every role where MnxModule.ModuleName='SGA'.
• ProdAdmin=1 → include every role where MnxModule.ModuleName='MFG'.
• ScmAdmin, CrmAdmin, EngAdmin, ProjectsTasks → their respective modules.

This makes [Authorize(Roles="…")] work uniformly without special-casing admin flags in every controller.

Resolution query (LINQ against EF Core entities)#

At login, UserAuthService.BuildUserInfoAsync runs the equivalent of aspmnx_GetRoles4UserModules union’d across every permission-bearing module:

// 1. Roles granted via group membership.
var groupRoles =
    from gu in db.AspmnxGroupUsers
    where gu.FkUserId == userId
    join gr in db.AspmnxGroupRoles on gu.FkGroupId equals gr.FkGroupId
    join r  in db.AspnetRoles      on gr.FkRoleId  equals r.RoleId
    join m  in db.MnxModules       on r.ModuleId   equals m.ModuleId into mj
    from m in mj.DefaultIfEmpty()
    where r.IsSpecial == true || (m != null && m.IsPermission == true)
    select r.RoleName;

// 2. Roles granted implicitly by aspnet_Profile admin flags.
var adminRoles =
    from r in db.AspnetRoles
    join m in db.MnxModules on r.ModuleId equals m.ModuleId
    where m.IsPermission == true
       && (
             profile.CompanyAdmin
          || (profile.AcctAdmin        && m.ModuleName == "SGA")
          || (profile.ProdAdmin        && m.ModuleName == "MFG")
          || (profile.ScmAdmin == true && m.ModuleName == "SCM")
          || (profile.CrmAdmin == true && m.ModuleName == "SALES")
          || (profile.EngAdmin == true && m.ModuleName == "ENG")
          || (profile.ProjectsTasks    && m.ModuleName == "EQS")
       )
    select r.RoleName;

var roles = await groupRoles.Concat(adminRoles).Distinct().ToArrayAsync(ct);

Entity definitions live under src/Manex.Data/Entities/ (AspmnxGroupUser, AspmnxGroupRole, AspnetRole, AspnetProfile, MnxModule) and are registered on MnxEFContext. [Column] attributes handle the lowercase-column quirks in aspmnx_groupUsers and aspmnx_GroupRoles.

Phase 1 does the resolution once at login and includes all roles the user could ever exercise; per-request filtering happens on the .NET side via [Authorize] and HasClaim checks.

Refresh semantics#

JWT lifetime is 60 minutes (from ADR 0001). Role / group / admin-flag changes made in the admin UI take effect at most 60 minutes later, when the token expires and the client re-logs in. This is acceptable for Phase 1; if faster propagation is required we add a per-user token version stamp in Phase 2.

Consequences#

• The JWT grows to roughly 1.5-2 KB for a user with ~20 permissions. For CompanyAdmin=1 users we materialize every permission-bearing role — audit query 6 in deploy/sql/phase-1-schema-audit.sql measures the worst case. Well under HTTP header limits at expected volumes.
• No server-side session store required. Every request carries its own authorization context.
• Legacy stored proc aspmnx_GetRoles4UserModules is not called from Cube 2.0. The equivalent LINQ query is authored once in UserAuthService. The SP stays live because the legacy site still runs and we don’t modify the legacy database.
• [Authorize(Roles = "…")] and standard authorization policies work with no custom middleware.
• Cross-session changes to aspnet_Profile (e.g., an admin toggles ITAR or a module admin flag) take one token expiry to propagate.
• mod_admin and itar claims cannot be self-modified by a compromised client — they’re signed as part of the JWT.

What we deliberately don’t do#

• We don’t write to the legacy DB. Cube 2.0 never modifies aspnet_* or aspmnx_* tables — those stay owned by the legacy site. The audit script (deploy/sql/phase-1-schema-audit.sql) is read-only.
• We don’t put every fine-grained permission in the JWT unconditionally. For high-cardinality permissions (>100 for a single user, e.g. CompanyAdmin), we still materialize them — but if we hit HTTP header ceilings we introduce a “permission set version” stamp + a /api/v1/auth/permissions fetch.
• We don’t precompute per-module role lists at the API level. The legacy SP takes moduleId + parent/child module as parameters and walks MnxModuleRelationship. We union across all permission-bearing modules once at login and let the .NET side filter per endpoint. Simpler, easier to reason about.
• We don’t replicate the legacy session cache. HttpContext.Current.Session["GetRolesOfUser"] is a legacy-only concept; the JWT is our new “session cache,” signed and stateless.
• We don’t use itar to gate role resolution. ITAR is enforced at the data layer (row-level filters on ITAR-flagged records), mirroring how legacy SPs like GetProductionControlDetails_SP_4 use it.