Roles and Privileges

The course so far has established where data sits and by which path it is read: page layout, the log, index types, partitioning, sharding patterns. Once layout and access paths are in place, the system becomes operational — but it does not yet count as operated. Operations is what keeps a running system secure, recoverable, and accessible over time.

This topic’s first question is the oldest one: who can access this data, and what can they do? The library loan records example makes it concrete. The circulation desk application needs to open loan records; it does not need to delete member records. The reporting tool needs to read loan counts; it does not need to change a row. Without these distinctions, the system can run for years without incident — until a single faulty query or one compromised application key charges, all at once, the price of the distinction that was never made.

Authentication and Authorization

Two questions get tangled together. Authentication is the question of who you are; it was introduced in the context of HTTPS in the How the Internet Works course. Authorization is the question of what you can do, once your identity is settled. Database engines keep the two in separate layers: identity is authenticated when the connection is established, and privilege is checked on every statement.

Object-level privileges were introduced with the GRANT and REVOKE statements in the SQL Fundamentals course. That lesson taught the language; this one teaches how the same statements are organized in operations. The difference is scale: granting privileges one at a time works for ten tables and two users, and it fails at a hundred tables and thirty accounts. The structure that scales is the role.

Role-Based Access Control

Role-based access control ties privileges to duties, not people. The duty is defined first — reader, circulation clerk, cataloger — and privileges are granted to it. A person’s account or an application account is then assigned to the role.

In operations, this means that when an employee changes duties, or an application is retired, the only thing that has to change is the role assignment. If privileges were written onto accounts one at a time, closing off a departing employee’s access becomes a search, and that search commonly comes up incomplete.

The second property of roles is role inheritance — one role encompassing the privileges of another. The cataloger role inherits from the reader role, so a cataloger can do everything a reader can. Inheritance shortens the writing, but it has a side effect: the effective privilege set is no longer visible where it was written, and has to be computed. Seeing what an account can actually reach requires taking the closure of the inheritance chain.

Computing Excess Privilege

The script below models a privilege chart: roles, the roles each role inherits from, the role assignments of each account, and the privileges each account actually needs to do its job. The script follows inheritance to produce the effective privilege set and subtracts out what is more than required.

This is a model, not the privilege chart of a real engine. What is modeled is the closure of inheritance and the difference calculation — these two steps are the same regardless of which engine is in use.

cat > privileges.mjs <<'EOF'
// Role -> directly granted privileges. Privilege format: "action:object".
const ROLE_PRIVILEGES = {
  reader:    ["select:book", "select:branch"],
  clerk:     ["select:member", "select:loan", "insert:loan", "update:loan"],
  cataloger: ["insert:book", "update:book", "delete:book"],
  manager:   ["delete:member", "delete:loan", "alter:*"],
};

// Role -> roles it inherits from.
const ROLE_INHERITANCE = {
  reader:    [],
  clerk:     ["reader"],
  cataloger: ["reader"],
  manager:   ["clerk", "cataloger"],
};

// Account -> assigned roles.
const ACCOUNT_ROLES = {
  circulation_desk:    ["clerk"],
  cataloging_team:     ["cataloger"],
  reporting_tool:      ["clerk"],
  maintenance_account: ["manager"],
};

// Privileges the account actually needs to do its job.
const REQUIRED = {
  circulation_desk:    ["select:book", "select:member", "select:loan", "insert:loan", "update:loan"],
  cataloging_team:     ["select:book", "insert:book", "update:book"],
  reporting_tool:      ["select:book", "select:loan"],
  maintenance_account: ["select:book", "select:member", "select:loan", "alter:*"],
};

function effectivePrivileges(role, seen = new Set()) {
  if (seen.has(role)) return new Set();          // guard against inheritance cycles
  seen.add(role);
  const set = new Set(ROLE_PRIVILEGES[role] ?? []);
  for (const parent of ROLE_INHERITANCE[role] ?? []) {
    for (const priv of effectivePrivileges(parent, seen)) set.add(priv);
  }
  return set;
}

function accountPrivileges(account) {
  const set = new Set();
  for (const role of ACCOUNT_ROLES[account]) {
    for (const priv of effectivePrivileges(role)) set.add(priv);
  }
  return set;
}

const isWriter = (priv) => !priv.startsWith("select:");

console.log("account              | effective | required | extra | extra write privileges");
console.log("---------------------|-----------|----------|-------|------------------------");
for (const account of Object.keys(ACCOUNT_ROLES)) {
  const effective = accountPrivileges(account);
  const required = new Set(REQUIRED[account]);
  const extra = [...effective].filter((i) => !required.has(i)).sort();
  console.log(
    account.padEnd(20) + " | " +
    String(effective.size).padStart(9) + " | " +
    String(required.size).padStart(8) + " | " +
    String(extra.length).padStart(5) + " | " +
    extra.filter(isWriter).join(", ")
  );
}
EOF
node privileges.mjs

account              | effective | required | extra | extra write privileges
---------------------|-----------|----------|-------|------------------------
circulation_desk     |         6 |        5 |     1 | 
cataloging_team      |         5 |        3 |     2 | delete:book
reporting_tool       |         6 |        2 |     4 | insert:loan, update:loan
maintenance_account  |        12 |        4 |     8 | delete:book, delete:loan, delete:member, insert:book, insert:loan, update:book, update:loan

The table shows four separate problems at once.

circulation_desk holds one privilege in excess, and it is a read privilege (select:branch) — a violation of least privilege, but a limited one: unavoidable residue from inheritance.

cataloging_team holds the privilege to delete books, though deletion is not among what is required. A team that corrects catalog entries deleting a record by mistake is an accident that is expensive to repair afterward.

reporting_tool is the clearest case. An account expected to only read holds the privilege to insert and update loan records, through the clerk role. This is the typical outcome of “there was a suitable role, so we granted it”: the role’s name described the job, but its privilege set was broader than the job.

maintenance_account holds twelve privileges, four of which are required. A maintenance account being broad is expected; the problem is not its breadth but that account being used for day-to-day work.

Read-Only Connections

The privilege chart is not the only line of defense. The second layer is a connection-level restriction: the connection is opened without the capability to write. Regardless of which role the reporting tool carries, a write attempt on a connection opened read-only is rejected at the connection layer.

rm -f library.db
sqlite3 library.db <<'SQL'
CREATE TABLE branch(id INTEGER PRIMARY KEY, name TEXT NOT NULL);
INSERT INTO branch VALUES (1,'Central'),(2,'Shore');
SQL

echo "--- writing from a read-only connection ---"
sqlite3 -readonly library.db "INSERT INTO branch VALUES (3,'New');"
echo "exit code: $?"

echo "--- reading from the same connection ---"
sqlite3 -readonly -box library.db "SELECT * FROM branch ORDER BY id;"
rm -f library.db

--- writing from a read-only connection ---
Error: stepping, attempt to write a readonly database (8)
exit code: 8
--- reading from the same connection ---
┌────┬─────────┐
│ id │  name   │
├────┼─────────┤
│ 1  │ Central │
│ 2  │ Shore   │
└────┴─────────┘

The -readonly option and the wording of the error message are specific to this engine; in server-based engines the counterpart is a connection parameter or a session setting. What holds regardless of engine is this: with the write capability switched off at connection time, a mistake in the privilege chart cannot turn into data loss on its own. The two layers exist together; neither substitutes for the other.

Separating Accounts

In operations, three classes of account are kept separate from each other.

The application account performs the day-to-day work. It carries privileges only on the tables the application touches, and only for the actions it performs. It does not carry the privilege to change the schema — application code is not expected to alter table structure at run time.

The report account only reads. Being separate is not only a matter of security: it also lets monitoring tell apart a slow report query consuming resources. This monitoring value comes back in the tenth lesson.

The maintenance account is used for schema changes, backups, and recovery. Its breadth is a requirement of the job; its discipline lies in not being used for day-to-day connections. If it appears in the application’s connection string, every defect in the application gains the capability to turn into schema loss.

The separation has a testable consequence: a connection opened with the application account attempting to drop a table must return a privilege error. Making this attempt once, by hand, turns “we granted the privileges” into “we verified the privileges.”

Privilege Drift Over Time

The privilege chart is correct on the day it is built; every following month it is a little more wrong. A report needs temporary access to one more table, the privilege is granted, the report is retired, the privilege remains. This accumulation is called privilege creep, and its only remedy is regular auditing.

The audit’s concrete form is what the script above did: extract the account’s effective privilege set, compare it against what the job requires, and list the difference. The difference does not have to be zero — leftover privileges from inheritance can be acceptable. What is required is that the difference be known, and that no write privilege appear in it.

The second audit question concerns access paths: while the privilege chart closes off a table, a view exposing that table’s columns may have been left open. Views were introduced in the SQL Fundamentals course; on the operations side, a view must not become a path around privilege checking. The audit covers every accessible object, not just the list of tables.

Summary

• Authentication asks who you are; authorization asks what you can do. Engines check the two in separate layers.
• Role-based access control ties privileges to duties, not people; when a duty changes, only the role assignment changes.
• Role inheritance makes the effective privilege set invisible at the point where it was written; it is computed by taking the closure of the inheritance chain.
• Auditing least privilege means computing the difference between the effective privilege set and what the job requires; no write privilege should appear in it.
• A read-only connection is a second line of defense, independent of the privilege chart.
• Application, report, and maintenance accounts are kept separate; regular auditing counters privilege creep.

Next Step

Every privilege in this lesson was object-level: an account could either read a table or it could not. In the library example, this is not enough — the Shore branch clerk needs to read the loan table, but only the rows of that branch. Showing different accounts a different row set on the same table cannot be expressed with an object-level privilege. The next lesson takes up row-level security, which defines with a policy expression the condition under which a row is visible, and shows how to count the rows a policy leaks.