Granting rights, and the injection that ignores them

Why the operating system cannot do this job

OS access control works on whole files. It can permit or forbid the personnel database as an object, and that is the end of its vocabulary. What it cannot do is restrict access to particular records or fields, or say which SQL commands a given user may issue against which items. That is the gap a DBMS fills, and the layering is deliberate rather than duplicated: the OS gates the database as a whole, the DBMS gates portions of it, and the DBMS assumes the OS has already authenticated whoever is asking.

Database security lagged the rest of the field for reasons that are organisational rather than technical. A DBMS is enormous and feature-rich, and every option has to be understood before it can be secured; SQL is a far richer interaction protocol than HTTP, so there is much more to get wrong; there is rarely a full-time database security role, because DBAs are measured on availability and performance while security staff lack DBMS depth; platforms are heterogeneous; and increasingly the database is somebody else's cloud service.

Views as an access-control mechanism

A view is a virtual table produced by a query. It can restrict rows, columns, or both, over one or more base tables, and it is the standard way to give somebody partial access to data they must not see in full.

Worked example. A staff table has seven columns: id, surname, email, department, salary, bank_account, active. The support desk needs to look people up but has no business seeing pay or banking details. So define a view over the three columns they do need, and grant on the view, not the table:

CREATE VIEW staff_directory AS
  SELECT id, surname, department
    FROM staff
   WHERE active;

GRANT SELECT ON staff_directory TO support_desk;

The support desk account now has no privilege on staff at all. Hold on to that last sentence — it is what makes the injection section below land differently than most learners expect.

GRANT, REVOKE, and the cascade

Administration of these rights follows one of three policies: centralized (a few privileged users grant and revoke), ownership-based (whoever created the table grants and revokes it), or decentralized, where the owner may also hand out the authorization right itself, letting grantees grant onward. Decentralized administration is DAC's propagation problem wearing SQL syntax.

The statement itself carries the five SQL access rights — Select, Insert, Update, Delete and References. References is the one everybody forgets: it is the right to define a foreign key referring to the specified columns, and it leaks, because a foreign key constrains which values may exist and can therefore be used to probe them.

GRANT SELECT, UPDATE ON staff_directory TO hr_lead WITH GRANT OPTION;

WITH GRANT OPTION is what creates a cascade: hr_lead may now pass the same rights on. And revocation cascades too, under a rule that is subtler than it looks. When A revokes a right, any right that cascaded from it is also revoked, unless that right would still exist had A's original grant never happened. The test depends on timestamps, not merely on graph reachability — which is why a grantee who received the same right twice keeps it after one grantor revokes, while the rights that grantee had already passed on before the second grant arrived still die. Learners test reachability and get this wrong.

SQL injection: what actually goes wrong

The mechanism is one sentence: a query built by string concatenation lets attacker-supplied input terminate the string literal early and append syntax of the attacker's choosing, with -- commenting out whatever the application intended to put after it. The root cause is input that was not filtered for escape characters and not strongly typed. The attack rides ordinary, legitimate HTTP, so the firewall passes it untouched — the pedagogical point is that the firewall is irrelevant here, and a web application firewall is compensating control rather than a fix.

Worked example. An application concatenates a surname into a lookup:

"SELECT id, email FROM staff WHERE surname = '" + box + "' AND active = TRUE"

A user types Okafor and the query is fine. An attacker types ' OR 1=1 -- and the string the database parses becomes:

SELECT id, email FROM staff WHERE surname = '' OR 1=1 --' AND active = TRUE

The quote closed the literal, OR 1=1 made the predicate universally true, and -- deleted the rest of the sentence including the active filter. Every row comes back. Nothing was hacked: the parser did its job on the sentence it was handed. The attacker did not supply a value, they supplied syntax.

The structural fix is a parameterised query, where the developer fixes the query's structure in advance and passes values separately, so an argument can only ever be a value:

PREPARE lookup (text) AS
  SELECT id, email FROM staff WHERE surname = $1 AND active = TRUE;

Executing that statement with the argument ' OR 1=1 -- now searches for somebody whose surname is literally those eleven characters, finds nobody, and returns zero rows. The payload became data because the structure was already committed.

Classifying an attack: five avenues, three types, three countermeasure classes

The five avenues are about how the payload arrives: user input (a form field via GET or POST); server variables (HTTP headers and environment variables, which an attacker can forge — and the injection fires when the logging query runs, not when the request is served); second-order injection, where the payload is stored innocuously and fires later when the stored data is used to build a query; cookies, which are client-controlled, so an altered cookie reshapes a query built from restored state; and physical user input such as barcodes, RFID tags, and OCR'd paper forms.

The three type categories are about how results come back:

• Inband — injection and retrieval share one channel. Sub-types: tautology (' OR 1=1 --), end-of-line comment, and piggybacked queries, which need a server that permits several statements in one string.
• Inferential — no data is transferred at all. The attacker reads the server's behaviour: illegal or logically incorrect queries that provoke descriptive error messages revealing the backend, and blind SQL injection, which asks true/false questions and watches the page differ even with no error.
• Out-of-band — results come back over a different channel entirely, such as the database sending them by e-mail.

The three countermeasure classes are defensive coding (input type checking, pattern matching, parameterised queries, and typed APIs in place of unregulated string concatenation), detection (signature-based, which needs constant updating and fails against self-modifying attacks; anomaly-based, with a training phase and then a detection phase; and code analysis), and run-time prevention (checking each query against a model of the queries the application is expected to issue).