05 · Database Security
Relational DB & SQL → SQL injection → categories & defences → access control → inference → encryption
(s.N) = slide number.
Relational DB: tables of rows and columns, linked by a unique identifier (s.4-5)
Terms (s.7) relation = table = file; tuple = row = record; attribute = column = field primary key uniquely identifies a row; foreign key links tables; view = virtual table from a query
SQL: IBM, mid-1970s; defines, manipulates, queries data; CREATE TABLE / FOREIGN KEY / CREATE VIEW (s.10)
Categories (s.16)
- In-band: same channel (tautology,
--, piggyback) - Inferential: no data shown; behaviour/timing (blind)
- Out-of-band: other channel (DNS/HTTP, email)
Inferential: illegal/logically incorrect queries reveal DB structure through descriptive error pages (s.18)
Defences (s.20)
- defensive coding: validate input (numeric = digits only)
- detection: signature, anomaly, code analysis
- runtime prevention: match expected queries
Tautology: password name' OR 'a'='a makes WHERE always true → SELECT * FROM items, all rows (s.13-14)
↪ category → 03 · SQLi categories & defences
Administration (s.21)
- centralized: few privileged users
- ownership-based: table owner
- decentralized: owner can delegate granting
↪ bypassed by → 05 · Inference
GRANT {privs|role} [ON table] TO {user|role|PUBLIC} [IDENTIFIED BY pw] [WITH GRANT OPTION]; REVOKE ... FROM ...; e.g. GRANT SELECT ON ANY TABLE TO john (s.22)
Rights: SELECT, INSERT, UPDATE, DELETE. PUBLIC = any user; GRANT OPTION = grantee can pass it on (s.22-24)
Inference = authorized queries + deduction → unauthorized information (s.25)
Techniques: functional dependencies (Rank → Salary); merging views (s.26)
Views example: V1 (Availability, Cost) + V2 (Item, Department), same row order → Item-Cost link revealed (s.27-28)
Fix: at design (split table, finer RBAC roles) or at query time (deny/alter) (s.29)
SQLi: malicious SQL to the DB server → modify/delete data, run OS commands, DoS (s.11)
Piggyback + comment: John'; DROP table Course;-- ends the string, adds a command, -- comments the rest (s.15)
Encrypt: entire DB = inflexible; rows or columns = best, plus attribute indexes (s.30)
Range queries fail: encrypted values lose ordering, e.g. Salary > 7000 (s.33)
Fix: encrypt each row as a block E(k,B) + index per attribute (Eid 1-200 → 1 ... 801-1000 → 5) (s.34-35)
eid < 300 → request I(eid)≤2 → decrypt rows → discard non-matches → plaintext to user (s.36)
Exam traps (see Past Exam note)
Q: Inferential SQLi vs inference? A: attack category (blind) vs leak via authorized queries (s.16, 25)
Q: Why can't an encrypted salary column do range queries? A: ordering not preserved (s.33)
Q: Injected always-true condition? A: tautology (s.17)
Gap: s.38 lists statistical database security, no slide covers it