Security Architecture

// From: src/app/api/sso-spn/byod/databricks/catalogs/mount/route.ts

// After creating a catalog with Global Admin SPN:
// Grant permissions to the user's service principal

async function mountCatalog(orgId: string, userEmail: string, catalogName: string) {
  // 1. Get global admin token (elevated privileges)
  const adminToken = await getGlobalAdminToken(workspaceUrl);

  // 2. Create the Delta Sharing catalog
  await createDeltaSharingCatalog(workspaceUrl, adminToken, catalogName, provider, share);

  // 3. Get the user's SPN from database
  const userSpn = await db.query.userSpns.findFirst({
    where: eq(userSpns.email, userEmail),
  });

  // 4. Grant limited permissions to user's SPN (not admin permissions)
  await updateCatalogPermissions(workspaceUrl, adminToken, catalogName, [
    {
      principal: userSpn.clientId,
      add: ["BROWSE", "EXECUTE", "READ_VOLUME", "SELECT", "USE_CATALOG", "USE_SCHEMA"],
      // Note: No MODIFY, CREATE_*, or administrative permissions
    },
  ]);
}

// User action → Data access trace

1. Application Log:
   User: alice@company.com (ID: user_123)
   Action: Execute SQL Query
   Organization: Company Inc (ID: org_456)
   Timestamp: 2024-01-15T14:30:00Z
   Query: SELECT * FROM sales.transactions LIMIT 100

2. API Log:
   Endpoint: POST /api/2.0/sql/statements
   SPN: spn_company_inc
   Request ID: req_789
   Duration: 450ms
   Status: 200

3. Databricks Unity Catalog Log:
   Principal: spn_company_inc
   Action: SELECT
   Object: sales.transactions
   Timestamp: 2024-01-15T14:30:00.123Z
   Rows Returned: 100

// Example: Encrypting SPN credentials before storage
import { encryptToken, decryptToken } from "@/lib/token-encryption";

// When storing SPN credentials
async function createOrganizationSpn(orgId: string, clientId: string, clientSecret: string) {
  const encryptedSecret = encryptToken(clientSecret);

  await db.insert(byodDatabricksSpns).values({
    id: generateId(),
    organizationId: orgId,
    clientId: clientId,           // Client ID can remain plaintext
    clientSecret: encryptedSecret, // Encrypted with AES-256-GCM
    createdAt: new Date(),
    updatedAt: new Date(),
  });
}

// When retrieving SPN credentials
async function getOrganizationSpn(orgId: string) {
  const spn = await db.query.byodDatabricksSpns.findFirst({
    where: eq(byodDatabricksSpns.organizationId, orgId),
  });

  if (spn) {
    return {
      ...spn,
      clientSecret: decryptToken(spn.clientSecret), // Decrypt on read
    };
  }
  return null;
}

// Database schema addition for tenant keys
export const organizationKeys = pgTable("organization_keys", {
  id: text("id").primaryKey(),
  organizationId: text("organization_id")
    .notNull()
    .references(() => organization.id),
  keyVersion: integer("key_version").notNull().default(1),
  encryptedKey: text("encrypted_key").notNull(), // Wrapped with master key
  createdAt: timestamp("created_at").notNull().defaultNow(),
  rotatedAt: timestamp("rotated_at"),
});

// Key hierarchy:
// 1. Master Key (HSM or KMS) - Never stored in database
// 2. Tenant Keys - Encrypted with master key, stored in DB
// 3. Data - Encrypted with tenant key

async function getTenantKey(orgId: string): Promise<Buffer> {
  const keyRecord = await db.query.organizationKeys.findFirst({
    where: eq(organizationKeys.organizationId, orgId),
    orderBy: desc(organizationKeys.keyVersion),
  });

  // Decrypt tenant key using master key from KMS
  const masterKey = await kms.getKey("firefly-master-key");
  return unwrapKey(keyRecord.encryptedKey, masterKey);
}