◇ OpenCyber Academy
Engineering · Chapter 22 · English

Chapter 22 — Authorized offensive security and red team: system and data models

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 22 — Authorized offensive security and red team: system and data models

Lesson map

Lesson 22 published. Map, example, EX064.

flowchart TD
  N0["Learning outcomes"]
  N1["22.1 Entities"]
  N2["22.2 Keys"]
  N3["22.3 Forbidden attributes"]
  N4["22.2b Time and actors"]
  N5["22.3b Soft delete vs purge"]
  N6["Synthesis"]
  N0 --> N1 --> N2 --> N3 --> N4 --> N5 --> N6
  N6 --> EX["Worked example"]
  EX --> Q["Exercise EX064"]

Logical drawing

flowchart LR
  N0["Learning outcomes"]
  N1["22.1 Entities"]
  N2["22.2 Keys"]
  N3["22.3 Forbidden attributes"]
  N4["22.2b Time and actors"]
  N5["22.3b Soft delete vs purge"]
  N6["Synthesis"]
  N0 --> N1 --> N2 --> N3 --> N4 --> N5 --> N6 --> Q["EX064"]

[Learning outcomes]
    |
    v
[22.1 Entities]
    |
    v
[22.2 Keys]
    |
    v
[22.3 Forbidden attributes]
    |
    v
[22.2b Time and actors]
    |
    v
[22.3b Soft delete vs purge]
    |
    v
[Synthesis]
    |
    v
[Worked example] --> [Exercise EX064]

Learning outcomes

You will (1) draw an entity model for engagements, scopes and findings; (2) choose keys and cardinality; (3) keep exploit content out of the model.

22.1 Entities

Engagement 1—* ScopeVersion; Engagement 1—* Finding; Finding — EvidenceObject (by hash); TechniqueTag — Finding. Cardinality mistakes (many scopes active without a version pointer) cause auditors to ask "which ROE was live?"

A version pointer on every finding answers which ROE was live without a scavenger hunt through mail. Many-to-many evidence links let one hash support several tickets when scanners overlap. Sam rejects schemas that allow two active ScopeVersions without naming which one TrackScope loads. In a dry-run dossier review, an auditor asked which ROE governed a finding filed on Tuesday after a Monday widen; the version pointer answered in one lookup instead of a mail thread.

22.2 Keys

Finding ids are ULID/UUID. Evidence is content-addressed by SHA-256. ScopeVersion has (engagement_id, version_int). Never key findings by title — titles change.

ULIDs sort by time and stay unique across services. Content-addressed evidence means renaming a file cannot fork history. Titles are labels for humans, never joins. Sam's migration guide forbids title-based foreign keys even in temporary scripts, because a title rename during editing must not orphan evidence links. A studio import that joined on title once duplicated the default-credential finding on web-02 after someone edited the title for clarity; the ULID join would have kept a single ticket.

22.3 Forbidden attributes

No attribute for payload, password or shell command. EvidenceObject stores bytes in a sealed bucket; the model stores only hash, media type and retention class.

Synthetic case — Riverstone Transit. An early schema had notes as free text; people pasted passwords. Sam replaced it with structured fields plus an evidence hash, and a linter rejects password-like strings in titles.

Schema validation is the last line when a tired engineer invents a custom field. Sealed bytes plus hash keep recipes out of the ticket body that gets mailed around. Retention class drives purge without ad-hoc deletes. Sam's CI fails if a migration adds a banned column name.

22.4 Errors and limits

Models that try to encode all of ATT&CK as mandatory fields force junk tags. Keep technique tags optional and reviewed.

Junk tags then pollute the coverage charts, making techniques look tested when they were only labelled. Sam's weekly tag review merges synonyms and drops tags nobody can define, and a tag without a one-line definition cannot be applied to a new finding. Tag definitions live in the same repo as PulseMap queries, so a deleted meaning cannot linger in charts.

22.2b Time and actors

Every mutable entity needs created_at, created_by, and for state changes updated_at/updated_by or an append-only event. Wall-clock alone is not an audit trail. Prefer UTC with an explicit offset in exports so shift handovers across time zones do not reorder history.

Append-only events survive disputes about who changed severity. UTC with explicit offsets stops a London and a New York handoff from looking reversed. Wall-clock screenshots without actor ids are not enough for an audit. Sam's export job refuses rows that lack created_by.

22.3b Soft delete vs purge

Findings are rarely hard-deleted during retention; they soft-delete with an actor and reason. Purge jobs honour retention class and emit an audit event. A silent DELETE FROM findings is a repudiation gift to anyone challenging the dossier later.

Soft delete keeps the story for the retention window; purge is a deliberate, logged act. Silent SQL deletes are how dossiers lose findings that counsel later needs. Actor and reason on soft delete answer who hid a ticket and why. Sam alerts when a purge job runs outside its schedule.

22.1b Attachment size limits

Evidence objects need size caps and media-type allow-lists. Unbounded uploads become availability attacks on the store and tempt dumping of bulk personal data. Large artefacts stay in the sealed bucket with streaming hash computation.

Caps stop a bulk dump of personal data disguised as evidence. Media-type allow-lists block surprise executables in the bucket. Streaming hashes handle large artefacts without loading them into FindingsDesk memory. Sam posts the size cap in the tester guide so people do not discover it at upload time.

Exercises

Synthesis

Model engagements, versions, findings and hashes — not attacks.

Continue studying — chapter 22 of 50

← Chapter 21 (draft) · Course chapter index · Chapter 23 (draft) →