◇ OpenCyber Academy
Technologist · Chapter 4 · English

Chapter 4 — 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 4 — Data models

Lesson map

Lesson 04 published. Map, example, EX007.

flowchart TD
  N0["Learning outcomes"]
  N1["4.1 An inventory schema"]
  N2["4.2 Provenance and freshness"]
  N3["4.3 Lifetimes"]
  N4["4.5 Practical activity"]
  N5["4.7 Keeping the model current"]
  N6["Synthesis"]
  N0 --> N1 --> N2 --> N3 --> N4 --> N5 --> N6
  N6 --> EX["Worked example"]
  EX --> Q["Exercise EX007"]

Logical drawing

flowchart LR
  N0["Learning outcomes"]
  N1["4.1 An inventory schema"]
  N2["4.2 Provenance and freshness"]
  N3["4.3 Lifetimes"]
  N4["4.5 Practical activity"]
  N5["4.7 Keeping the model current"]
  N6["Synthesis"]
  N0 --> N1 --> N2 --> N3 --> N4 --> N5 --> N6 --> Q["EX007"]

[Learning outcomes]
    |
    v
[4.1 An inventory schema]
    |
    v
[4.2 Provenance and freshness]
    |
    v
[4.3 Lifetimes]
    |
    v
[4.5 Practical activity]
    |
    v
[4.7 Keeping the model current]
    |
    v
[Synthesis]
    |
    v
[Worked example] --> [Exercise EX007]

Learning outcomes

You will (1) model services, endpoints, certificates and algorithms as related records; (2) design an inventory schema with provenance; (3) attach data-lifetime attributes; and (4) query the model for migration candidates.

4.1 An inventory schema

A practical schema has tables for service (name, owner, tier), endpoint (host, port, protocol), crypto_use (endpoint, function, algorithm, parameter, library, version), observation (crypto_use, method, timestamp, observer) and data_class (name, confidentiality_years) linked to services. Separating observation from use lets you record that the same use was seen by a scanner on Monday and confirmed in configuration on Wednesday.

KLS adds two practical columns: change_mechanism on crypto_use, holding configuration, package, vendor release or hardware, and controller on endpoint. With them, one query lists every classical key exchange KLS can fix by configuration this month.

4.2 Provenance and freshness

Every row needs a method (passive capture, configuration, code scan, vendor statement) and a timestamp. Stale observations are downgraded to "unverified" rather than deleted. Without provenance, a dashboard mixes facts with guesses and nobody knows which.

KLS uses three grades: confirmed (two independent methods agree), observed (one method within 30 days) and unverified (older or single vendor claim). The 71 % dashboard in Chapter 9 went wrong partly because scanner-era rows with no method label were counted as fresh. The grade is computed from the observation table, never typed by hand, so it cannot drift from the evidence.

4.3 Lifetimes

Attach confidentiality_years to data classes. KLS's shipment records must stay confidential for ten years by contract; billing data seven; telemetry one. A query joining lifetimes to key-exchange algorithms produces the harvest-now-decrypt-later candidate list automatically.

Contract text beats retention schedules: KLS's billing data is retained seven years for tax, but one freight customer's contract asks for ten-year confidentiality of rates, so that customer's billing rows inherit ten. The lifetime column therefore lives on the data class and may be overridden per customer, and the query takes the maximum lifetime of any class a flow carries.

4.4 Worked example

import sqlite3
db = sqlite3.connect(":memory:")
db.executescript("""
CREATE TABLE use(service TEXT, function TEXT, algorithm TEXT, data_years INT);
INSERT INTO use VALUES ('api-gateway','kex','X25519MLKEM768',10),('partner-sftp','kex','ecdh-sha2-nistp256',10),
                       ('billing-db','kex','ECDHE-P256',7),('telemetry','kex','ECDHE-P256',1);""")
rows = db.execute("SELECT service FROM use WHERE function='kex' AND algorithm NOT LIKE '%MLKEM%' AND data_years>=5").fetchall()
print("HNDL candidates:", [r[0] for r in rows])

The query returns partner SFTP and the billing database; telemetry drops out because its data is short-lived, and the gateway is already hybrid.

The query encodes three decisions worth reading aloud. The function='kex' condition limits it to harvest-now-decrypt-later exposure, because signatures follow a different timetable. The NOT LIKE '%MLKEM%' test treats any hybrid group as acceptable. The data_years>=5 threshold is KLS policy, not a standard; at three years the billing database would still be listed, and telemetry would still not be. Rerun after every inventory feed, the list becomes a live work queue for the platform team.

4.5 Practical activity

Extend the example with an observation table and a query listing uses whose newest observation is older than 90 days.

Give the observation table a foreign key to use, a method column and an observed_at timestamp stored as ISO text. Insert two observations for the gateway, one 10 days old and one 200 days old, and one 120-day-old observation for partner SFTP. The correct query groups by use and compares the maximum timestamp with the cut-off, so the gateway is fresh because its newest row is recent. A query that compares every row would wrongly list the gateway; include that mistake as a negative test.

4.6 Common errors and limits

At KLS the flat spreadsheet had a column "algorithm" with values typed by four people; nobody could say whether "ECDHE" for billing came from a scan, a config file or memory. Deleting stale rows removed the forklift telemetry entirely, which hid that nobody knew its cryptography. And the first lifetime list copied the seven-year tax retention for billing, missing the ten-year contract clause. Each error makes the migration list shorter than it should be, which feels like progress but is not.

4.7 Keeping the model current

An inventory model decays the moment it is populated. Feed it automatically: nightly configuration exports from the load balancers, certificate lists from the CA database, image manifests from the registry, and handshake summaries from logs. Each feed writes observations with its own method label, so the model always shows what was seen, how and when. Hand-entered rows should be the exception and should carry the name of the person who entered them. When two sources disagree — configuration says hybrid is enabled, logs show no hybrid negotiations — record both and raise a task; the disagreement is usually the most useful fact in the model.

Access to the model also matters. A complete map of which services still use classical key exchange is useful to an attacker, so restrict write access to the feeds and read access to the teams who need it, and publish only aggregates outside the platform group.

Exercises

Synthesis

A relational inventory with provenance and lifetimes turns migration planning into queries that can be rerun whenever the estate changes.

Continue studying — chapter 04 of 30

← Chapter 03 (draft) · Course chapter index · Chapter 05 (draft) →