Chapter 9 — Data and databases

Chapter 9 — Data and databases

Lesson map

Lesson 09 published. Map, example, EX025.

flowchart TD
  N0["Learning outcomes"]
  N1["9.1 Least-privilege database access"]
  N2["9.2 Injection and parameters"]
  N3["9.3 Backups and test copies"]
  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 Least-privilege database access"]
  N2["9.2 Injection and parameters"]
  N3["9.3 Backups and test copies"]
  N4["Synthesis"]
  N0 --> N1 --> N2 --> N3 --> N4 --> Q["EX025"]

[Learning outcomes]
    |
    v
[9.1 Least-privilege database access]
    |
    v
[9.2 Injection and parameters]
    |
    v
[9.3 Backups and test copies]
    |
    v
[Synthesis]
    |
    v
[Worked example] --> [Exercise EX025]

Learning outcomes

You will (1) design database access for least privilege; (2) prevent injection through parameterised queries; (3) protect backups and test copies.

9.1 Least-privilege database access

Fernway's shop database holds members, orders and stock. Lucas reviews how applications connect. The shop uses one database account with full rights to everything, including dropping tables. He proposes separate accounts: the shop can read products and write orders; reporting can read aggregated views; only an administrator account, used rarely and logged, can change the schema. Each account receives only the rights its function needs. If the shop is compromised, the attacker inherits only the shop's rights, not the power to delete or export every table.

9.2 Injection and parameters

Lucas reviews the shop's search code and finds a query built by joining strings with customer input. He demonstrates, on a synthetic database using Python's sqlite3, how a crafted search term returns every member record. The fix is to pass input as parameters, which the database treats strictly as data. He also checks other queries and finds two more built the same way. Parameterised queries are a simple, well-established control, yet injection remains common because developers under time pressure copy old patterns. Code review checklists should include it explicitly.

9.3 Backups and test copies

The inventory in chapter 2 found a test copy of the database in cloud storage. Lucas investigates: it contained real member data and was readable by anyone with the link. He recommends that test environments use synthetic or masked data, that backups be encrypted with keys held separately and that storage permissions be reviewed automatically. ISO/IEC 27002:2022 includes controls on test information and data masking. Copies of data multiply risk, and architecture must treat every copy with the same care as the original or avoid creating it.

Worked example

Lucas splits one all-powerful database account into three least-privilege accounts, finds three injectable queries and recommends synthetic test data and encrypted backups.

Synthetic case. Database contents and queries are synthetic; ISO/IEC 27002:2022 is real.

Errors and limits

Splitting accounts needs application changes and testing. Masked data can still be re-identifiable if done poorly.

Exercises

• EX025 — Compromise impact. How do separate database accounts limit a shop compromise? Rubric: 3 = attacker gains only the shop account's limited rights;; 2 = limited;; 1 = vague;; 0 = none.
• EX026 — Injection count. How many injectable queries did Lucas find in total? Rubric: 3 = three: the search query and two others built the same way;; 2 = three, without saying which;; 1 = one;; 0 = none.
• EX027 — Test data rule. What data should test environments use? Rubric: 3 = synthetic or masked data;; 2 = synthetic;; 1 = real;; 0 = none.

Synthesis

Database architecture limits each account's power, keeps input as data and controls every copy of the data.

Continue studying — chapter 09 of 40

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