Where session 5 sits on the ladder
MFA, key rotation, a private bucket and pinned dependencies have closed six of Ironwood's nine paths. The three left are the insider's own analyst login, that login lifted to admin by a Friday grant that was never revoked, and the support agent's legitimate session. Tonight's two controls, query logging on the crown-jewel table and egress alerting on external stages, leave the path count at three and take the undetected count from three to zero. Nothing closes; everything becomes visible.
★ What you walk out with tonight. Two SQL detections over your own query history: an unusual read of the crown jewels, and any copy of them to an external location that is not on an allowlist. A per-role baseline so that "unusual" means something. A review table and a scheduled job that fills it. And the "seen by" column of your inventory file filled in for the two edges that carry a log.
LiveWhy detection is not the consolation prize3 min▶
Prevention removes a door. Detection puts a camera on the doors that have to stay open. On Ironwood's graph the finished design still has one open path, the support agent reading customer records to answer tickets, and the query log is the only defence that path will ever have. A programme with strong prevention and no detection has closed every door it could and left the one that remains in the dark.
The other reason is time. Most breaches of a data estate are discovered by someone outside the company: a researcher, a customer, a buyer on a forum. The gap between the first read and that phone call is measured in months. A detection that fires the morning after the read turns months into a day, and a day is a containable incident.
Ironwood's platform team had query history switched on for two years. Nobody had ever queried it. When an analyst's account was used to read the full customers table at 02:40 on a Sunday, the record was written, retained, and read by nobody, and the incident surfaced eleven weeks later when the data appeared for sale. The log had done its job. There was no detection on top of it and no human at the end.
Self-studyThe vocabulary for tonight3 min▶
- Query history - the warehouse's own record of every statement executed: who, with which role, what text, against what, when, from which client, returning how many rows. Every major platform ships one under a different name.
- Egress - data moving out of the warehouse's boundary: a COPY INTO an external stage, an unload to a bucket, a share to another account. The record lives in the same history but the event is different in kind, because after it the data no longer needs the warehouse.
- Baseline - what normal looks like for one role over a trailing window, usually 30 days. Rows per query, hours of the day, tables touched.
- Detection - a query over the log that compares today against the baseline and writes a row when the difference crosses a threshold.
- Review table - where detections write. One row per flag, with columns for who looked and what they concluded. The table is the control; the alert is only its doorbell.
- Allowlist - the short table of external locations the business has approved for copies of customer data. Anything not on it is a flag, not a block.
Data leaves through a query or through a copy
9 min liveOn Ironwood's graph there are exactly two edges a log can see. The edge from the warehouse to the customers table is a SELECT, and query history records it. The edge from the customers table to an external endpoint is a COPY INTO an external stage or an unload to a bucket, and egress alerting records it. Every path through the warehouse crosses the first. The export bucket path crosses neither.
LiveThe two edges, and what each log is3 min▶
- Warehouse to customers is a SELECT. Whoever arrived at the warehouse, by whichever credential, has to issue a statement against the table to get the rows, and the warehouse writes that statement to its query history. This one edge is why query logging sees every path that passes through the warehouse, all eight of the original nine.
- Customers to an external endpoint is a copy out: a COPY INTO an external stage, an unload to an object bucket, a share to another account. It is also a statement, so it also lands in query history, but it deserves its own detection because after it runs the data no longer needs the warehouse. Egress alerting is the practice of watching that class of statement specifically, and comparing the destination against a list the business has approved.
- The export bucket path crosses neither. The nightly extract was written by a job long ago; reading the bucket is a request to the object store, not a query to the warehouse, and it goes to no external stage. Session 3 closed that door with a private bucket. The object store's own access log is the only thing that would ever have seen it, and it needs its own row in the inventory.
LivePaths stay at three; undetected goes to zero3 min▶
Switching on query logging and egress alerting changes nothing about how many ways in exist. On the bench the path count reads three before and three after. What changes is the second number: undetected paths go from three to zero, because each of the three remaining paths ends in a SELECT against customers, and that SELECT is now written down and reviewed.
The finished design, after least privilege in the access-control course removes the Friday admin grant and the broad analyst SELECT, has one path left: the support agent's legitimate read. It is logged. These two detections are the only defence that path will ever have, and that is not a gap, it is the reason detection is on the ladder at all.
A control that moves no headline number is not a control that did nothing. A report that lists only open paths would show tonight's work as a flat line. The second and third numbers on the bench exist so that logging and backups can be seen to matter.
Self-studyWhat a query history genuinely records, and what it cannot4 min▶
Every warehouse ships a query history under some name: a system view, an account-usage schema, an audit log export. The column names differ; the content is remarkably consistent.
| Recorded | What it lets a detection ask |
|---|---|
| User and role | Which identity asked, and with which set of grants. Baselines are built per role, not per user. |
| Statement text | What was asked. Whether it was a SELECT, a COPY, an unload; which tables it names. |
| Objects accessed | Which tables and columns were actually read, on platforms that record it separately from the text. More reliable than searching the text. |
| Timestamp | When. Hour of day per role is one of the three signals tonight. |
| Client application and address | From where and with what tool. A BI tool at 10:00 and a command-line client at 02:40 are different events. |
| Rows returned, bytes scanned | How much. The clearest single signal for a bulk read. |
What it cannot record is intent. The history shows that the support role read 400,000 rows at 02:40. It does not show whether that was a migration script somebody forgot to mention, a new report, or the beginning of an incident. Every detection you write tonight ends in a question to a named human for exactly this reason.
A detection is a query over the log with a threshold and a human
8 min liveA log that nobody queries is storage. A detection is three things on top of it: a query that compares today against a baseline, a threshold that decides what is worth a row, and a named person who reads the rows. Leave out any one and you have Ironwood's two years of history that nobody opened.
LiveDetection one: an unusual read of the crown jewels3 min▶
Three signals, each compared against the role's own last 30 days, any one of which writes a row:
- A role that has not read customers in 30 days reads it. New roles, dormant roles, and roles that were never meant to touch the table. At Ironwood the Friday admin grant shows up here the first time the analyst uses it against customers.
- A read far larger than that role's typical rows per query. Ten times the role's median is a defensible starting threshold. A support agent answers one ticket at a time; a 400,000-row read is not a ticket.
- A read at an hour that role never works. Per role, not globally. The ETL role runs at 03:00 every night and that is normal; the support role at 03:00 is not.
The ten-times and 30-day figures are starting points, chosen so the first week produces a handful of rows rather than hundreds. They get tuned, in the direction of fewer rows, until the table is something a person actually reads.
LiveDetection two: a copy of customers to somewhere not on the allowlist2 min▶
Any COPY INTO an external stage, unload to a bucket or share of the customers table whose destination is not in a short table of approved locations. This one has no baseline, because there is no normal amount of copying customer data to unknown places. The allowlist is the baseline.
It is a flag, not a block. Blocking egress is a network and platform decision with its own failure modes; the detection's job is to make sure a copy to a new destination is a row somebody reads that morning, whether or not it was also blocked.
Ironwood's finance team had a legitimate weekly unload of an aggregated customer view to a reporting bucket. The first version of the detection flagged it every Monday. The fix was not a lower threshold; it was adding the finance bucket to the allowlist with an owner and a review date, so the detection was quiet on Monday and loud on the Thursday a different bucket appeared.
Self-studyThe false-positive discipline, borrowed from data observability4 min▶
Data observability learned this the hard way: a freshness monitor that fires on every late load is switched off within a month, and the month it is needed it is off. The same law governs security detection with sharper consequences, because the person who stops reading the review table is the person the whole design depends on.
- A detection that pages six times a week is a detection nobody reads. After the third false alarm the reader stops opening the row. Measure rows per week from the first day, and treat more than a few as a defect in the detection, not a fact about the estate.
- Baseline per role, never globally. A global "more than 100,000 rows" threshold is silent on the support role reading 90,000 and loud on the ETL role doing its nightly job. Every role has its own normal, and the baseline query is grouped by role for that reason.
- Tune towards fewer rows, and record why. Every allowlist entry and every raised threshold is a decision with an owner and a date. An allowlist without owners becomes the next public bucket.
- The response is a question, not an accusation. The log sees the read and not the reason. "Your role read 400,000 customer rows at 02:40 on Sunday; was that you, and what was it for?" sent to the role's named owner resolves most rows in a sentence and is the correct first move on all of them. The one that does not resolve is the incident.
Two detections on the query log
★ 20 min · everyone buildsAll of this runs against your own warehouse's query history, read-only until the last two steps, which create a table and a scheduled job in a schema you own. The view is written here as query_history with generic column names; the exact view, column names and the percentile function differ by platform, so substitute yours. Where your platform records objects accessed separately from the statement text, use that instead of searching the text: it is more reliable and it catches reads through views.
List every role that read customers in the last 30 days, with row counts. This is the baseline in its rawest form and usually the first surprise: roles nobody expected to see on the list.
Compute each role's typical rows per query and flag reads above ten times it. The median is the right "typical"; a mean is dragged by exactly the reads you are looking for. The floor of 100 stops a role whose median is 3 rows from flagging a 40-row read. Roles absent from the baseline entirely are flagged by the left join.
Flag reads of customers outside each role's usual hours. An hour the role has never read the table in during the baseline window is unusual for that role, whatever it is for another.
List every COPY or unload of customers to an external location and compare against an allowlist. Create the allowlist first, with an owner and a review date on every row. The detection then returns copies whose statement names no approved prefix.
Wire both detections into a scheduled job that writes to a review table. The table has columns for who reviewed the row and what they concluded; those two columns are the control. The scheduler object is a task, a job or a DAG depending on your platform. Run it once an hour or once a night; the reader's cadence matters more than the job's.
Update the inventory file's "seen by" column for the two edges. Name the log and the detection, not just "logging". The bench counts a path as seen when it crosses a logged edge; your file should say which query does the seeing.
Read the review table yourself for the first two weeks. The first fortnight tells you which roles need an allowlist entry and which thresholds are producing noise. Hand it to the named owner only once a week's rows fit on one screen. A detection handed over while it is still loud is a detection that will be muted.
Before session 6
30 min- Run step 1 against your own warehouse and write down every role on the list you did not expect. Each one is either an allowlist conversation or a least-privilege ticket for the access-control work.
- Run steps 2 and 3 over the last seven days, not just yesterday, and count the rows. If it is more than a handful, tune before you schedule anything. Record every tuning decision with a reason.
- Fill the allowlist with every legitimate external destination for customer data you can find, each with an owner. Then ask the owner. At least one will have left the company.
- Find the log that would see your export bucket, share or replica being read. If there is none, that is the finding, and it goes in the inventory as a blank "seen by" cell rather than being left unrecorded.
Three questions
Ironwood's export bucket held a nightly extract of the customers table. Why did neither query logging nor egress alerting ever see it being read?
The extract was written long ago by a job. Reading it later never touches the warehouse, so the two warehouse-side logs have nothing to record. Only the object store's own access log could see it, and it needs its own inventory row.
Query logging and egress alerting leave Ironwood's path count at three. What do they change on the bench?
Detection does not remove a path. It makes using one visible. The finished design still has one path, the support agent's legitimate read, and these two logs are its only defence.
The unusual-read detection flags the finance role's weekly unload every Monday. What is the right fix?
A global threshold raised for one role goes blind for every role. The allowlist records a decision with an owner; the detection keeps firing on the day a different destination appears.