skip to content

What is the difference between object privileges and system-level privileges in a relational database, and why does the distinction matter when you design access control?

level: middleimportance: must knowfreq 48%

answer

  1. object = named thing + verb; system = database-wide capability
  2. ANY / *.* / global scope = escalation smell
  3. CREATEROLE can grant itself memberships
  4. CREATE on schema = ownership of future objects
  5. BYPASSRLS silently defeats row policies

basics

~20 s

Object privileges permit actions on a specific named object - SELECT on a table, EXECUTE on a function. System privileges permit actions on the database itself or on whole classes of object - creating tables, creating roles, connecting, bypassing policies. Object privileges are narrow and enumerable; system privileges are the escalation-prone ones.

solid answer

~50 s

**Object privileges** attach to a named object and a verb: `SELECT`/`INSERT`/`UPDATE`/`DELETE`/`REFERENCES` on a table, `EXECUTE` on a routine, `USAGE` on a schema or sequence. They are enumerable - you can list exactly which objects a role can touch. **System (or database-level) privileges** are not tied to one object: `CREATE` on a schema or database, `CONNECT`, role-management rights like `CREATEROLE`, and attributes such as superuser or the ability to bypass row-level security. Oracle names these explicitly (`CREATE ANY TABLE`, `SELECT ANY TABLE`) and the 'ANY' family is the classic escalation trap. Why it matters: object privileges bound what a role can reach; system privileges bound what a role can *become*. `CREATEROLE` can grant itself other roles; `CREATE FUNCTION` plus a definer's-rights function is a path to running code as the owner; `SELECT ANY TABLE` defeats every carefully written per-table grant at once. So reviews should scrutinise system privileges hard and treat object privileges as routine.

code

sql · 8 lines
sql
-- object privileges: enumerable, bounded
GRANT SELECT, INSERT ON app.orders TO orders_write;
GRANT EXECUTE ON FUNCTION app.recalc_totals(bigint) TO orders_write;
GRANT USAGE ON SCHEMA app TO orders_write;

-- system-level: review these individually
ALTER ROLE deploy_bot CREATEROLE;        -- can manage roles
GRANT CREATE ON SCHEMA app TO deploy_bot; -- can create and then own new objects

go deeper

for a junior

Give clear examples of each family - SELECT on a table versus the ability to create roles or databases - and say which one everyday grants should use.

for a middle

Explain that system privileges are the escalation-prone family, with concrete cases such as CREATEROLE, ANY-scoped privileges, and CREATE on a schema implying ownership.

for a senior

Add the review and monitoring angle: enumerate system-privilege holders, alert on changes, use predefined monitoring roles, and reason about definer's-rights functions as a code-execution path.

for a principal

Set the policy: which roles may ever hold system privileges, how those grants are approved and audited, and how the model degrades if any single role accumulates them.

## The two families **Object privileges** answer 'what may this role do to *this thing*?' The grant names an object and a verb: - Tables/views: `SELECT`, `INSERT`, `UPDATE`, `DELETE`, `REFERENCES` (create a foreign key pointing at it), `TRIGGER` - Routines: `EXECUTE` - Schemas: `USAGE` (may see objects inside), `CREATE` (may create objects inside) - Sequences: `USAGE`, `SELECT`, `UPDATE` They are finite and auditable. 'Which tables can `reporting` read?' has a definite answer you can query from the catalog. **System privileges** answer 'what may this role do to the *database*?' Examples: connecting to a database at all, creating databases or schemas, creating and altering roles, installing extensions, replication rights, and attributes like superuser or bypassing row-level security. Oracle exposes a large explicit list (`CREATE SESSION`, `CREATE ANY TABLE`, `SELECT ANY TABLE`, `ALTER SYSTEM`); PostgreSQL splits the same ground between role attributes (`SUPERUSER`, `CREATEDB`, `CREATEROLE`, `REPLICATION`, `BYPASSRLS`) and a set of predefined roles for monitoring and maintenance; MySQL calls them global privileges granted at `*.*` scope, plus finer 'dynamic privileges'. ## Why the distinction is a security distinction, not a taxonomy Object privileges compose predictably: two grants give you exactly the union of two narrow capabilities. System privileges frequently compose into *escalation*: - **Role management.** A role that can create and alter roles can usually grant itself memberships, so `CREATEROLE` is close to 'may become most things'. Modern engines have tightened this (PostgreSQL 16 restricts a CREATEROLE role to managing roles it created), but the instinct should remain that role-management rights are near-administrative. - **The ANY family.** `SELECT ANY TABLE` makes every per-table grant irrelevant in one statement. Any privilege whose scope is 'all objects of this kind, including future ones' should be treated as a system privilege regardless of what the engine calls it. - **Code execution.** `CREATE FUNCTION` combined with definer's-rights execution means a role can author code that runs with the owner's privileges. Give someone the right to create functions in a schema owned by a powerful role and you may have given them that role. - **Policy bypass.** A `BYPASSRLS`-style attribute silently defeats row-level security everywhere, so a row-level design is only as strong as the list of roles holding it. - **Extensions and untrusted languages.** Installing an extension or an untrusted procedural language often means running native code in the server process - effectively host-level access. ## Schema `CREATE` is the boundary case `CREATE` on a schema looks like an object privilege - it names a schema - but behaves like a system privilege, because it lets a role introduce new objects that it will then *own*, with all owner rights on them. This is why a runtime application account should not hold `CREATE` on its schema: it turns a data-plane account into one that can define functions, views and tables of its own. ## Designing with the distinction 1. **Default deny on system privileges.** Very few roles should have any. The list is short enough to enumerate in a review; do so periodically and alert on changes. 2. **Express normal access purely with object privileges**, granted to roles, on named objects or on a schema-wide basis that is stable and reviewed. 3. **Watch the scope word.** Anything with 'any', 'all', or a global scope (`*.*` in MySQL, `ANY` in Oracle) deserves the same scrutiny as superuser. 4. **Separate 'can operate' from 'can read data'.** Monitoring and maintenance roles should be able to see statistics and run maintenance without reading application rows; most engines now ship predefined roles precisely for that, so you no longer need to hand out superuser for a monitoring agent. 5. **Audit system-privilege grants specifically.** A new `SELECT` grant on one table is routine noise; a new `CREATEROLE` or `ALTER SYSTEM` grant is an event worth paging on. ## Common interview traps Candidates often say 'privileges are just GRANT statements, they're all the same mechanism'. Syntactically similar, yes - but a per-table `SELECT` grant is bounded and reversible in an obvious way, while a system privilege may be self-amplifying. The habit worth demonstrating is asking, for any grant, 'can the holder use this to obtain privileges they were not given?' - and treating a yes as an administrative decision rather than a routine one.

  • Why is granting CREATE on a schema to an application's runtime account risky, even though it names a single schema?
    Because the account can then create objects that it owns, and owners hold full privileges on their objects including the right to grant them onward. It can also define functions - and with definer's-rights execution, code that runs as its owner. So a narrow-looking grant converts a data-plane account into one that can extend the schema and potentially escalate.
  • A monitoring agent needs query statistics and buffer-cache metrics. Does it need superuser?
    No. Modern engines ship predefined roles for exactly this - PostgreSQL has pg_monitor and its component roles, and MySQL exposes dynamic privileges for monitoring - which grant visibility into statistics views without granting read access to application data or any administrative capability. Handing a monitoring agent superuser is a common and unnecessary escalation.

saying these in an interview costs you the question

  • Treating all grants as equivalent because they use the same GRANT keyword.
  • Granting a monitoring or backup agent superuser instead of the engine's purpose-built predefined roles.
  • Assuming per-table grants still bound a role that also holds a SELECT ANY TABLE-style privilege.
  • Overlooking that CREATE on a schema implies ownership of everything the role subsequently creates there.

context

resolving links…