Tutorial 5.1: Database Security

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

Practice for 2026-05-11-database-security; the mechanisms themselves are collected in database-access-control.

Purpose: assign and revoke privileges using SQL, identify DBMS access errors for given situations, and gain experience identifying privacy violations that occur through a lack of inference control. By the end you should be able to

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].

Figure 1: The Rob’s Convenience Store schema the whole of Section A is set on. Each table lists its columns with their types (int(11), varchar(100), timestamp), as a database tool shows them, not in the course’s ER notation. The four foreign keys are crimson arrows pointing FK → PK. The shaded fName/lName columns in Employee and Customer are the four attributes Emily’s column-level grant in A.1/A.2 is about, and they are the reason this exercise needs attribute-level privileges at all.

Question A.1 — Identify and fix incorrect access

The permission table below (as currently configured) contains several errors. Fix it, using the UoD and the schema above. - indicates that the access level for that object is not directly specified; the first column (Paris) has been fixed for you already.

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

Corrected table (changed cells in bold):

Paris Rachel Aaron Chris Emily
Employee ALL PRIVILEGES ALL PRIVILEGES - - -
Restock ALL PRIVILEGES SELECT ALL PRIVILEGES - SELECT
Item ALL PRIVILEGES - ALL PRIVILEGES - -
Sale ALL PRIVILEGES - ALL PRIVILEGES SELECT SELECT
Customer ALL PRIVILEGES - - ALL PRIVILEGES -
Customer.fName - - - - SELECT
Customer.lName - - - - SELECT
Employee.fName - - - - SELECT
Employee.lName - - - - SELECT

Ten cells change, and each traces to one line of the UoD:

  • Paris, Sale and Customer: blank / SELECT, UPDATE → ALL PRIVILEGES. She is the sysadmin with full access to the whole database, so every table is ALL PRIVILEGES.
  • Rachel, Restock: ALL PRIVILEGES → SELECT. She may view Restock but “is not allowed to make changes”.
  • Aaron, Employee: UPDATE → -, and Customer: SELECT → -. His remit is Item, Restock and Sale only; nothing in the UoD gives him any access to staff or customer records.
  • Chris, Sale: UPDATE, GRANT → SELECT. He needs read access to Sale, so UPDATE is too much — and the grant option is never justified for anyone but Paris.
  • Emily, Sale: - → SELECT. She investigates missing stock and needs read access to Sale as well as Restock.
  • Emily, Employee: SELECT → -, with Employee.fName and Employee.lName: - → SELECT. This is the pair of changes worth understanding: the UoD gives her only two columns of Employee, so table-level SELECT (which would expose role and email as well) is withdrawn and replaced by attribute-level SELECT — the same shape her Customer access already had.

The general rule the whole question drills: grant the narrowest privilege that satisfies the stated responsibility — read-only where someone only needs to look, nothing at all on tables outside their remit, and attribute-level SELECT where they need two columns rather than a whole table.

Question A.2 — SQL grant/revoke queries

Using your answer above, write the appropriate SQL queries to fix the user accounts with incorrect permissions. (Extension: import the sample database and test the queries manually.)

Only the four accounts with errors need statements — Paris’s own entries were corrected as the worked example, and no account needs a statement for a cell that was already right (Emily keeps her Restock SELECT and her two Customer columns untouched).

-- 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';

Three points of syntax the solution turns on:

  • Downgrading is revoke-then-grant. There is no “set the privilege to SELECT” statement: you REVOKE ALL PRIVILEGES and then GRANT back exactly what is wanted (Rachel, Chris, Emily). Where nothing should be left, the REVOKE stands alone (Aaron).
  • REVOKE GRANT OPTION is a separate statement from revoking the privilege itself. Chris needs both: one to take away UPDATE, one to take away his ability to pass anything on.
  • Attribute-level privileges are written as a column list on the privilege, not on the object: GRANT SELECT (`fName`, `lName`) ON `…`.`Employee`. Backticks quote identifiers containing spaces and apostrophes — hence `Rob's Convenience Store` — and the schema is qualified as `schema`.`table`.

Section B — Mandatory Access Control

Universe of discourse: ASIO (the Australian Security Intelligence Organisation) has created a database to track the identities of its undercover agents. Because some agents’ assignments are riskier than others, a MAC database was implemented to prevent classified information falling into the wrong hands.

Schema:

Spy [ID, fName, lName, dob, email]
CodeName [spyID, name, dateEffective, dateExpired, operation]
Operation [name, description, budget]

CodeName.spyID references Spy.id
CodeName.operation references Operation.name

CodeName’s key is the composite {spyID, name}, which is what makes B.1’s third query an apparent key violation. (The Spy key is written ID in the schema line but Spy.id in the foreign-key line and id in the sample data; it is the same attribute.)

DBMS accounts, low to high:

Database user Access level
Cody U (Unclassified)
Jane C (Confidential)
Tanya S (Secret)
Jack TS (Top Secret)

Sample data

Each value carries its own classification, shown in parentheses; TC is the Tuple Classification, the highest classification of any attribute in the tuple.

Spy — note that fName and lName are both TS, so only Jack can read an agent’s actual name; id is U, dob is C, email is S.

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

CodeName — the classification of spyID, name and operation varies row by row (in the BBQ row all three are TS); both date attributes are U in every row.

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

Operation — the highest classification anywhere in this table is S, which is what makes Jack’s insert in B.1 illegal.

name description budget TC
Hotpot (S) Infiltrate McDonalds and discover the ingredients in the Big Mac secret sauce. (S) 1000000 (S) S
Le-Ferrari (C) Identify the leader of an international Ferrari stealing syndicate. (U) 10000000 (U) C
KimChi (S) Someone has stolen ASIO’s secret KimChi supply and now the yearly Korean BBQ party is ruined! The thief must be found! (S) 500000000 (S) S

Figure 2: The two Bell–LaPadula rules, with this exercise’s four accounts on the rungs they occupy. The four classes run low to high, U (green), C (orange), S (red), TS (dark red); the three worked outcomes on the right are B.1’s second, fourth and fifth rows. A subject may read anything at or below its own rung and write anything at or above it, so the only unrestricted position is a subject whose clearance exactly matches the data’s class — which is why Jane’s UPDATE at C succeeds while Cody reading up and Jack writing down both fail.

Question B.1 — Identifying security denials

Based on the information above, identify which of the following queries will succeed and which will return nothing / fail to be executed. The first row is the worked example.

User Query S / F Reason
Cody SELECT id FROM Spy; S Cody’s classification (U) is ≥ the classification of every id value (U), so the query is valid.
Cody SELECT name FROM Operation WHERE budget > 10000000 F Cody’s level (U) is not ≥ the classification of any of the tuples’ name values (S, C, S). So even though the WHERE condition evaluates true for one tuple (KimChi, 500000000), the query still returns nothing.
Cody INSERT INTO CodeName (spyID, name, dateEffective, dateExpired, operation) VALUES (2, "BBQ", "16/05/2019", "26/05/2019", "Hotpot") S This looks like a key violation — {spyID, name} = {2, BBQ} already exists — but the two “identical” tuples have different security classifications, so the DBMS can apply polyinstantiation to let them co-exist.
Jack INSERT INTO Operation (name, description, budget) VALUES ("Ace", "Find an unidentified hacker who has been using SQL injections to change students' grades to all 7's in Universities around Australia.", 350000) F The security classification of the Operation table is S, that being the highest class of any of its tuples. Jack’s class is TS, which is greater than S, so the query is not executed — it would let information flow from a higher to a lower security classification.
Jane UPDATE CodeName SET name = "Iced Coffee" WHERE spyID = 4 S Jane’s classification (C) is ≥ the classification of the spyID attribute (C), so she has read permission; it is also ≤ the classification of that row’s name attribute (C), so she has write permission. Both hold, so the DBMS processes the update.

Two things are worth pulling out of that table.

An UPDATE needs both directions to hold at once. Jane’s update reads (WHERE spyID = 4) and writes (SET name = …), so it is subject to both rules — read requires clearance ≥ class, write requires clearance ≤ class. Both are satisfied here only because the attributes involved are classified exactly at her own level. Had that row’s name been U, the same statement would have been a write down and failed.

“Fails” and “returns nothing” are the same answer here. The column is Success (S) or Failure (F), and Cody’s second query is F even though the DBMS does not raise an error — it simply returns an empty result. The question counts the two together (“identify which of the following queries will succeed, and which ones will return nothing / fail to be executed”), so the column is really does the user get an answer. The DBMS stays silent rather than refusing outright because telling a U user “permission denied” would itself disclose that a matching higher-classified tuple exists — the same covert-channel argument that motivates polyinstantiation.

Question B.2 — Returning data in a MAC database

Fill the table and identify what data is returned when the specified users run the query. The first row is the worked example.

Figure 3: The worked example of B.2, drawn out: the two different things MAC does to a tuple. The left-hand table is the CodeName sample data with each value’s classification, and the right-hand table is what Jane’s query returns. The distinction it makes is the one the remaining B.2 rows turn on: an over-classified value inside a tuple that is otherwise readable comes back as NULL, but a tuple with nothing readable in it does not come back at all — it is not returned as a row of NULLs.

Jane — SELECT spyID, name, operation FROM CodeName (the worked example, drawn out in Figure 3):

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

Row 1’s operation (Le-Ferrari, S) and row 2’s name (BunnyBunny, S) are above Jane’s C and come back as NULL; the BBQ row, whose spyID, name and operation are all TS, does not appear at all.

A caveat: the EMPLOYEE example in 2026-05-11-database-security drops a tuple whose key value is unreadable (the U user never sees Brown, because Name is C) — though nothing else in that tuple is readable at U either, so it does not settle which rule is doing the work. Applied strictly to CodeName, whose key is {spyID, name}, that rule would also drop row 2 — BunnyBunny is S — yet the model answer keeps it with name masked. So the test these answers actually apply is “is anything in the tuple readable”, not “is the whole key readable”. Answer the same way.

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

The join works because Spy.id is U and CodeName.spyID is C in the three surviving rows — both readable at C, so the pairing can be computed. dob is C throughout, so it is returned in full; BunnyBunny is masked as before; and the BBQ row is again absent (its spyID is TS, so it can neither be read nor joined), which is why there are three rows and not four even though every dateEffective in the table satisfies the condition.

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

name budget
NULL 10,000,000

Only Le-Ferrari’s description is U, so it is the only row whose WHERE condition Cody can even evaluate — Hotpot’s and KimChi’s descriptions are S and invisible to him. For that row, budget is U and is returned in full, while name is C and is masked to NULL. Note the shape of the leak this prevents: Cody learns that some operation matching %a% has a ten-million-dollar budget, but not which one.

Jane — SELECT spyID, Operation.name, Operation.budget FROM Operation, CodeName WHERE Operation.name = CodeName.operation: empty set returned. When you complete a join on CodeName and Operation using the operation attribute, in each instance one side of the join is of class S or TS, which is above Jane’s clearance. Hence there are no valid joins and no tuples returned. Concretely: the three joinable pairs are Le-Ferrari (S in CodeName, C in Operation), Hotpot (C in CodeName, S in Operation) and KimChi (TS/S) — each has at least one unreadable end, so the equality can never be established at C.

This is the sharpest MAC result in the tutorial: a join can return the empty set even though matching rows plainly exist, and even though the user can see parts of both tables.

Bonus — Completing a database audit

Universe of discourse: SummerStyles is a clothing retailer which uses a DBMS to store all its staff, customer and stock information. Every month it performs a database audit to check for unauthorised changes to company records and to identify potential privacy violations.

Task: using the log below, identify potential cases of privacy breaches or unauthorised modification of data. Hint: there is no exact formula — use your intuition and common sense to flag potential cases where you feel it is possibly a security breach.

The log has the column shape of a MySQL general query log: each entry records event_time, user_host, thread_id, server_id, command_type (every row here is Query) and the argument — the statement as issued. That “who ran what, and when” shape is exactly the system log that database-access-control describes an audit as reading.

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

Exactly two entries should be flagged:

  1. 8:35:05am — CustomerService: SELECT fName, lName, max(wage) FROM SummerStyles.staff. A customer service account querying staff wage data is well outside its remit — salary information is HR’s domain. A potential privacy breach.
  2. 8:57:37am — HRManager: UPDATE SummerStyles.stock SET price = 11.00 WHERE name LIKE "%Calvin Klein%". An HR account modifying product pricing is outside HR’s duties — the stock tables are StockManager’s domain, and that account’s own two entries in this log are both stock changes. A potential unauthorised modification.

Both share the same tell, and it is the one the audit is looking for: the statement is perfectly legal SQL that the account had the privileges to run, and it is the role, not the syntax, that is wrong. Which is the argument for A.1’s narrow grants — had CustomerService never been given SELECT on staff in the first place, there would have been nothing to find in the log.

Worth not flagging, and knowing why: the HRManager wage rise at 8:30:07 (an across-the-board UPDATE on staff.Wage with no WHERE looks drastic, but it is HR’s own table and its own business); the CustomerService lookup of one named customer at 8:31:07 (exactly what customer service does); and the SystemAdmin SHOW/ information_schema queries and ALTER TABLE (schema administration, which is that account’s whole purpose).