Chapter 9 — Data and databases
Lesson map
Lesson 09 published. Map, example, EX025.
flowchart TD
N0["Learning outcomes"]
N1["9.1 Tables"]
N2["9.2 SQL basics"]
N3["Lab — querying sign-ins"]
N4["9.3 Reading the result"]
N5["9.4 Joins"]
N6["9.5 Quality"]
N7["9.6 Privacy"]
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 Tables"]
N2["9.2 SQL basics"]
N3["Lab — querying sign-ins"]
N4["9.3 Reading the result"]
N5["9.4 Joins"]
N6["9.5 Quality"]
N7["9.6 Privacy"]
N0 --> N1 --> N2 --> N3 --> N4 --> N5 --> N6 --> N7 --> Q["EX025"]
[Learning outcomes]
|
v
[9.1 Tables]
|
v
[9.2 SQL basics]
|
v
[Lab — querying sign-ins]
|
v
[9.3 Reading the result]
|
v
[9.4 Joins]
|
v
[9.5 Quality]
|
v
[9.6 Privacy]
|
v
[Worked example] --> [Exercise EX025]
Learning outcomes
You will (1) model security data as tables; (2) write SQL queries with filters, grouping and joins; (3) use a database to answer investigation questions; (4) consider data quality and privacy in stored data.
9.1 Tables
Security logs fit naturally into tables: one row per event, columns for time, user, address, country and result. Databases answer questions over millions of rows quickly.
Riverton's SOC loads each day's sign-in export into a database table with those columns plus the client application. During the phishing incident, analysts queried a month of data, several million rows, to find every sign-in by the clicking accounts. The same questions in a spreadsheet would have been slow and error-prone, and harder to repeat exactly.
9.2 SQL basics
SELECT chooses columns, WHERE filters rows, GROUP BY aggregates and ORDER BY sorts. A query such as "successful sign-ins outside the usual country, grouped by user" answers an investigation question in one statement.
Lucas's query selected user, time, country and result from the sign-in table, filtered to successful sign-ins where the country was not GB, grouped by user and counted them. Writing the question as a query also documents it: Dr. Kim could read the statement and confirm it asked what she intended before relying on its answer.
Lab — querying sign-ins
"""Query synthetic sign-in records with the standard-library sqlite3 module."""
import sqlite3
db = sqlite3.connect(":memory:")
db.execute("create table signin(ts text, user text, country text, result text)")
rows = [("09:01","s1001","GB","ok"),("09:03","s1002","GB","ok"),("09:05","s1001","GB","ok"),
("02:14","s2047","NL","ok"),("02:15","s2047","NL","ok"),("02:20","s3310","NL","fail"),
("10:12","s2047","GB","ok"),("02:31","s3310","NL","ok")]
db.executemany("insert into signin values (?,?,?,?)", rows)
q = "select user, count(*) from signin where country != 'GB' and result = 'ok' group by user order by user"
for user, n in db.execute(q): print(user, "successful sign-ins outside GB:", n)
Run with python3 lab_sqlite.py. Verified output:
s2047 successful sign-ins outside GB: 2
s3310 successful sign-ins outside GB: 1
9.3 Reading the result
Two accounts had successful sign-ins outside GB: s2047 twice and s3310 once. s3310's success at 02:31 followed a failure at 02:20, suggesting the attacker had its password after a mistake or second attempt.
The pair of records for s3310 matters for the timeline: the failure eleven minutes before the success shows the attacker tried the account more than once. Lucas notes it as consistent with a stolen password, while recognising that it could also mean the attacker mistyped. Either way, the success at 02:31 is when the account was compromised.
9.4 Joins
Joining the sign-in table with the mailbox-rule table on user shows which risky sign-ins were followed by rule changes.
The join found that s2047's unusual-country sign-in was followed six minutes later by a rule forwarding mail to an external address. For s3310, the join returned a deletion rule too, but created more than two hours after the risky sign-in. Lucas recorded both, since a join on user alone shows that a rule exists, while the time difference decides whether the two events are likely to be connected. That difference matters again when the correlation rule is built and tested in the first laboratory.
9.5 Quality
Missing countries, duplicated rows and inconsistent user formats (s2047 versus S2047) distort results. Normalising at load time prevents errors.
In Riverton's data, the identity provider wrote usernames in lower case while one mail export used capitals. Without normalising, a join would have treated s2047 and S2047 as different users and missed the link between sign-in and rule. Lucas converts usernames to lower case and removes duplicate rows as the data is loaded, and records how many were changed in a load report.
9.6 Privacy
Databases with personal data need access control and retention limits. The SOC database keeps sign-in details for 90 days.
Access to the database is limited to SOC staff and the student analysts on shift, and queries are logged. Ninety days covers most investigations, which usually begin within days of an incident. Records needed for an open case, such as s2047's sign-ins, are copied into the case file before deletion, so retention does not destroy evidence still needed for an open case.
Indexes
Queries filtering by user or time run faster when those columns are indexed. Lucas adds indexes on user and timestamp after a query over a month of data takes minutes, and it then returns in under a second. Indexes cost storage and slow inserts slightly, a trade-off worth making for fields used in most investigations.
Worked example
Lucas extends the query to compare each user's sign-in countries with their previous 30 days. s2047 had only GB before; s3310 had GB and FR (a semester abroad). The NL sign-in is new for both, but s3310's history reminds him that travel is normal for some students.
Synthetic case. Records come from the lab script and invented history.
Errors and limits
SQL answers what was stored; events never logged cannot be queried.
Exercises
- EX025 — Lab result. Which accounts had successful sign-ins outside GB? Rubric: 3 = s2047 (2), s3310 (1); 2 = one; 1 = wrong; 0 = none.
- EX026 — Failure then success. What does s3310's 02:20 failure followed by 02:31 success suggest? Rubric: 3 = attacker obtained/retried password successfully; 2 = partial; 1 = vague; 0 = none.
- EX027 — User history. Why compare with the previous 30 days? Rubric: 3 = shows what is normal for each user (e.g. travel); 2 = partial; 1 = no reason; 0 = none.
Synthesis
Tables and SQL answer investigation questions quickly when data is clean and handled under privacy rules.
Continue studying — chapter 09 of 40
← Chapter 08 (draft) · Course chapter index · Chapter 10 (draft) →