◇ OpenCyber Academy
Engineering · Chapter 13 · English

Chapter 13 — Data systems

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 13 — Data systems

Lesson map

Lesson 13 published. Map, example, EX037.

flowchart TD
  N0["Learning outcomes"]
  N1["13.1 Identity data sources"]
  N2["13.2 Schema for sign-in events"]
  N3["13.3 Queries"]
  N4["13.4 Retention and access"]
  N5["Synthesis"]
  N0 --> N1 --> N2 --> N3 --> N4 --> N5
  N5 --> EX["Worked example"]
  EX --> Q["Exercise EX037"]

Logical drawing

flowchart LR
  N0["Learning outcomes"]
  N1["13.1 Identity data sources"]
  N2["13.2 Schema for sign-in events"]
  N3["13.3 Queries"]
  N4["13.4 Retention and access"]
  N5["Synthesis"]
  N0 --> N1 --> N2 --> N3 --> N4 --> N5 --> Q["EX037"]

[Learning outcomes]
    |
    v
[13.1 Identity data sources]
    |
    v
[13.2 Schema for sign-in events]
    |
    v
[13.3 Queries]
    |
    v
[13.4 Retention and access]
    |
    v
[Synthesis]
    |
    v
[Worked example] --> [Exercise EX037]

Learning outcomes

You will (1) design a store for identity and audit data; (2) define a schema for sign-in events; (3) plan queries and retention.

13.1 Identity data sources

Identity data comes from several systems: the HR record of staff and roles, the directory of accounts and groups, the identity provider's sign-in logs, application access logs and the service desk's ticket system. Each answers different questions. HR says who should exist, the directory says who does, sign-in logs show who is active and tickets show who needed help. Sana lists each source with its owner, update frequency and clock, because combining them only works when identities and timestamps can be matched reliably.

13.2 Schema for sign-in events

Sana defines a schema for sign-in events: time in UTC, user identifier, application, authentication methods used, result, client device, network location and a correlation identifier linking related events. Agreeing the schema before collection allows events from different systems to be stored and queried together. Without it, one system's user field might hold an email address and another's an internal number, so queries silently miss matches. The schema is documented with examples and versioned as it changes. Fields that some sources cannot supply are marked as optional.

13.3 Queries

Good queries answer operational questions: how many sign-ins failed for each user yesterday, which accounts have not signed in for ninety days, and which administrators signed in from new devices this week. Sana writes each query with an explicit time window and filters, checks its result against a small sample read by hand, and saves it with a description. During an incident there is no time to write and debug new queries, so a tested library of common queries is part of being prepared.

13.4 Retention and access

Sign-in logs are personal data and also evidence. Sana sets retention by purpose: detailed events for a year to support investigations, summaries for longer for trend reporting. Access to the logs is restricted to named roles and logged in turn, because logs reveal staff working patterns. Deletion at the end of the period is automated so the rule is actually applied. She records these decisions in the data protection documentation, linking each to the policy that justifies it. Requests to extend retention need documented approval.

Worked example

Sana's sign-in schema has eight fields, a tested query finds 61 synthetic accounts inactive for over ninety days, and retention is one year detailed, three years summarised.

Synthetic case. Sources, counts and periods are synthetic.

Errors and limits

Combining sources whose user identifiers or clocks differ produces queries that silently miss matches.

Exercises

Synthesis

A shared schema, tested queries and purposeful retention turn identity logs into reliable evidence.

Continue studying — chapter 13 of 50

← Chapter 12 (draft) · Course chapter index · Chapter 14 (draft) →