Chapter 9 — Data and databases
Lesson map
Lesson 09 published. Map, example, EX025.
flowchart TD
N0["Learning outcomes"]
N1["9.1 Data classes"]
N2["9.2 Test data"]
N3["9.3 Access"]
N4["9.4 Encryption"]
N5["9.5 Retention"]
N6["Lab — Lab — retention check"]
N7["9.6 Lab result"]
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 Data classes"]
N2["9.2 Test data"]
N3["9.3 Access"]
N4["9.4 Encryption"]
N5["9.5 Retention"]
N6["Lab — Lab — retention check"]
N7["9.6 Lab result"]
N0 --> N1 --> N2 --> N3 --> N4 --> N5 --> N6 --> N7 --> Q["EX025"]
[Learning outcomes]
|
v
[9.1 Data classes]
|
v
[9.2 Test data]
|
v
[9.3 Access]
|
v
[9.4 Encryption]
|
v
[9.5 Retention]
|
v
[Lab — Lab — retention check]
|
v
[9.6 Lab result]
|
v
[Worked example] --> [Exercise EX025]
Learning outcomes
You will (1) classify StudyHub's data; (2) apply access, encryption and retention; (3) separate test and production data; (4) check retention with a lab.
9.1 Data classes
Student records (personal, high), coursework uploads (personal, medium), logs (may contain personal data), test data (should be synthetic). Classification guides controls.
Logs are the class most often overlooked. StudyHub's request logs once recorded full student IDs in URLs, which made ordinary application logs personal data. Classifying them correctly meant applying access controls and retention limits that had not been considered before. Samuel's data register lists each store with its class, owner and the controls that class requires. The register is reviewed each term.
9.2 Test data
Test databases should never hold real student data. The opening case's test database held synthetic records only, which limited the impact; Samuel confirms with a check against known synthetic markers.
His check looks for marker values that the synthetic data generator always includes, such as student IDs in a reserved range and names from a fixed list. Every record in the test database matched. If any record had lacked the markers, that would have suggested real data had been copied in, and the incident's impact would have been very different.
9.3 Access
Applications use database roles with only the needed permissions; administrators use separate, audited accounts.
In StudyHub, records-api's database role can read and update student records but cannot drop tables or create users. Administrators who need those powers use separate accounts whose sessions are logged. If the application's credentials leak, as nearly happened in the opening case, the damage is limited to what that narrow role allows. Each role's permissions are written down beside the service that uses it, so a reviewer can compare what the service needs with what it has and spot any permission that has crept in without a reason.
9.4 Encryption
Managed databases and storage encrypt at rest; connections require TLS.
Encryption at rest protects against lost disks or backups but not against someone who connects with valid credentials, since the database decrypts data for any authorised connection. That is why the leaked connection string mattered despite encryption. Requiring TLS stops credentials and records from being read on the network between the cluster and the database. Samuel's checker reports any storage bucket or database that allows unencrypted connections, and the first run found one internal reporting database that still accepted them. It was changed in the next maintenance window, after the reporting tool was updated to connect over TLS.
9.5 Retention
Keeping data longer than needed increases risk. Westmere's rules: app logs 90 days, audit logs 365, exam uploads 400, test snapshots 30.
The periods reflect different needs. Application logs help debug recent problems, so ninety days is enough. Audit logs support investigations that may start months later. Exam uploads must be kept through the appeals period. Test snapshots should be short-lived because they may be copied around and forgotten, as the lab below shows. Each period was agreed with the records office and the relevant data owner, and the rules are written into lifecycle settings rather than left to memory.
Lab — Lab — retention check
"""Compare synthetic data store ages with retention rules (stdlib only)."""
rules = {"app-logs": 90, "audit-logs": 365, "exam-uploads": 400, "test-db-snapshots": 30}
stores = [("app-logs", 120), ("audit-logs", 200), ("exam-uploads", 380), ("test-db-snapshots", 210), ("test-db-snapshots", 12)]
for kind, age in stores:
print(f"{kind:18} age {age:3} d, limit {rules[kind]:3} d {'OVER RETENTION' if age > rules[kind] else 'ok'}")
Run with python3 lab_retention.py. Verified output:
app-logs age 120 d, limit 90 d OVER RETENTION
audit-logs age 200 d, limit 365 d ok
exam-uploads age 380 d, limit 400 d ok
test-db-snapshots age 210 d, limit 30 d OVER RETENTION
test-db-snapshots age 12 d, limit 30 d ok
9.6 Lab result
app-logs at 120 days exceed the 90-day limit. One test-db-snapshot at 210 days exceeds its 30-day limit; another at 12 days is fine. audit-logs (200 of 365) and exam-uploads (380 of 400) are within limits. The 210-day snapshot is a forgotten copy that should have been deleted automatically.
Both over-retention findings have a cause. App logs had no lifecycle rule, as described below, and the old snapshot was made by hand outside the rules. Fixing each needs a different action: adding a rule for the logs, and a process change for manual snapshots. Simply deleting the over-age data would leave both causes in place.
Lifecycle rules
Cloud storage and snapshot services support lifecycle rules that delete or archive data after a set age. Rules are defined in code, reviewed and tested on synthetic objects. They are better than manual deletion because they never forget. Samuel checks each rule against Westmere's retention policy and finds that app logs had no rule at all, which explains why they exceeded 90 days.
Owner review
Data owners confirm retention periods yearly with the records office.
Worked example
The snapshot dates from a migration. The team deletes it after confirming nobody needs it, and adds a rule that manual snapshots carry an expiry tag enforced by automation.
Synthetic case. Data stores are synthetic.
Errors and limits
Deleting data is irreversible; confirm before deleting.
Exercises
- EX025 — Test data rule. What should test databases contain? Rubric: 3 = synthetic data only; 2 = partial; 1 = vague; 0 = none.
- EX026 — Lab over. Which stores were over retention? Rubric: 3 = app-logs 120; snapshot 210; 2 = one; 1 = vague; 0 = none.
- EX027 — Snapshot fix. What rule followed? Rubric: 3 = expiry tags on manual snapshots; 2 = partial; 1 = vague; 0 = none.
Synthesis
Classify data, separate test data and enforce retention.
Continue studying — chapter 09 of 40
← Chapter 08 (draft) · Course chapter index · Chapter 10 (draft) →