Cloud Connector — Multi-Tenant DB

Multi-tenant Azure SQL architecture with Row-Level Security. Each tenant sees only their data — enforced inside the database, not the app tier. Built for a SaaS product at Arete IT (2021) — contributed to a 35% sales lift in the quarter following launch.

See CHANGELOG.md for the version history (2021 → 2026).

What this gives you

• 4 tenants, each with isolated Records + Users, sharing one DB
• SECURITY POLICY with FILTER + BLOCK predicates on every tenant-scoped table
• A predicate function that reads SUSER_SNAME() and SESSION_CONTEXT('TenantId')
• Stored procedures so the app tier never writes raw DML
• Append-only audit table for privileged cross-tenant operations
• Python client (pyodbc) that sets the tenant context on every connection
• A test suite (sql/tests/) that fails the build if RLS ever regresses

Repository layout

cloud-connector-multitenant/
├── sql/
│   ├── 001_schema.sql              tables: Tenants, Users, Records, sec.UserMap
│   ├── 002_security_function.sql   sec.fn_TenantPredicate (SCHEMABINDING)
│   ├── 003_security_policy.sql     SECURITY POLICY sec.TenantIsolation
│   ├── 004_indexes.sql             nonclustered indexes for hot paths
│   ├── 005_seed_tenants.sql        4 sample tenants + a few records each
│   ├── 006_audit.sql               audit.RecordTransfers
│   ├── procedures/                 sp_RegisterTenant, sp_AddRecord, …
│   ├── views/v_TenantSummary.sql   per-tenant rollup
│   └── tests/                      test_rls_isolation, test_cross_tenant_blocked
├── client/                         Python pyodbc client + demo
├── deploy/deploy.sh / deploy.ps1   sqlcmd-driven deploy
├── docs/architecture.md
└── docs/rls_threat_model.md

Deploy

# Linux / macOS
export SQL_SERVER=myserver.database.windows.net
export SQL_DB=multitenant
export SQL_USER=admin
export SQL_PASSWORD='your-password'
bash deploy/deploy.sh

# Windows
.\deploy\deploy.ps1 -Server "myserver.database.windows.net" `
                    -Database "multitenant" `
                    -User "admin" `
                    -Password (Read-Host -AsSecureString)

The script applies schema, RLS, indexes, procedures, views, then seeds 4 sample tenants and runs the isolation tests.

Quickstart from Python

from client.connector import CloudConnector
import os

c = CloudConnector(
    server=os.environ['SQL_SERVER'],
    database=os.environ['SQL_DB'],
    user=os.environ['SQL_USER'],
    password=os.environ['SQL_PASSWORD'],
)

with c.session(tenant_id='AAAAAAAA-1111-1111-1111-111111111111') as cur:
    for r in c.list_records(cur, status='open'):
        print(r.record_id, r.title, r.amount)

Threat model

See docs/rls_threat_model.md. Short version:

The app tier sets SESSION_CONTEXT('TenantId') and never lets user input reach that call. That's the one rule. The DB does the rest.

Verifying isolation

Two regression tests live in sql/tests/:

• test_rls_isolation.sql — Tenant A sees its own rows and 0 cross-tenant rows.
• test_cross_tenant_blocked.sql — INSERT/UPDATE that would change a row's TenantId throws as expected.

CI runs sqlfluff on every PR. Add the integration tests to your nightly pipeline by adding the deploy step's tail (bash deploy/deploy.sh) to a GitHub Actions job with secrets SQL_* set.

Performance notes

• Predicate fn is WITH SCHEMABINDING — eligible for plan caching.
• IX_Records_Tenant_Status_UpdatedUtc covers the dashboard's hot query.
• v_TenantSummary can be promoted to an indexed view if you start hitting the per-row aggregation cost on dashboards.

License

MIT — see LICENSE.