Skip to content

project_mtp_authz_model.md

Latest commit

 

History

History
57 lines (51 loc) · 6.31 KB

project_mtp_authz_model.md

File metadata and controls

57 lines (51 loc) · 6.31 KB
Raw
name project-mtp-authz-model
description Authorization model — single Permission concept, single-column ltree/jsonb scope, JWT claims, menu derived. Replaces GNUMS SEC_ sprawl.
metadata
type
project

Target authorization model for the multi-tenant platform. Detailed rationale in ADR-0004. Companion memory: [[project-mtp-tenancy-model]], [[project-mtp-persistence-model]], [[project-mtp-postgres-ltree]].

Why: Legacy GNUMS SEC_ schema (~50 tables, 4082 operations, 5575 menus, 297 roles, 58k allocations, 151 TVFs) is unfit for replication. Concrete smells: 15 nullable scope FK columns on SEC_RoleAllocation, Menu vs Operation duplication, SQL-Server TVF lock-in, session-based auth, kitchen-sink SEC_ mixing authz + login audit + approval workflow + dashboards + glossary.

Decisions (locked):

  1. No copying GNUMS tables. INS_Department, INS_Program, INS_Faculty, INS_Course, SEC_Role, SEC_Operation, SEC_Menu etc. are input only. Their hierarchy is also not assumed — different tenants have different depth/labels/dimensions. Our own tables, our own model.
  2. Configurable org hierarchy. org."OrgLevelType" (per-tenant catalogue of level codes/labels/depth) + org."OrgNode" (single tree with Path ltree). Adding a new dimension a client needs = insert one row, no schema change, no code change.
  3. One Permission concept. Single string-keyed code (e.g. exam.result.publish) + dense PermissionId smallint for bitmap-style checks. No separate Menu + Operation. UI menu = projection of granted permission set + per-tenant menu config.
  4. Permission codes are forever (Hard Rule 11 applied to authz).
  5. Single scope column per grant row — ScopePath ltree for hierarchical scope, ScopeExtra jsonb for non-hierarchical extras. Never 15 nullable FKs.
  6. Top-level scope vanishes under DB-per-client. No UniversityID/InstituteID/CompanyID columns. In-tenant org dimensions collapse into one OrgNodeId (FK). OrgPath ltree is conditionally denormalised — see decision 5a. 5b. One canonical tree per tenant, not a DAG. GNUMS INS_* tables form a DAG (Faculty↔Institute many-to-many via INS_InstituteWiseFaculty, self-nesting Institute/Department, Course skips Institute, Program has 5 simultaneous parents, parallel mirrors like SalaryFacultyID/DegreeFacultyID). We do not port this faithfully. Each tenant picks ONE primary academic tree for authz scoping. Secondary dimensions (salary, exam centre, hostel cluster, transport route) go into ScopeExtra jsonb, not extra ltree columns. Multi-tree-per-tenant deferred until a concrete tenant demands it. 5c. Empirical depth check (May 2026 GNUMS_Development snapshot): max observed scope FKs populated on SEC_RoleAllocation = 6 (across 56 of 58 236 rows). Typical = 4 (62%). 8-level ltree cap has 2 levels of headroom. 5a. Two storage patterns, picked per business table:
    • Pattern A (denormalised): OrgPath stored on every row. Use for hot, huge, write-heavy tables (audit log, ledger, exam attempts, attendance, payment lines). Fastest filter, but org-restructure requires bulk rewrite job.
    • Pattern B (joined): only OrgNodeId on row; filter joins org."OrgNode" (or materialised view org."NodePath"). Use for lower-volume entities (Student, Staff, Course, Result, LibraryItem). Tiny join, big storage win, painless org moves.
    • Path cap: 8 levels × 32 chars/label ≈ 256 chars total, CHECK-enforced.
    • Org-move event fires two jobs: OrgNode subtree path rewrite (always) + Pattern-A table fan-out (gated, chunked).
  7. JWT-backed auth. Token carries sub + client (tenant code). Permission set resolved server-side after token validation, cached per request. No session affinity.
  8. Insane-fast filtering — non-negotiable. Target: < 100 µs added per query.
    • Per-JWT PermissionSnapshot cached in-process (sliding TTL, invalidated by grant events).
    • FrozenSet<int> / bitmap of permission ids for O(1) check.
    • Materialised authz."UserScopeExpanded" (UserId, PermissionCode, OrgPath) — pre-expanded effective access, refreshed on grant events.
    • GIST on every Path ltree; GIN on every jsonb; partial index on live grants (ValidTo IS NULL OR ValidTo > now()).
    • IPermissionFilter<T> composes IQueryable<T> predicate (EF Core) or SQL fragment (Dapper). Never 151 SP TVFs.
    • Postgres native RLS = defence-in-depth on sensitive tables only, never primary.
  9. Domain separation:
    • identity schema → auth (login, password, OTP, lockout)
    • authz schema → roles, permissions, scope, grants
    • org schema → org hierarchy (level types + nodes)
    • workflow schema → approval flows (not authz)
    • Feature flags → separate concern, never in permission rows
    • Login + grant audit → Marten event stream (not per-table LOG_*/LOJ_* shadows)
  10. Grant history = event stream (UserGranted, UserRevoked, RoleAssigned, ScopeChanged) in Marten per user aggregate. Current state = projection. UserScopeExpanded is rebuilt from these events. Replayable.
  11. User-level override is first-class but bounded: authz."UserPermissionOverride" with explicit Effect = Grant|Deny, reason, validity window. No scatter across UserWiseFeeHead/UserWisePaymentMode/etc.

Core tables (sketch):

  • authz."Permission" — code, module, display name, metadata
  • authz."Role" — role definition + validity window
  • authz."RolePermission" — role → permission grants
  • authz."UserRole" — user → role with ScopePath (ltree) + ScopeExtra (jsonb) + validity
  • authz."UserPermissionOverride" — direct user grant/deny with scope + reason

How to apply:

  • New feature → define permission code first (e.g. module.entity.action), register in authz."Permission". Code stable forever.
  • Controllers stay MVC (Hard Rule 4); use [RequirePermission("code")] attribute filter, never MediatR pipeline behaviors.
  • Service layer pulls user's scope paths via IUserScopeProvider.GetScopePaths(permissionCode) and composes predicate via IPermissionFilter<T>.
  • Adding a new org dimension = adding an ltree segment definition, not a schema migration on every table.
  • When discussing GNUMS code that uses Session["UserID"], TVF_FilterBy*, or scope FK columns, treat as legacy pattern to translate, not to copy.