Queries · q321 · Chief Information Security Officer · week 2, night 4
“Are broadly-scoped (admin_api) keys specifically a feature of our oldest, longest-unrotated keys, or is that scope just how every integration key is set up regardless of age — i.e. is 'old + broad' a rotation failure or simply the norm?”
Fictional people at a fictional company, each played by a language-model agent. Who they are and what they are told
The manager, at the start of week 2
Subject: Re: Account security snapshot — good start, three gaps worry me more now
Noah,
This is a strong first week — thank you. The sign-in and access-governance findings give me confidence I can defend in a questionnaire. Three things genuinely surprised me and I want to understand them better before Friday's memo, plus one follow-up.
If we can only chase a couple of these deeply this week, I'd prioritize the deletion-audit scope (#1) and the churn-deactivation scope (#3) — those are the two a due-diligence team is most likely to ask us to prove, one way or the other.
Thanks, Henrik
The analyst · week 2, night 4, request 2 of 3
Night 3 sharpened every thread nicely. Report 1 confirms the "no actor" gap in order_status_history is NOT cancellation-specific — it's 0.0% across every single status transition (pending, paid, fulfilled, cancelled, refunded; 150k+ rows, ~118 tenants) — a systemic table-wide property, not a narrow bug. Report 2 gives the clean #2 number: 88.3% of shoppers (9,910) are password-only with literally no backup factor, vs. 11.7% who at least inherit some protection from an OAuth provider — and recent-activity rates are nearly identical between the two groups (47.9% vs 46.9%), so OAuth adoption isn't just an artifact of inactive accounts. Report 3 is the most consequential: the long-lived (4+ year) active keys are dominated by integration-type keys ("ERP sync", "Accounting sync", "Warehouse management sync", "Marketplace product feed sync") nearly all carrying limit_scope = admin_api, used daily (every row shows ~5 days since last use, clustered right at month-end — looks like a nightly batch job), and they belong to our largest, most revenue-concentrated merchants (Cragsley Outfitters, Marrowby & Vane both appear with 4-5 such keys each). That's "old + broad + important account" together, exactly what Henrik flagged as the real risk combination.
Last night of the cycle — I want three closing numbers rather than opening new threads: (1) the full scope tally across all 76 old keys (the printed list only showed 50), (2) whether admin_api scope is concentrated in old keys specifically or is just how all integration keys are scoped regardless of age (tells us if "old+broad" is a rotation failure or a design norm), and (3) closing the loop between the churn and API-key findings by checking whether the 2 active keys sitting on churned tenants carry that same elevated scope.
The engineer · 1 dry run on the empty schema, then the submission
SELECT bucket AS age_bucket, COUNT(DISTINCT ak_id) AS total_active_keys, COUNT(DISTINCT CASE WHEN has_admin_api THEN ak_id END) AS admin_api_scoped_keys, ROUND(100.0 * COUNT(DISTINCT CASE WHEN has_admin_api THEN ak_id END) / NULLIF(COUNT(DISTINCT ak_id), 0), 1) AS admin_api_share_pct, COUNT(DISTINCT CASE WHEN no_scope_recorded THEN ak_id END) AS no_scope_recorded_keys, COUNT(DISTINCT tenant_id) AS distinct_tenants FROM ( SELECT ak.id AS ak_id, ak.tenant_id AS tenant_id, CASE WHEN (DATE '2026-01-05' - CAST(ak.created_at AS DATE)) < 90 THEN 'Under 90 days' WHEN (DATE '2026-01-05' - CAST(ak.created_at AS DATE)) < 365 THEN '90 days-1 year' WHEN (DATE '2026-01-05' - CAST(ak.created_at AS DATE)) < 730 THEN '1-2 years' WHEN (DATE '2026-01-05' - CAST(ak.created_at AS DATE)) < 1460 THEN '2-4 years' ELSE '4+ years' END AS bucket, CASE WHEN (DATE '2026-01-05' - CAST(ak.created_at AS DATE)) < 90 THEN 1 WHEN (DATE '2026-01-05' - CAST(ak.created_at AS DATE)) < 365 THEN 2 WHEN (DATE '2026-01-05' - CAST(ak.created_at AS DATE)) < 730 THEN 3 WHEN (DATE '2026-01-05' - CAST(ak.created_at AS DATE)) < 1460 THEN 4 ELSE 5 END AS bucket_order, CASE WHEN rl.has_admin_api = 1 THEN TRUE ELSE FALSE END AS has_admin_api, CASE WHEN rl.api_key_id IS NULL THEN TRUE ELSE FALSE END AS no_scope_recorded FROM api_keys ak LEFT JOIN ( SELECT api_key_id, MAX(CASE WHEN limit_scope = 'admin_api' THEN 1 ELSE 0 END) AS has_admin_api FROM rate_limits WHERE api_key_id IS NOT NULL GROUP BY api_key_id ) rl ON rl.api_key_id = ak.id WHERE ak.status = 'active' ) sub GROUP BY bucket, bucket_order ORDER BY bucket_order
“Query compiles cleanly with the right columns and bucket ordering. Submitting.”
“Submitted. Summary of what the report delivers:
- Grain: one row per active-key age bucket (5 fixed ranges, ordered youngest→oldest).
- Columns: age bucket, total active keys, admin_api-scoped keys, admin_api share (%), keys with no rate_limits row recorded, distinct tenants.
- Logic: api_keys filtered to status='active', bucketed by 2026-01-05 - created_at; left-joined to a per-key aggregation of rate_limits (flagging whether any row has limit_scope='admin_api') so multiple rate_limits rows per key don't double-count.
- Caveat flagged to the analyst: "no scope recorded" means the key has no rate_limits row at all, not just a non-admin scope — this directly supports distinguishing "old + broad is the norm" vs. "a rotation failure" since Noah can see whether admin_api share rises with age while total coverage (non-null scope) stays flat or also shifts.”
As Noah Fischer saw it, from the small dataset the agents worked on
age_bucket | total_active_keys | admin_api_scoped_keys | admin_api_share_pct | no_scope_recorded_keys | distinct_tenants ---------------+-------------------+-----------------------+---------------------+------------------------+----------------- Under 90 days | 1 | 1 | 100.0 | 0 | 1 90 days-1 year | 11 | 8 | 72.7 | 3 | 11 1-2 years | 14 | 12 | 85.7 | 2 | 9 2-4 years | 11 | 10 | 90.9 | 1 | 10 4+ years | 76 | 53 | 69.7 | 23 | 32 (5 rows)
The memo and the queries that followed; links found by title, so inferred
It builds on:
Changed only where the benchmark needs the same result on every system
SELECT bucket AS age_bucket, COUNT(DISTINCT ak_id) AS total_active_keys, COUNT(DISTINCT CASE WHEN has_admin_api THEN ak_id END) AS admin_api_scoped_keys, ROUND(100.0 * COUNT(DISTINCT CASE WHEN has_admin_api THEN ak_id END) / NULLIF(COUNT(DISTINCT ak_id), 0), 1) AS admin_api_share_pct, 100.0 * COUNT(DISTINCT CASE WHEN has_admin_api THEN ak_id END) / NULLIF(COUNT(DISTINCT ak_id), 0) AS admin_api_share_pct, COUNT(DISTINCT CASE WHEN no_scope_recorded THEN ak_id END) AS no_scope_recorded_keys, COUNT(DISTINCT tenant_id) AS distinct_tenantsFROM ( SELECT ak.id AS ak_id, ak.tenant_id AS tenant_id, CASE WHEN (DATE '2026-01-05' - CAST(ak.created_at AS DATE)) < 90 THEN 'Under 90 days' WHEN (DATE '2026-01-05' - CAST(ak.created_at AS DATE)) < 365 THEN '90 days-1 year' WHEN (DATE '2026-01-05' - CAST(ak.created_at AS DATE)) < 730 THEN '1-2 years' WHEN (DATE '2026-01-05' - CAST(ak.created_at AS DATE)) < 1460 THEN '2-4 years' ELSE '4+ years' END AS bucket, CASE WHEN (DATE '2026-01-05' - CAST(ak.created_at AS DATE)) < 90 THEN 1 WHEN (DATE '2026-01-05' - CAST(ak.created_at AS DATE)) < 365 THEN 2 WHEN (DATE '2026-01-05' - CAST(ak.created_at AS DATE)) < 730 THEN 3 WHEN (DATE '2026-01-05' - CAST(ak.created_at AS DATE)) < 1460 THEN 4 ELSE 5 END AS bucket_order, CASE WHEN rl.has_admin_api = 1 THEN TRUE ELSE FALSE END AS has_admin_api, CASE WHEN rl.api_key_id IS NULL THEN TRUE ELSE FALSE END AS no_scope_recorded FROM api_keys ak LEFT JOIN ( SELECT api_key_id, MAX(CASE WHEN limit_scope = 'admin_api' THEN 1 ELSE 0 END) AS has_admin_api FROM rate_limits WHERE api_key_id IS NOT NULL GROUP BY api_key_id ) rl ON rl.api_key_id = ak.id WHERE ak.status = 'active') subGROUP BY bucket, bucket_orderORDER BY bucket_orderORDER BY bucket_order NULLS LAST
SELECT bucket AS age_bucket, COUNT(DISTINCT ak_id) AS total_active_keys, COUNT(DISTINCT CASE WHEN has_admin_api THEN ak_id END) AS admin_api_scoped_keys, 100.0 * COUNT(DISTINCT CASE WHEN has_admin_api THEN ak_id END) / NULLIF(COUNT(DISTINCT ak_id), 0) AS admin_api_share_pct, COUNT(DISTINCT CASE WHEN no_scope_recorded THEN ak_id END) AS no_scope_recorded_keys, COUNT(DISTINCT tenant_id) AS distinct_tenants FROM ( SELECT ak.id AS ak_id, ak.tenant_id AS tenant_id, CASE WHEN (DATE '2026-01-05' - CAST(ak.created_at AS DATE)) < 90 THEN 'Under 90 days' WHEN (DATE '2026-01-05' - CAST(ak.created_at AS DATE)) < 365 THEN '90 days-1 year' WHEN (DATE '2026-01-05' - CAST(ak.created_at AS DATE)) < 730 THEN '1-2 years' WHEN (DATE '2026-01-05' - CAST(ak.created_at AS DATE)) < 1460 THEN '2-4 years' ELSE '4+ years' END AS bucket, CASE WHEN (DATE '2026-01-05' - CAST(ak.created_at AS DATE)) < 90 THEN 1 WHEN (DATE '2026-01-05' - CAST(ak.created_at AS DATE)) < 365 THEN 2 WHEN (DATE '2026-01-05' - CAST(ak.created_at AS DATE)) < 730 THEN 3 WHEN (DATE '2026-01-05' - CAST(ak.created_at AS DATE)) < 1460 THEN 4 ELSE 5 END AS bucket_order, CASE WHEN rl.has_admin_api = 1 THEN TRUE ELSE FALSE END AS has_admin_api, CASE WHEN rl.api_key_id IS NULL THEN TRUE ELSE FALSE END AS no_scope_recorded FROM api_keys ak LEFT JOIN ( SELECT api_key_id, MAX(CASE WHEN limit_scope = 'admin_api' THEN 1 ELSE 0 END) AS has_admin_api FROM rate_limits WHERE api_key_id IS NOT NULL GROUP BY api_key_id ) rl ON rl.api_key_id = ak.id WHERE ak.status = 'active' ) sub GROUP BY bucket, bucket_order ORDER BY bucket_order NULLS LAST
The agents' own records, as the role play wrote them; only the personal data of the person who ran it is redacted
Report request from Noah Fischer (analyst for the Chief Information Security Officer):
Title: Is admin_api scope concentrated in old keys, or standard across all ages?
Question: Are broadly-scoped (admin_api) keys specifically a feature of our oldest, longest-unrotated keys, or is that scope just how every integration key is set up regardless of age — i.e. is 'old + broad' a rotation failure or simply the norm?
Grain: one row per active-key age bucket (the same 5 buckets used previously: under 90 days, 90 days-1 year, 1-2 years, 2-4 years, 4+ years)
Columns:
- age bucket
- total active keys in bucket
- of those, keys with rate_limits.limit_scope = 'admin_api'
- share admin_api-scoped (%)
- of those, keys with no matching rate_limits row ('no scope recorded')
- distinct tenants represented
Details: Source: api_keys where status = 'active', bucketed by age (2026-01-05 minus created_at) into the same 5 fixed ranges as the earlier age-distribution report, left-joined to rate_limits on api_key_id (count distinct api_keys.id per bucket to avoid double-counting if a key has multiple rate_limits rows). Order rows by bucket from youngest to oldest in the fixed sequence (not alphabetical), so the trend is readable top to bottom.
Check your query with the warehouse's dry_run tool, then hand it in with submit.dry_runSELECT bucket AS age_bucket, COUNT(DISTINCT ak_id) AS total_active_keys, COUNT(DISTINCT CASE WHEN has_admin_api THEN ak_id END) AS admin_api_scoped_keys, ROUND(100.0 * COUNT(DISTINCT CASE WHEN has_admin_api THEN ak_id END) / NULLIF(COUNT(DISTINCT ak_id), 0), 1) AS admin_api_share_pct, COUNT(DISTINCT CASE WHEN no_scope_recorded THEN ak_id END) AS no_scope_recorded_keys, COUNT(DISTINCT tenant_id) AS distinct_tenants FROM ( SELECT ak.id AS ak_id, ak.tenant_id AS tenant_id, CASE WHEN (DATE '2026-01-05' - CAST(ak.created_at AS DATE)) < 90 THEN 'Under 90 days' WHEN (DATE '2026-01-05' - CAST(ak.created_at AS DATE)) < 365 THEN '90 days-1 year' WHEN (DATE '2026-01-05' - CAST(ak.created_at AS DATE)) < 730 THEN '1-2 years' WHEN (DATE '2026-01-05' - CAST(ak.created_at AS DATE)) < 1460 THEN '2-4 years' ELSE '4+ years' END AS bucket, CASE WHEN (DATE '2026-01-05' - CAST(ak.created_at AS DATE)) < 90 THEN 1 WHEN (DATE '2026-01-05' - CAST(ak.created_at AS DATE)) < 365 THEN 2 WHEN (DATE '2026-01-05' - CAST(ak.created_at AS DATE)) < 730 THEN 3 WHEN (DATE '2026-01-05' - CAST(ak.created_at AS DATE)) < 1460 THEN 4 ELSE 5 END AS bucket_order, CASE WHEN rl.has_admin_api = 1 THEN TRUE ELSE FALSE END AS has_admin_api, CASE WHEN rl.api_key_id IS NULL THEN TRUE ELSE FALSE END AS no_scope_recorded FROM api_keys ak LEFT JOIN ( SELECT api_key_id, MAX(CASE WHEN limit_scope = 'admin_api' THEN 1 ELSE 0 END) AS has_admin_api FROM rate_limits WHERE api_key_id IS NOT NULL GROUP BY api_key_id ) rl ON rl.api_key_id = ak.id WHERE ak.status = 'active' ) sub GROUP BY bucket, bucket_order ORDER BY bucket_order
STATUS: COMPILES (dry run on the empty schema, no data read) COLUMNS: age_bucket, total_active_keys, admin_api_scoped_keys, admin_api_share_pct, no_scope_recorded_keys, distinct_tenants
submitSELECT bucket AS age_bucket, COUNT(DISTINCT ak_id) AS total_active_keys, COUNT(DISTINCT CASE WHEN has_admin_api THEN ak_id END) AS admin_api_scoped_keys, ROUND(100.0 * COUNT(DISTINCT CASE WHEN has_admin_api THEN ak_id END) / NULLIF(COUNT(DISTINCT ak_id), 0), 1) AS admin_api_share_pct, COUNT(DISTINCT CASE WHEN no_scope_recorded THEN ak_id END) AS no_scope_recorded_keys, COUNT(DISTINCT tenant_id) AS distinct_tenants FROM ( SELECT ak.id AS ak_id, ak.tenant_id AS tenant_id, CASE WHEN (DATE '2026-01-05' - CAST(ak.created_at AS DATE)) < 90 THEN 'Under 90 days' WHEN (DATE '2026-01-05' - CAST(ak.created_at AS DATE)) < 365 THEN '90 days-1 year' WHEN (DATE '2026-01-05' - CAST(ak.created_at AS DATE)) < 730 THEN '1-2 years' WHEN (DATE '2026-01-05' - CAST(ak.created_at AS DATE)) < 1460 THEN '2-4 years' ELSE '4+ years' END AS bucket, CASE WHEN (DATE '2026-01-05' - CAST(ak.created_at AS DATE)) < 90 THEN 1 WHEN (DATE '2026-01-05' - CAST(ak.created_at AS DATE)) < 365 THEN 2 WHEN (DATE '2026-01-05' - CAST(ak.created_at AS DATE)) < 730 THEN 3 WHEN (DATE '2026-01-05' - CAST(ak.created_at AS DATE)) < 1460 THEN 4 ELSE 5 END AS bucket_order, CASE WHEN rl.has_admin_api = 1 THEN TRUE ELSE FALSE END AS has_admin_api, CASE WHEN rl.api_key_id IS NULL THEN TRUE ELSE FALSE END AS no_scope_recorded FROM api_keys ak LEFT JOIN ( SELECT api_key_id, MAX(CASE WHEN limit_scope = 'admin_api' THEN 1 ELSE 0 END) AS has_admin_api FROM rate_limits WHERE api_key_id IS NOT NULL GROUP BY api_key_id ) rl ON rl.api_key_id = ak.id WHERE ak.status = 'active' ) sub GROUP BY bucket, bucket_order ORDER BY bucket_order
Submitted to tonight's batch. Your work on this request is done.