🔒 Multi-Tenant Data Isolation
SaaS tenant separation architecture and enforcement mechanisms
Executive Summary
AIUsagePlatform implements shared-database multi-tenancy with row-level security. Each tenant's data is isolated via OrganizationId foreign keys across all tenant-scoped tables. The MultiTenantIsolationMiddleware enforces this isolation at the request level.
✅ Row-Level Security
OrganizationId FK on all tenant tables
OrganizationId FK on all tenant tables
✅ Middleware Enforcement
Automatic tenant context extraction
Automatic tenant context extraction
✅ SuperAdmin Bypass
Controlled cross-tenant access for admins
Controlled cross-tenant access for admins
⚠️ No DB Row Security
Relies on app layer, not DB RLS
Relies on app layer, not DB RLS
🏗️ Multi-Tenant Architecture
Shared Database Multi-Tenancy Model
═══════════════════════════════════════════════════════════════════════════════
┌─────────────────────────────────────────────────────────────────────────────┐
│ SINGLE DATABASE - MULTIPLE TENANTS │
│ (Shared Schema Approach) │
├─────────────────────────────────────────────────────────────────────────────┤
│ │
│ ┌─────────────────────────────────────────────────────────────────────┐ │
│ │ Organizations Table │ │
│ │ ┌──────────────┬────────────────┬──────────┬──────────┐ │ │
│ │ │ Id │ Name │ Plan │ Status │ │ │
│ │ ├──────────────┼────────────────┼──────────┼──────────┤ │ │
│ │ │ org-acme │ Acme Corp │ Pro │ Active │ │ │
│ │ │ org-tech │ TechStart Inc │ Basic │ Active │ │ │
│ │ │ org-global │ GlobalCo │Enteprise │Suspended │ │ │
│ │ └──────────────┴────────────────┴──────────┴──────────┘ │ │
│ └─────────────────────────────────────────────────────────────────────┘ │
│ │
│ ┌─────────────────────────────────────────────────────────────────────┐ │
│ │ Users Table (Tenant-Scoped) │ │
│ │ ┌──────────────┬──────────────┬──────────────┬──────────┐ │ │
│ │ │ Id │ Email │ OrganizationId│ Role │ │ │
│ │ ├──────────────┼──────────────┼──────────────┼──────────┤ │ │
│ │ │ usr-john │john@acme.com │ org-acme │ Admin │ │ │
│ │ │ usr-jane │jane@acme.com │ org-acme │ User │ │ │
│ │ │ usr-bob │ bob@tech.com │ org-tech │ Admin │ │ │
│ │ └──────────────┴──────────────┴──────────────┴──────────┘ │ │
│ │ │ │
│ │ ⚠ CRITICAL: Every tenant-scoped table has OrganizationId FK │ │
│ │ │ │
│ └─────────────────────────────────────────────────────────────────────┘ │
│ │
│ ┌─────────────────────────────────────────────────────────────────────┐ │
│ │ Sessions Table (Tenant-Scoped) │ │
│ │ ┌──────────────┬──────────────┬──────────────┬────────────┐ │ │
│ │ │ Id │ UserId │ OrganizationId│ Start │ │ │
│ │ ├──────────────┼──────────────┼──────────────┼────────────┤ │ │
│ │ │ ses-001 │ usr-john │ org-acme │ 2026-01-15 │ │ │
│ │ │ ses-002 │ usr-jane │ org-acme │ 2026-01-15 │ │ │
│ │ │ ses-003 │ usr-bob │ org-tech │ 2026-01-15 │ │ │
│ │ └──────────────┴──────────────┴──────────────┴────────────┘ │ │
│ │ │ │
│ │ Query Pattern: │ │
│ │ SELECT * FROM Sessions WHERE OrganizationId = @orgId │ │
│ │ │ │
│ └─────────────────────────────────────────────────────────────────────┘ │
│ │
│ Tenant Isolation Benefits: │
│ • ✅ Single codebase for all tenants │
│ • ✅ Simplified backups and migrations │
│ • ✅ Cross-tenant analytics possible (SuperAdmin) │
│ • ⚠️ Risk of data leakage if queries miss WHERE OrganizationId clause │
│ │
└─────────────────────────────────────────────────────────────────────────────┘
🔌 MultiTenantIsolationMiddleware
Middleware Execution Flow
═══════════════════════════════════════════════════════════════════════════════
Incoming Request
│
▼
┌─────────────────────────────────────────────────────────────────────────────┐
│ MultiTenantIsolationMiddleware (Custom) │
├─────────────────────────────────────────────────────────────────────────────┤
│ │
│ Step 1: Bypass Check │
│ ───────────────────────────────────────────────────────────────────────── │
│ IF path is in bypass list (auth, swagger, health, agent-onboarding) │
│ → Skip tenant validation, continue to next middleware │
│ │
│ Step 2: Extract Tenant Context │
│ ───────────────────────────────────────────────────────────────────────── │
│ Primary: Extract OrganizationId from JWT claims (sub claim) │
│ Fallback: Check X-Organization-Key header (for service accounts) │
│ │
│ IF no OrganizationId found │
│ → Return 401 Unauthorized │
│ │
│ Step 3: SuperAdmin Check │
│ ───────────────────────────────────────────────────────────────────────── │
│ IF user has SuperAdmin role │
│ → BYPASS all tenant checks (explicit permission) │
│ → Set IsSuperAdmin = true in TenantContext │
│ → Continue (SuperAdmin can access all tenants) │
│ │
│ Step 4: Validate Organization │
│ ───────────────────────────────────────────────────────────────────────── │
│ Query Organization table: │
│ SELECT * FROM Organizations WHERE Id = @orgId │
│ │
│ IF organization not found │
│ → Return 403 Forbidden "Invalid organization" │
│ │
│ IF organization.Status != Active │
│ → Return 403 Forbidden "Organization suspended/expired" │
│ │
│ Step 5: Subscription Validation │
│ ───────────────────────────────────────────────────────────────────────── │
│ Check Subscription: │
│ SELECT * FROM Subscriptions │
│ WHERE OrganizationId = @orgId AND Status = 'Active' │
│ AND EndDate > GETUTCDATE() │
│ │
│ IF no active subscription │
│ → Return 402 Payment Required "Subscription expired" │
│ │
│ Step 6: Feature Gate Check (Optional) │
│ ───────────────────────────────────────────────────────────────────────── │
│ IF endpoint has [RequiresPlanFeature] attribute │
│ → Check if feature is enabled in organization's plan │
│ → Return 403 if feature not available │
│ │
│ Step 7: Rate Limiting Check │
│ ───────────────────────────────────────────────────────────────────────── │
│ Check Redis for rate limit counters per OrganizationId │
│ → Return 429 Too Many Requests if exceeded │
│ │
│ Step 8: Attach Tenant Context │
│ ───────────────────────────────────────────────────────────────────────── │
│ Store in HttpContext.Items: │
│ • "OrganizationId" → extracted org ID │
│ • "IsSuperAdmin" → true/false │
│ │
│ Attach Response Headers: │
│ • X-Tenant-Id: org_xxx │
│ • X-Tenant-Status: Active │
│ │
│ Step 9: Continue Pipeline │
│ ───────────────────────────────────────────────────────────────────────── │
│ → Call next middleware/controller │
│ │
└─────────────────────────────────────────────────────────────────────────────┘
🛡️ Isolation Enforcement Points
| Layer | Enforcement Mechanism | Responsibility |
|---|---|---|
| Middleware | MultiTenantIsolationMiddleware | Extract tenant, validate status, attach context |
| Controller | BaseApiController.GetCurrentOrganizationId() | Access tenant context from claims |
| Service | OrganizationId parameter on all methods | Pass tenant context to repositories |
| Repository | WHERE OrganizationId = @orgId on all queries | Enforce row-level isolation |
| Database | Foreign key constraints | Referential integrity, cascade delete |
📊 Tenant Data Access Patterns
Standard User Flow
User Login
↓
JWT Created: { sub: usr-123, org: org-456 }
↓
Request /api/sessions
↓
Middleware: Extract org-456 from JWT
↓
Validate org-456 exists & Active
↓
Repository Query:
SELECT * FROM Sessions
WHERE OrganizationId = 'org-456'
AND UserId = 'usr-123'
↓
Return: Only user's org sessions
SuperAdmin Flow
SuperAdmin Login
↓
JWT Created: { sub: usr-admin, role: SuperAdmin }
↓
Request /api/adminanalytics
↓
Middleware: Detect SuperAdmin role
↓
BYPASS tenant validation
↓
Set IsSuperAdmin = true
↓
Service Query:
SELECT * FROM Sessions
(no WHERE OrganizationId!)
↓
Return: All tenants' data
⚠️ Security Risks & Mitigations
Missing WHERE Clause
Developers may forget OrganizationId filter. Mitigation: Code review, integration tests, repository base class enforcement.
SuperAdmin Privilege Escalation
Compromised SuperAdmin account = full platform access. Mitigation: IP whitelisting, MFA, audit logging.
Defense in Depth
Multiple enforcement layers (middleware, service, repository) reduce single-point-of-failure risk.
Status Validation
Suspended/expired organizations automatically blocked at middleware layer before reaching business logic.
✅ Recommended Enhancements
Repository Base Class
Create TenantRepositoryBase
Recommended
Integration Tests
Automated tests that verify Tenant A cannot access Tenant B's data for every endpoint.
Recommended
SQL Server RLS
Enable Row-Level Security policies in SQL Server as additional defense layer.
Recommended