Granting and Revoking Privileges

The previous lesson left a view’s second job unfinished. A view can show the reader only a portion of the base table — but being able to show that does not mean the base table is hidden. Whoever queries the view can query the base table on the same connection too, if they choose to. What is missing is a separate mechanism that says who can see what.

The fourth of the five families separated out in the SQL Language Families lesson — data control language — writes exactly this. Its statements are short, but its concepts carry as many consequences as schema design: a portion of the most expensive mistakes in a system come out of privileges granted more broadly than needed.

The Parts of the Privilege Model

Object-level authorization ties three things together.

The grantee is the party that receives the privilege: a user account, a role, or a generic name meaning “everyone.” The object is the thing the privilege is defined on: a table, a view, a column, a schema, or the database itself. The privilege names the operation that can be performed on that object: reading, inserting, updating, deleting, referencing, changing the definition.

Every combination of this triple is a separate privilege. A grantee with read access on a table does not have write access on the same table; that has to be granted separately.

The standard answer to who grants a privilege is the object’s owner: the party that creates a table starts out holding every privilege on it, along with the right to grant those privileges to others.

Granting and Revoking

In standard SQL, privileges are granted with GRANT and revoked with REVOKE.

GRANT SELECT, INSERT, UPDATE ON loan TO clerk;
GRANT SELECT ON member_summary TO reporting;
GRANT SELECT (member_id, first_name, last_name) ON member TO reporting;
GRANT SELECT ON book TO cataloger WITH GRANT OPTION;

REVOKE DELETE ON loan FROM clerk;
REVOKE ALL PRIVILEGES ON member FROM reporting CASCADE;

The statements work exactly as they read: which privileges, on which object, to whom. The object on the second line is not a table but the view defined in the previous lesson — a privilege is granted on views the same way it is on tables. The third line narrows the privilege at the column level: the grantee can query the table but sees only the listed columns, and cannot reach the email address. The grant option on the fourth line also gives the grantee the right to grant the privilege it received to others.

Revoking has two subtleties. ALL PRIVILEGES covers every privilege granted to the grantee on that object. CASCADE follows the branching of a privilege granted with the grant option: if the grantee distributed that privilege to others, the revoke removes those distributions too. If this option is left out and branching exists, the standard rejects the revoke — because a branch left standing silently means a privilege that was thought to be revoked is not.

The grant option is therefore used sparingly. Who can distribute a privilege is a more important decision than the privilege being distributed itself: every grantee given the transfer right is a single point that can expand the privilege matrix on its own.

The concept of a role makes the same problem more manageable. Privileges are granted not to individual users but to a role, and users are attached to the role. When a job description changes, a single role’s privileges get adjusted; someone new joining the job inherits all its privileges by being attached to the role. Without roles, privileges multiply by the number of users, and nobody can track exactly what anyone can access.

It should be stressed that these statements are not found on every engine. Some engines have no concept of a user account; the command-line tool used in this lesson is one of them, and it cannot parse the GRANT statement. There, access control is left to filesystem permissions and the application layer. Where authorization happens is one of the questions to ask when choosing an engine.

Measuring the Principle of Least Privilege

The principle of least privilege introduced in the Introduction to Linux course holds here too: no grantee is given more than the privileges its job requires. Compliance with the principle is not claimed, it is counted. When the granted privileges and the privileges the job requires are written out as two sets, the difference between them can be computed directly.

The script below builds these two sets for the library database, prints the matrix, and counts the differences. It then revokes the excess privileges and prints the matrix again.

cat > privileges.mjs <<'EOF'
// Builds an object-level privilege matrix and counts violations of least privilege.
const permissions = ["SELECT", "INSERT", "UPDATE", "DELETE"];
const objects      = ["member", "book", "loan", "member_summary"];

// Privileges granted with GRANT: "grantee object permission"
const granted = new Set([
  "clerk member SELECT",     "clerk member UPDATE",     "clerk book SELECT",
  "clerk loan SELECT",       "clerk loan INSERT",
  "clerk loan UPDATE",       "clerk loan DELETE",       "clerk member_summary SELECT",
  "reporting member SELECT", "reporting book SELECT",   "reporting loan SELECT",
  "reporting loan DELETE",   "reporting member_summary SELECT",
  "cataloger book SELECT",   "cataloger book INSERT",   "cataloger book UPDATE",
]);

// Privileges the job requires.
const required = new Set([
  "clerk member SELECT",     "clerk member UPDATE",     "clerk book SELECT",
  "clerk loan SELECT",       "clerk loan INSERT",
  "clerk loan UPDATE",       "clerk member_summary SELECT",
  "reporting member_summary SELECT",
  "cataloger book SELECT",   "cataloger book INSERT",   "cataloger book UPDATE",
]);

const grantees = [...new Set([...granted].map((k) => k.split(" ")[0]))];

function report(title, set) {
  console.log(title);
  console.log(("  " + "grantee".padEnd(11) + "object".padEnd(16) + permissions.map((p) => p.padEnd(8)).join("")).trimEnd());
  for (const g of grantees) {
    for (const o of objects) {
      const cell = permissions.map((p) => {
        const v = set.has(`${g} ${o} ${p}`);
        const r = required.has(`${g} ${o} ${p}`);
        return (v ? (r ? "ok" : "EXTRA") : r ? "MISSING" : "-").padEnd(8);
      });
      console.log(("  " + g.padEnd(11) + o.padEnd(16) + cell.join("")).trimEnd());
    }
  }
  const excess = [...set].filter((k) => !required.has(k));
  const missing = [...required].filter((k) => !set.has(k));
  console.log(`  excess privileges granted: ${excess.length}`);
  for (const k of excess) console.log("    " + k);
  console.log(`  missing privileges: ${missing.length}`);
  return excess;
}

const excess = report("== before REVOKE ==", granted);
const after = new Set([...granted].filter((k) => !excess.includes(k)));
report("== after REVOKE ==", after);
EOF
node privileges.mjs

== before REVOKE ==
  grantee    object          SELECT  INSERT  UPDATE  DELETE
  clerk      member          ok      -       ok      -
  clerk      book            ok      -       -       -
  clerk      loan            ok      ok      ok      EXTRA
  clerk      member_summary  ok      -       -       -
  reporting  member          EXTRA   -       -       -
  reporting  book            EXTRA   -       -       -
  reporting  loan            EXTRA   -       -       EXTRA
  reporting  member_summary  ok      -       -       -
  cataloger  member          -       -       -       -
  cataloger  book            ok      ok      ok      -
  cataloger  loan            -       -       -       -
  cataloger  member_summary  -       -       -       -
  excess privileges granted: 5
    clerk loan DELETE
    reporting member SELECT
    reporting book SELECT
    reporting loan SELECT
    reporting loan DELETE
  missing privileges: 0
== after REVOKE ==
  grantee    object          SELECT  INSERT  UPDATE  DELETE
  clerk      member          ok      -       ok      -
  clerk      book            ok      -       -       -
  clerk      loan            ok      ok      ok      -
  clerk      member_summary  ok      -       -       -
  reporting  member          -       -       -       -
  reporting  book            -       -       -       -
  reporting  loan            -       -       -       -
  reporting  member_summary  ok      -       -       -
  cataloger  member          -       -       -       -
  cataloger  book            ok      ok      ok      -
  cataloger  loan            -       -       -       -
  cataloger  member_summary  -       -       -       -
  excess privileges granted: 0
  missing privileges: 0

Five violations came out, and each can be explained by its own reason.

The clerk loan DELETE privilege is a consequence of the previous lesson: deleting a loan record destroys history, and a return is handled by updating the record instead. The clerk has no need for delete access.

The reporting grantee’s read access on three base tables is more interesting. The reporting job does need reading — but not on the base tables, on the member_summary view. When the privilege is granted on the view, reporting still works, and the email addresses on the member table stop being reachable. The previous lesson’s unfinished thought gets completed here: the view defines what is shown, the privilege makes it binding. Each one alone is incomplete.

The reporting loan DELETE privilege cannot be explained by any reason; it is most likely a leftover from copying. As privilege matrices grow by hand, this kind of leftover accumulates and leaves a write path that nobody notices.

In the second matrix, after the revoke, both excess and missing privileges are zero: every grantee’s privileges equal what its job requires. Computing this number on a regular basis is a different thing from claiming the principle is followed.

Summary

• Object-level authorization ties together the grantee, object, and privilege triple; every combination is a separate privilege and has to be granted on its own.
• GRANT grants a privilege, REVOKE takes it back; a privilege can be narrowed at the column level, widened with the grant option, and a revoke can be written to follow branching.
• Roles separate privileges from users; when a job description changes, a single role’s privileges get adjusted and access stays trackable.
• A view defines what gets shown, a privilege makes it binding; the two are used together to narrow access.
• The principle of least privilege is not claimed, it is counted: the difference between granted privileges and the privileges a job requires is a computable quantity.

Course Wrap-Up

This course built SQL’s core statements. The Querying topic covered SELECT structure, conditions, sorting, working with null values, and built-in functions. The Joins and Aggregation topic brought tables together with inner and outer joins, produced summaries with aggregate functions and grouping, and combined result sets with set operations. This topic moved from reading to writing: defining the schema and protecting it with constraints, altering the schema, inserting rows, the risks of updating and deleting, views, and object-level privileges.

One example ran through the entire course — a library database with branches, members, books, and loan transactions. Both queries and write statements were written on the same schema; what a constraint blocks, what an unconditional update does, and what a view does and does not hide were all seen on the same tables.

The next course, Advanced SQL, deepens these statements in three directions. Its Compound Queries topic covers subqueries, common table expressions, recursive queries, and window functions — where in this course a subquery was used only to write a condition, there it becomes a construct in its own right. Its Transactions topic opens up the mechanism underneath the BEGIN-ROLLBACK pair used as a habit in this course: savepoints, isolation levels, locking, and deadlocks. Its Query Performance topic covers indexes, reading a query plan, and the constructs that defeat a plan — alongside the insert difference measured in this course, the read side’s cost model gets added.