Chapter 9 — Data and databases | Bachelor's Identity and access management

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 A simple database"]
  N2["9.2 Queries"]
  N3["9.3 Protecting the data"]
  N4["Synthesis"]
  N0 --> N1 --> N2 --> N3 --> N4
  N4 --> EX["Worked example"]
  EX --> Q["Exercise EX025"]

Logical drawing

flowchart LR
  N0["Learning outcomes"]
  N1["9.1 A simple database"]
  N2["9.2 Queries"]
  N3["9.3 Protecting the data"]
  N4["Synthesis"]
  N0 --> N1 --> N2 --> N3 --> N4 --> Q["EX025"]

[Learning outcomes]
    |
    v
[9.1 A simple database]
    |
    v
[9.2 Queries]
    |
    v
[9.3 Protecting the data]
    |
    v
[Synthesis]
    |
    v
[Worked example] --> [Exercise EX025]

Learning outcomes

You will (1) store identity data in a simple database; (2) write queries for common questions; (3) protect personal data in the store.

9.1 A simple database

Monthly CSV files become hard to compare over time, so Elena loads them into a small SQLite database, which Python's standard library supports. Tables hold staff, accounts, group memberships and monthly snapshots, each with a date. Storing snapshots allows questions such as how many leavers were disabled within a day in each month. The structure is documented with a short description of every table and column, so anyone at Greenhill can understand what is stored and why. Each snapshot records the export file it came from.

9.2 Queries

Queries answer questions directly. Which enabled accounts have not signed in for ninety days? Which staff are in more than five groups? How many leavers in March were disabled after their leaving date? Elena writes each query with a clear time window, checks its results against a small sample by hand and saves it with a description. Saved queries mean the same question gets the same method every month, which makes trends trustworthy rather than an artefact of how someone happened to ask.

9.3 Protecting the data

The database contains names, departments and sign-in dates, which is personal data. Elena stores it on an encrypted Greenhill drive with access limited to the IT team, keeps only the fields her checks need and deletes snapshots older than two years, as agreed with Greenhill's data protection lead. She never copies it to her personal devices. Handling identity data carefully is part of being trusted to work with it, and it is also a legal duty under data protection law.

Worked example

The SQLite store holds twelve monthly snapshots; a saved query finds nine synthetic enabled accounts unused for ninety days.

Synthetic case. Tables, snapshots and counts are synthetic.

Errors and limits

Keeping every field from every export forever creates a growing store of personal data with no purpose.

Exercises

EX025 — Snapshot value. Why store dated snapshots? Rubric: 3 = compare months and measure trends;; 2 = trends;; 1 = backup;; 0 = none.
EX026 — Unused query result. How many enabled accounts are unused for ninety days in the worked example? Rubric: 3 = nine;; 2 = 9;; 1 = vague;; 0 = none.
EX027 — Retention agreed. How long are snapshots kept? Rubric: 3 = two years, agreed with data protection lead;; 2 = two years;; 1 = forever;; 0 = none.

Synthesis

A documented database, saved queries and careful data protection make identity trends reliable and lawful.

Continue studying — chapter 09 of 40

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