Tutorial 5.1: Database Security

exercises
tutorial
databases
database-security
access-control
sql-injection

Practice for 2026-05-11-database-security.

Section A — Discretionary Access Control

Universe of discourse: Rob’s Convenience Store has a database system to manage business activities. System admin Paris has created accounts for herself and four staff members, with these requirements:

  • Paris (sysadmin) — full access to the whole database, and can grant permissions to others.
  • Rachel (HR manager) — full access to Employee; can view (but not modify) Restock.
  • Aaron (supply chain lead) — full access to Item, Restock, and Sale.
  • Chris (customer service) — full access to Customer; read access to Sale.
  • Emily (security manager, investigates missing stock) — read access to Sale and Restock; can view only the fName/lName attributes of both Employee and Customer.

Schema: Employee [id, fName, lName, role, email], Restock [employee, item, time, quantity] (FKs to Employee.id, Item.id), Item [id, name, description, price], Sale [customer, item, time, quantity] (FKs to Customer.id, Item.id), Customer [id, fName, lName, dob].

Question A.1 — Identify and fix incorrect access

The permission table below (as currently configured) contains several errors. Fix it, using the UoD above (- = not directly granted).

Paris Rachel Aaron Chris Emily
Employee ALL ALL UPDATE — SELECT
Restock ALL ALL ALL — SELECT
Item ALL — ALL — —
Sale — — ALL UPDATE, GRANT —
Customer SELECT, UPDATE — SELECT ALL —
Customer.fName/lName — — — — SELECT
Employee.fName/lName — — — — —

Corrected table:

Paris Rachel Aaron Chris Emily
Employee ALL ALL — — —
Restock ALL SELECT ALL — SELECT
Item ALL — ALL — —
Sale ALL — ALL SELECT SELECT
Customer ALL — — ALL —
Customer.fName/lName — — — — SELECT
Employee.fName/lName — — — — SELECT

Errors fixed (bold): Rachel had ALL on Restock (should only be SELECT — she’s not allowed to make changes there). Aaron had UPDATE on Employee and SELECT on Customer (should have none — he’s supply chain, not HR/customer-facing). Chris had UPDATE, GRANT on Sale (should be SELECT only, and definitely no grant option — he only needs read access, and never had a stated need to further delegate access). Emily had no access to Sale at all (should have SELECT, per her investigative role) and no access to Employee.fName/lName (should have SELECT, matching her Customer.fName/lName access) — and Paris, as sysadmin, needed ALL on Sale and Customer (both were missing/blank), consistent with her full-database access.

Question A.2 — SQL grant/revoke queries

Write the SQL queries to fix the incorrectly-configured accounts from A.1.

-- Rachel
REVOKE ALL PRIVILEGES ON `Rob's Convenience Store`.`Restock` FROM 'Rachel';
GRANT SELECT ON `Rob's Convenience Store`.`Restock` TO 'Rachel';

-- Aaron
REVOKE ALL PRIVILEGES ON `Rob's Convenience Store`.`Employee` FROM 'Aaron';
REVOKE ALL PRIVILEGES ON `Rob's Convenience Store`.`Customer` FROM 'Aaron';

-- Chris
REVOKE ALL PRIVILEGES ON `Rob's Convenience Store`.`Sale` FROM 'Chris';
REVOKE GRANT OPTION ON `Rob's Convenience Store`.`Sale` FROM 'Chris';
GRANT SELECT ON `Rob's Convenience Store`.`Sale` TO 'Chris';

-- Emily
REVOKE ALL PRIVILEGES ON `Rob's Convenience Store`.`Employee` FROM 'Emily';
GRANT SELECT ON `Rob's Convenience Store`.`Sale` TO 'Emily';
GRANT SELECT (`fName`, `lName`) ON `Rob's Convenience Store`.`Employee` TO 'Emily';

Section B — Mandatory Access Control

Universe of discourse: ASIO tracks undercover agents’ identities in a MAC database, to keep classified information from riskier assignments protected.

Schema: Spy [ID, fName, lName, dob, email], CodeName [spyID, name, dateEffective, dateExpired, operation] (FK spyID → Spy.id, operation → Operation.name), Operation [name, description, budget].

DBMS accounts (low to high): Cody = U, Jane = C, Tanya = S, Jack = TS.

Sample data, with each attribute’s classification noted, and the overall Tuple Classification (TC = the highest classification of any attribute present):

Spy — id(U), fName(U), lName(TS), dob(C), email(S)
id  fName  lName  dob         email                 TC
1   Daniel Teal   18/06/1995  bmw@hotmail.com        TS
2   May    Lee    03/02/1998  may@stevefam.com       TS
3   John   Smith  12/05/1990  john@smith.com         TS
4   April  Fuller 25/06/1980  ilikefood@korea.com    TS

CodeName — spyID always (C); name/operation classification varies per row;
dateEffective/dateExpired always (U)
spyID  name              dateEffective  dateExpired  operation      TC
1      Handbrake (C)     01/02/2019     03/06/2020   Le-Ferrari (S) S
2      BunnyBunny (S)    12/03/2019     12/04/2019   Hotpot (C)     S
4      BubbleTea (C)     19/04/2019     24/04/2019   Hotpot (C)     C
2      BBQ (TS)          27/09/2019     04/10/2019   KimChi (TS)    TS

Operation
name         description                                    budget       TC
Hotpot (S)   "Infiltrate McDonalds..." (S)                  1000000 (S)  S
Le-Ferrari (C) "Identify the Ferrari-stealing syndicate leader" (U) 10000000 (U) C
KimChi (S)   "Someone stole ASIO's KimChi supply..." (S)     500000000 (S) S

Question B.1 — Identifying security denials

For each query, determine whether it succeeds (S) or fails (F), and why.

User Query Result Reason
Cody SELECT id FROM Spy; S Cody’s clearance (U) ≥ the classification of every id value (U).
Cody SELECT name FROM Operation WHERE budget > 10000000 F The one matching tuple (KimChi) has TC = S, above Cody’s U clearance — the query returns nothing even though the WHERE condition is logically true for it.
Cody INSERT INTO CodeName (spyID, name, dateEffective, dateExpired, operation) VALUES (2, "BBQ", "16/05/2019", "26/05/2019", "Hotpot") S Looks like it should violate the key constraint (another spyID=2, name="BBQ" combination already exists at TS) — but since the two “identical” tuples have different security classifications, the DBMS applies polyinstantiation to let them coexist.
Jack INSERT INTO Operation (name, description, budget) VALUES ("Ace", "Find an unidentified hacker...", 350000) F Operation’s highest classification is S. Jack’s clearance is TS — higher than S — so the star property (no write down) blocks this insert, to prevent Jack from writing lower-classified information into a table that could then leak higher-clearance context.
Jane UPDATE CodeName SET name = "Iced Coffee" WHERE spyID = 4 S Jane’s clearance (C) ≥ the classification of spyID (C, so she can read/select the row) and ≤ the classification of name for that row (C, so she can write it) — both read and write permission hold, so the update succeeds.

Question B.2 — Returning data in a MAC database

What data is returned for each query?

Jane: SELECT spyID, name, operation FROM CodeName →

spyID name operation
1 Handbrake NULL
2 NULL Hotpot
4 BubbleTea Hotpot

(Row spyID=2, name="BBQ" is entirely TS-classified — above Jane’s C clearance — so it doesn’t appear at all. Within the visible rows, any individual cell above Jane’s clearance is masked to NULL rather than hiding the whole row — e.g. row 1’s Handbrake is C, visible, but row 2’s BunnyBunny is S, masked.)

Jane: SELECT name, dob FROM CodeName, Spy WHERE id = spyID AND dateEffective < "01/06/2020" →

name dob
Handbrake 18/06/1995
NULL 03/02/1998
BubbleTea 25/06/1980

Cody: SELECT name, budget FROM Operation WHERE description LIKE "%a%" →

name budget
NULL 10,000,000

(Only Le-Ferrari — TC=C — is within reach of Cody’s U clearance enough to appear at all, and even then its name attribute specifically, being C-classified, is masked to NULL for a U user; budget for that row is U-classified so it’s shown.)

Jane: SELECT spyID, Operation.name, Operation.budget FROM Operation, CodeName WHERE Operation.name = CodeName.operation → empty set. Every possible join row pairs a CodeName.operation value with an Operation.name value where at least one side is classified S or TS — above Jane’s C clearance — so no valid (joinable, visible-to- Jane) pairing exists, and the join returns nothing.

Bonus — Completing a database audit

Universe of discourse: SummerStyles (a clothing retailer) audits its DBMS log monthly for unauthorised changes or potential privacy violations.

Using the log below, flag any potentially suspicious records (there’s no exact formula — use judgement).

event_time            user_host                thread_id  argument
17-05-2020 8:27:18am   SystemAdmin@localhost    2097       SHOW COLUMNS FROM SummerStyles.customer
17-05-2020 8:28:00am   SystemAdmin@localhost    2097       SHOW INDEXES FROM SummerStyles.customer
17-05-2020 8:30:07am   HRManager@localhost      2103       UPDATE SummerStyles.staff SET Wage = Wage * 1.1
17-05-2020 8:31:07am   CustomerService@localhost 2129      SELECT * FROM SummerStyles.customer WHERE fName="Elaine" AND lName="Wang"
17-05-2020 8:33:30am   SystemAdmin@localhost    2129       SELECT TABLE_NAME FROM information_schema.VIEWS WHERE TABLE_SCHEMA='SummerStyles' AND TABLE_NAME='Customer'
17-05-2020 8:35:05am   CustomerService@localhost 2129      SELECT fName, lName, max(wage) FROM SummerStyles.staff
17-05-2020 8:35:43am   StockManager@localhost   2129       DELETE FROM SummerStyles.deliveries WHERE id = 204472
17-05-2020 8:38:32am   StockManager@localhost   2128       UPDATE SummerStyles.currentStock SET quantity = quantity + 8 WHERE stockID = 1263
17-05-2020 8:41:14am   SystemAdmin@localhost    2129       ALTER TABLE SummerStyles.staff ADD email varchar(255);
17-05-2020 8:45:41am   HRManager@localhost      2129       INSERT INTO SummerStyles.staff (id, fName, lName) VALUES (142, "Leya", "Rebecca")
17-05-2020 8:57:37am   HRManager@localhost      2131       UPDATE SummerStyles.stock SET price = 11.00 WHERE name LIKE "%Calvin Klein%"

Two entries stand out as worth flagging:

  1. 8:35:05am — CustomerService: SELECT fName, lName, max(wage) FROM SummerStyles.staff. A customer service rep querying staff wage data is well outside their normal remit — salary information is HR’s domain (per the DAC exercise above, this kind of cross-role access is exactly what discretionary permissions should normally prevent) — a potential unauthorised-access/privacy concern worth following up.
  2. 8:57:37am — HRManager: UPDATE SummerStyles.stock SET price = 11.00 WHERE name LIKE "%Calvin Klein%". An HR manager modifying product pricing is outside HR’s normal duties (that’s StockManager’s job, as seen elsewhere in the log) — an unauthorised-modification concern, and potentially indicative of privilege misuse.