-- ═══════════════════════════════════════════════════════════════════════════ -- admin-api / tenant-api database + grant isolation setup -- ═══════════════════════════════════════════════════════════════════════════ -- Run this once, as MySQL root (or an equivalent privileged account), AFTER -- admin-api's migrations have created its tables (`npm run migrate` in -- admin-api, using ADMIN_DB_MIGRATE_USER — a privileged account, never -- admin_api_user itself, which has no DDL grants at all). -- -- This is the enforcement point the architecture requires: the isolation -- between admin_console and tenant_platform, and the immutability of -- admin_audit_log, must hold even if application code has a bug — so both -- are enforced here at the grant level, not only in admin-api's routes. -- -- IMPORTANT — MySQL grants are additive, not restrictive: a database-level -- GRANT ... ON admin_console.* always wins over any narrower table-level -- REVOKE on one table within it. That means audit-log immutability can only -- be achieved by granting DML per-table (never a blanket admin_console.* -- grant) and giving admin_audit_log itself only SELECT+INSERT. The list -- below must be kept in sync with admin-api/db/migrations/ — add a line -- here whenever a migration adds a new table. -- -- MVP note: this repo currently runs tenant-api's database as `lpms_dev` -- (see backend/db/knexfile.js) rather than a database literally named -- `tenant_platform` — adjust the REVOKE block below if you rename it. Both -- databases living on the same MySQL server for now is the documented -- cost-saving shortcut; nothing below assumes that stays true — splitting -- them onto separate servers later is a connection-string change only. -- -- Usage: -- mysql -u root -p < admin-api/db/setup-grants.sql -- ═══════════════════════════════════════════════════════════════════════════ -- ── 1. Create the admin_console database ──────────────────────────────────── CREATE DATABASE IF NOT EXISTS admin_console CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; -- ── 2. Create admin_api_user ──────────────────────────────────────────────── -- Change this password before running against anything but a local dev box, -- and put the real value only in admin-api/.env (ADMIN_DB_PASS), never here. CREATE USER IF NOT EXISTS 'admin_api_user'@'%' IDENTIFIED BY 'CHANGE_ME_ADMIN_DB_PASSWORD'; -- ── 3. Per-table grants — full DML everywhere except admin_audit_log ─────── GRANT SELECT, INSERT, UPDATE, DELETE ON admin_console.admin_roles TO 'admin_api_user'@'%'; GRANT SELECT, INSERT, UPDATE, DELETE ON admin_console.admin_permissions TO 'admin_api_user'@'%'; GRANT SELECT, INSERT, UPDATE, DELETE ON admin_console.admin_role_permissions TO 'admin_api_user'@'%'; GRANT SELECT, INSERT, UPDATE, DELETE ON admin_console.admin_users TO 'admin_api_user'@'%'; GRANT SELECT, INSERT, UPDATE, DELETE ON admin_console.admin_user_mfa TO 'admin_api_user'@'%'; GRANT SELECT, INSERT, UPDATE, DELETE ON admin_console.admin_sessions TO 'admin_api_user'@'%'; GRANT SELECT, INSERT, UPDATE, DELETE ON admin_console.admin_ip_allowlist TO 'admin_api_user'@'%'; -- Phase 2 (tenant lifecycle) added no new tables to admin_console — everything -- lives in tenant-api's own database, reached only via /internal/* calls. -- Phase 3 (billing/payment config) GRANT SELECT, INSERT, UPDATE, DELETE ON admin_console.billing_plans TO 'admin_api_user'@'%'; GRANT SELECT, INSERT, UPDATE, DELETE ON admin_console.billing_subscriptions TO 'admin_api_user'@'%'; GRANT SELECT, INSERT, UPDATE, DELETE ON admin_console.billing_subscription_invoices TO 'admin_api_user'@'%'; GRANT SELECT, INSERT, UPDATE, DELETE ON admin_console.payment_gateway_credentials TO 'admin_api_user'@'%'; GRANT SELECT, INSERT, UPDATE, DELETE ON admin_console.payment_routing_rules TO 'admin_api_user'@'%'; -- Payment Orchestration Service (Law Firm -> JurisLink subscription payments) GRANT SELECT, INSERT, UPDATE, DELETE ON admin_console.payment_transactions TO 'admin_api_user'@'%'; GRANT SELECT, INSERT, UPDATE, DELETE ON admin_console.refund_requests TO 'admin_api_user'@'%'; -- Phase 4 (monitoring, support, system-wide issue resolution). Note: -- maintenance_windows/platform_kill_switches/job_run_log live in tenant-api's -- lpms_dev, NOT here — they're read by backend's own enforcement middleware -- on every tenant request, so they must be data backend can query directly. -- No new grants needed there: tenant_api_user already has a schema-level -- DML grant on lpms_dev.* (see §4 below), which covers new tables automatically. GRANT SELECT, INSERT, UPDATE, DELETE ON admin_console.service_health_checks TO 'admin_api_user'@'%'; GRANT SELECT, INSERT, UPDATE, DELETE ON admin_console.security_alerts TO 'admin_api_user'@'%'; GRANT SELECT, INSERT, UPDATE, DELETE ON admin_console.support_tickets TO 'admin_api_user'@'%'; GRANT SELECT, INSERT, UPDATE, DELETE ON admin_console.kb_articles TO 'admin_api_user'@'%'; -- Storefront marketing blog (docs/SPEC.md §33.9, migration 026) — read by -- both the authenticated admin-web CRUD surface and the unauthenticated -- public endpoint (routes/publicRoutes.js), both same DB user. GRANT SELECT, INSERT, UPDATE, DELETE ON admin_console.blog_posts TO 'admin_api_user'@'%'; GRANT SELECT, INSERT, UPDATE, DELETE ON admin_console.deployments TO 'admin_api_user'@'%'; -- Phase 5 (compliance/Act 843, analytics, backup/DR tracking, integration -- registry). Same note as Phase 4 for api_keys/platform_announcements — -- those live in lpms_dev (backend migration 155), not here, since backend -- must check them on incoming requests. GRANT SELECT, INSERT, UPDATE, DELETE ON admin_console.dsar_requests TO 'admin_api_user'@'%'; GRANT SELECT, INSERT, UPDATE, DELETE ON admin_console.breach_incidents TO 'admin_api_user'@'%'; GRANT SELECT, INSERT, UPDATE, DELETE ON admin_console.backup_records TO 'admin_api_user'@'%'; GRANT SELECT, INSERT, UPDATE, DELETE ON admin_console.restore_tests TO 'admin_api_user'@'%'; GRANT SELECT, INSERT, UPDATE, DELETE ON admin_console.third_party_integrations TO 'admin_api_user'@'%'; GRANT SELECT, INSERT, UPDATE, DELETE ON admin_console.tax_currency_config TO 'admin_api_user'@'%'; GRANT SELECT, INSERT, UPDATE, DELETE ON admin_console.webhook_deliveries TO 'admin_api_user'@'%'; -- Phase 6 (module 13 — in-app notifications; foundation for modules 14/15). GRANT SELECT, INSERT, UPDATE, DELETE ON admin_console.admin_notifications TO 'admin_api_user'@'%'; -- Phase 6 (module 14 — SMS/email alerting + escalation). GRANT SELECT, INSERT, UPDATE, DELETE ON admin_console.alert_channel_config TO 'admin_api_user'@'%'; GRANT SELECT, INSERT, UPDATE, DELETE ON admin_console.alert_escalation_rules TO 'admin_api_user'@'%'; GRANT SELECT, INSERT, UPDATE, DELETE ON admin_console.alert_escalation_chain TO 'admin_api_user'@'%'; GRANT SELECT, INSERT, UPDATE, DELETE ON admin_console.alert_delivery_log TO 'admin_api_user'@'%'; -- Phase 6 (module 16 — per-tenant resource usage reporting). GRANT SELECT, INSERT, UPDATE, DELETE ON admin_console.tenant_usage_rollups TO 'admin_api_user'@'%'; GRANT SELECT, INSERT, UPDATE, DELETE ON admin_console.tenant_api_call_rollups TO 'admin_api_user'@'%'; -- Phase 6 (module 15 — threat detection thresholds; the alerts themselves -- reuse the existing security_alerts table/grant from Phase 4). GRANT SELECT, INSERT, UPDATE, DELETE ON admin_console.threat_detection_config TO 'admin_api_user'@'%'; -- Plan Builder (docs/AUDIT.md, deferred item #5 follow-up) — per-feature -- entitlement matrix per plan tier, edited via admin-web's new Plan Builder -- UI and pushed to the tenant backend's plan_tier_entitlements table on save. GRANT SELECT, INSERT, UPDATE, DELETE ON admin_console.plan_entitlements TO 'admin_api_user'@'%'; -- §17.3 sales-assisted onboarding ("Contact Sales") request queue — public -- submission via /api/v1/public/signup-requests, reviewed in admin-web's new -- Sales Requests page. GRANT SELECT, INSERT, UPDATE, DELETE ON admin_console.platform_signup_requests TO 'admin_api_user'@'%'; -- Phase 7 (module 7 — break-glass credential recovery). The runtime app only -- ever SELECTs a candidate token and UPDATEs it to used_at; INSERT exists so -- scripts/issue-recovery-token.js (which connects as this same scoped user, -- not the privileged migration user) can mint new tokens. GRANT SELECT, INSERT, UPDATE, DELETE ON admin_console.admin_recovery_tokens TO 'admin_api_user'@'%'; -- Audit-log immutability: SELECT + INSERT only, ever. No route in this -- codebase issues UPDATE/DELETE against this table — this grant means MySQL -- itself would reject it even if one did. GRANT SELECT, INSERT ON admin_console.admin_audit_log TO 'admin_api_user'@'%'; -- Explicit belt-and-suspenders REVOKE — makes the tenant-isolation boundary -- visible in this file even though a fresh user has no grants elsewhere by -- default. Replace `lpms_dev` with your tenant-api database name if renamed. REVOKE ALL PRIVILEGES ON lpms_dev.* FROM 'admin_api_user'@'%'; -- ── 4. Create tenant_api_user, scoped to the tenant database only ────────── -- Phase 2: closes the isolation gap flagged in Phase 1 — tenant-api -- (backend/) now connects as this scoped user (backend/.env's DB_USER), -- never as root, so root's incidental access to admin_console is no longer -- something tenant-api's runtime code can exploit even if compromised. -- Change this password before running against anything but a local dev box, -- and put the real value only in backend/.env (DB_PASS) / root .env, never here. -- Replace `lpms_dev`/`lpms_test` if you've renamed tenant-api's databases. CREATE USER IF NOT EXISTS 'tenant_api_user'@'%' IDENTIFIED BY 'CHANGE_ME_TENANT_DB_PASSWORD'; GRANT SELECT, INSERT, UPDATE, DELETE ON lpms_dev.* TO 'tenant_api_user'@'%'; GRANT SELECT, INSERT, UPDATE, DELETE ON lpms_test.* TO 'tenant_api_user'@'%'; REVOKE ALL PRIVILEGES ON admin_console.* FROM 'tenant_api_user'@'%'; REVOKE ALL PRIVILEGES ON admin_console_test.* FROM 'tenant_api_user'@'%'; FLUSH PRIVILEGES; -- ── 5. Verify the boundary ─────────────────────────────────────────────── -- SHOW GRANTS FOR 'admin_api_user'@'%'; -- Expected output: per-table GRANTs on `admin_console` tables only, with -- admin_audit_log limited to SELECT, INSERT — and nothing mentioning -- lpms_dev/tenant_platform at all. admin-api/tests/db-grant-boundary.test.js -- automates this check against a live connection.