◇ OpenCyber Academy
Bachelor's · Chapter 9 · English

Chapter 9 — Data and databases

Open, free learning for anyone who wants to protect people and technology. No account is required to read this text or its worked answers.

Chapter 9 — Data and databases

Lesson map

Lesson 09 published. Map, example, EX025.

flowchart TD
  N0["Learning outcomes"]
  N1["9.1 Minimal schema"]
  N2["9.2 Access control"]
  N3["9.3 Retention"]
  N4["9.4 Backups"]
  N5["9.5 Query hygiene"]
  N6["9.6 Integrity"]
  N7["9.7 Access review against HR records"]
  N0 --> N1 --> N2 --> N3 --> N4 --> N5 --> N6 --> N7
  N7 --> EX["Worked example"]
  EX --> Q["Exercise EX025"]

Logical drawing

flowchart LR
  N0["Learning outcomes"]
  N1["9.1 Minimal schema"]
  N2["9.2 Access control"]
  N3["9.3 Retention"]
  N4["9.4 Backups"]
  N5["9.5 Query hygiene"]
  N6["9.6 Integrity"]
  N7["9.7 Access review against HR records"]
  N0 --> N1 --> N2 --> N3 --> N4 --> N5 --> N6 --> N7 --> Q["EX025"]

[Learning outcomes]
    |
    v
[9.1 Minimal schema]
    |
    v
[9.2 Access control]
    |
    v
[9.3 Retention]
    |
    v
[9.4 Backups]
    |
    v
[9.5 Query hygiene]
    |
    v
[9.6 Integrity]
    |
    v
[9.7 Access review against HR records]
    |
    v
[Worked example] --> [Exercise EX025]

Learning outcomes

You will (1) design minimal schemas for findings; (2) protect customer data in evidence DBs; (3) retain and delete on schedule.

9.1 Minimal schema

Store what retests need, not entire customer tables.

A finding record at TRB needs only enough to reproduce and verify the weakness: the affected asset identifier, the request or configuration that demonstrated it, a timestamp, the tester, the engagement id and a pointer to the evidence file. When Sam proved that a statement-download endpoint returned another customer's PDF, the record kept the two synthetic account ids involved and a hash of the response, not the statement itself. A retest next quarter repeats the same request and compares the outcome. Every extra column copied from a customer table becomes data the testing team must now protect, retain and eventually delete.

9.2 Access control

Evidence databases use role-based access; Sam's intern role is read/write on engagement schemas only.

Roles are granted per engagement, not per person, so the permission disappears when the engagement closes rather than when someone remembers to revoke it. FindingsDesk has three: tester (write findings and evidence for one engagement), reviewer (read across the engagements they sign off) and custodian (run retention and restore jobs, never edit findings). Separating the custodian from the tester means the person who deletes evidence on schedule is not the person whose work it documents. When a tester moves to a new engagement, their old role expires and the reviewer keeps read access until the report is accepted.

9.3 Retention

Agreement-driven deletion jobs; "keep forever" is not a policy.

The statement of work fixes a retention period for raw evidence, commonly ninety days after the final report, and a longer one for the report and remediation status. FindingsDesk stores that date on the engagement row the day the agreement is signed, and a nightly job deletes evidence blobs whose date has passed, writing a deletion receipt with counts and hashes. Retention also protects the tester: evidence kept past its agreed date is customer data held without a reason, and an auditor will ask why. If a client requests a legal hold, the hold is a dated field with an owner, not an informal promise.

9.4 Backups

Encrypted, access-logged, tested restores — backups are in scope for abuse review.

A backup is a second copy of every screenshot, request log and credential-shaped string the team ever captured, so it attracts the same threats as the live database and is often watched less carefully. TRB's evidence backups are encrypted with a key held by the custodian role, stored in a separate account, and every read is logged. Retention applies to backups too: a deletion job that cleans the live store but leaves ninety-day-old snapshots intact has not deleted anything. Sam's quarterly review compares the oldest snapshot date with the shortest retention period on any active engagement.

9.5 Query hygiene

No SELECT * into laptops; export minimised CSVs.

Analysts usually want a count, a trend or a single example, and the query should return exactly that. Pulling whole tables to a laptop moves evidence outside the controls that protect it: no access log, no retention job and a disk that may travel. FindingsDesk therefore exposes saved queries such as open findings by severity, or days to fix per business unit, that return aggregates and redacted columns. When someone needs a raw row, they open it inside the evidence viewer, where the read is logged. Sam rewrote a popular dashboard query that selected every column and cut the export from forty fields to six.

9.6 Integrity

Foreign keys from findings to evidence blobs.

A finding that points to a missing or altered file cannot be defended when the client disputes it. Each evidence blob in FindingsDesk is stored with a SHA-256 hash computed at upload, and the finding row references the blob by id with a foreign-key constraint, so the database refuses to delete evidence that a live finding still cites. When a report goes out, the export lists each finding with the hashes of its evidence. If TRB later asks whether a screenshot was edited, Sam recomputes the hash and compares. A mismatch is itself an incident to investigate, not a reason to re-upload quietly.

9.7 Access review against HR records

Evidence DB roles are reviewed quarterly against HR lists. Sam's query checks for active vault roles without active employees. The first run found two contractors; access was removed the same afternoon.

The join is simple: roles with no matching active record in the HR export are orphaned. The interesting cases are near-misses. A tester on parental leave is inactive in HR but should keep a dormant account, and a contractor renewed under a new purchase order may appear under a different id. Sam's query therefore produces a review list rather than revoking access automatically, and the security lead signs each decision. The second quarter's run found zero orphans, which is the result the control exists to produce.

9.8 Export watermarks

CSV exports embed engagement id and exporter name in a header comment. Leaked files can be traced. Sam verifies the watermark exists in a quarterly spot check.

A watermark does not stop a leak; it shortens the investigation that follows one. When a file turns up on a shared drive, the header names the engagement and the person who exported it, so the custodian can check whether that export was authorised and which retention date applies. Header comments are easy to strip, so the export log in FindingsDesk records the same fields together with a hash of the file. Matching a stray file's hash against that log identifies its origin even when someone has deleted the comment line.

9.9 Backup restore game

Quarterly, Sam restores an evidence backup to an isolated host and opens one file. Untested backups are assumed broken.

The exercise has a pass condition written in advance: restore within two hours, open a randomly chosen evidence file, recompute its hash and match it to the finding record. The isolated host has no network route to production and is wiped afterwards, because a restored backup is full of sensitive evidence. Timing matters as much as success. If TRB disputes a finding during a report review, the team must produce the original evidence that week, not after a day of searching for the decryption key. Each game ends with a short note recording duration, problems and fixes.

9.10 Refusing a bulk export

An analyst requests "all evidence for the year" for a thesis. Sam refuses bulk export and offers a statistics-only extract with counts by severity. The thesis still proceeds. Minimisation applies to friendly requests too.

The refusal works because Sam asked what question the thesis was trying to answer. The analyst wanted to show how long high-severity web findings stayed open, which needs dates and severities, not screenshots or account ids. A query grouped by quarter and severity answered that, and the result contained no customer data at all. Purpose comes first: a request framed as all evidence usually hides a narrower need, and naming that need turns a privacy conflict into a reporting task.

9.11 Restore without a manifest

During restore game day the backup opened, but the hash manifest was missing. Sam failed the game and restored the manifest job before the next engagement. Backups without manifests are rumours.

The file opened, so a less careful reviewer would have passed the game. Without the manifest, though, nobody could prove the restored file matched what was captured during testing, and that proof is what the evidence is for. The cause was mundane: a storage migration had dropped the scheduled job that writes manifests beside each snapshot. Sam added a check that fails the backup itself when no manifest is present, so the gap now surfaces the night it occurs instead of on the next quarterly game.

9.12 A contact address in every export

Watermark headers include a contact mailto for lost-and-found of stray exports. Someone used it once; Sam bought them coffee.

The address gives an honest finder a way to report a stray export instead of guessing who owns it. It goes to a shared custodian mailbox, not to an individual, so it still works after staff change. Reports are handled like a small incident. The custodian confirms the file's origin from the export log, asks the finder to delete their copy or hand it back, and records whether its retention date had already passed. Thanking the reporter publicly makes it more likely that the next stray file is reported too.

9.13 A returned drive

Lost-and-found mailto once returned a USB drive labelled with an engagement id. Process works.

The drive also showed where the process had failed earlier: someone had copied evidence to removable media despite the export rules. The custodian did not simply wipe the drive. They checked its contents against the export log, found an unlogged copy of screenshots from an engagement that had closed months before, and traced it to a tester who had wanted to work offline on a train. The response was a short conversation, deletion with a receipt, and a new option in FindingsDesk for encrypted offline workspaces that expire on the retention date.

Worked example (synthetic TRB)

Sam's export job redacts account numbers to last four digits before analysts download CSV.

Synthetic case — Riverstone Transit Bank (TRB). The bank, Sam Ito, TrackScope/FindingsDesk/PulseMap and all figures are invented for teaching.

Errors and limits

This book grants no authority to test real banks or payment systems. Labs use documentation-range addresses and synthetic data only; exploit code is never provided.

Exercises

Synthesis

Databases hold the least personal data needed, with retention and roles enforced.

Continue studying — chapter 09 of 40

← Chapter 08 (draft) · Course chapter index · Chapter 10 (draft) →