Part 5 of 5 · 3 min read
Exercises
Easy
Easy 1: Grant read access (15 minutes)
Write a statement granting SELECT on books to a role called catalog_reader.
Done when: a correct GRANT SELECT ON books TO catalog_reader; is given.
Hint
GRANT SELECT ON books TO catalog_reader;
Easy 2: Revoke access (15 minutes)
Write a statement removing that same permission.
Done when: a correct REVOKE SELECT ON books FROM catalog_reader; is given.
Hint
REVOKE SELECT ON books FROM catalog_reader;
Easy 3: Spot the injection risk (15 minutes)
State whether building a query by directly concatenating a user-supplied string into SQL text is safe.
Done when: not safe is correctly stated, naming SQL injection as the risk.
Hint
Not safe; this is exactly the pattern that enables SQL injection.
Medium
Medium 1: Apply least privilege (25 minutes)
A new support_role needs to look up customer orders to answer questions, but never needs to change any data. Write the correct GRANT statements, and name any permission deliberately left out.
Done when: SELECT-only grants on the needed tables are given, with write permissions explicitly identified as left out.
Hint
GRANT SELECT ON orders, customers TO support_role;, explicitly leaving out INSERT, UPDATE and DELETE.
Medium 2: Role reassignment (25 minutes)
An employee moves from the reporting team to the support team. Describe the correct way to update their access using roles, rather than editing their individual permissions directly.
Done when: the description correctly involves revoking the old role and granting the new one, rather than touching individual permissions.
Hint
Revoke the reporting role and grant the support role, rather than editing individual permission grants directly.
Medium 3: Rewrite a vulnerable query (25 minutes)
Rewrite "SELECT * FROM customers WHERE customer_id = " + user_input as a safe, parameterized query.
Done when: a correct parameterized form, binding user_input separately from the SQL text, is given.
Hint
SELECT * FROM customers WHERE customer_id = $1; with $1 bound separately to user_input.
Hard
Hard 1: Why 1=1 is the classic injection payload (30 minutes)
Explain exactly why appending OR 1=1 to an injected input causes a WHERE clause to match every row, regardless of the original condition.
Done when: the explanation correctly notes 1=1 is always true, and OR with an always-true condition makes the whole WHERE clause always true.
Hint
1=1 evaluates to true for every row, and OR only needs one side to be true, so the entire condition becomes true for every row regardless of the original filter.
Hard 2: Over-granting discovered in an audit (35 minutes)
An access audit finds reporting_role has DELETE permission on orders, which nobody remembers granting or ever needing. Describe the correct remediation, and the principle it restores.
Done when: a REVOKE of the unneeded DELETE permission is correctly proposed, explicitly tied back to least privilege.
Hint
REVOKE DELETE ON orders FROM reporting_role; restores least privilege by removing a permission the role never actually needed.
Hard 3: Parameterized queries versus input sanitization (40 minutes)
Explain why manually "sanitizing" user input, such as stripping out the word "OR," is a weaker defense against SQL injection than using parameterized queries.
Done when: the explanation correctly notes that sanitization tries to block known bad patterns and can be bypassed by an unanticipated one, while parameterized queries structurally prevent input from ever becoming SQL logic at all.
Hint
Sanitization defends against specific known patterns and can miss a new or disguised one; a parameterized query never interprets input as SQL logic in the first place, regardless of its content.