Tutorial 5.1: Database Security
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
- use SQL Data Control Language to apply access control to a database system,
- troubleshoot and remedy incorrect permission assignments,
- determine how mandatory access control settings affect the running of common SQL queries, and
- perform a basic database security audit.
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, andSale. - Chris (customer service) — full access to
Customer; read access toSale. - Emily (security manager, investigates missing stock) — read access to
SaleandRestock; can view only thefName/lNameattributes of bothEmployeeandCustomer.
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,
SaleandCustomer: blank /SELECT, UPDATE→ALL PRIVILEGES. She is the sysadmin with full access to the whole database, so every table isALL PRIVILEGES. - Rachel,
Restock:ALL PRIVILEGES→SELECT. She may viewRestockbut “is not allowed to make changes”. - Aaron,
Employee:UPDATE→-, andCustomer:SELECT→-. His remit isItem,RestockandSaleonly; nothing in the UoD gives him any access to staff or customer records. - Chris,
Sale:UPDATE, GRANT→SELECT. He needs read access toSale, soUPDATEis too much — and the grant option is never justified for anyone but Paris. - Emily,
Sale:-→SELECT. She investigates missing stock and needs read access toSaleas well asRestock. - Emily,
Employee:SELECT→-, withEmployee.fNameandEmployee.lName:-→SELECT. This is the pair of changes worth understanding: the UoD gives her only two columns ofEmployee, so table-levelSELECT(which would exposeroleandemailas well) is withdrawn and replaced by attribute-levelSELECT— the same shape herCustomeraccess 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: youREVOKE ALL PRIVILEGESand thenGRANTback exactly what is wanted (Rachel, Chris, Emily). Where nothing should be left, theREVOKEstands alone (Aaron). REVOKE GRANT OPTIONis a separate statement from revoking the privilege itself. Chris needs both: one to take awayUPDATE, 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 | 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:
- 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. - 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 areStockManager’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).