In a relational database, what is the difference between a user and a role, and why do teams attach privileges to roles instead of granting them directly to each user?
answer
- user = role with LOGIN (Postgres); Oracle keeps them separate
- privileges on roles, roles on identities
- object roles vs business roles, two layers
- onboarding/offboarding = one statement
- reviewability: who can read this table?
basics
~20 sA user is an identity that can log in; a role is a named collection of privileges that can be granted to users or to other roles. Attaching privileges to roles means access is defined once per job function, and adding or removing a person is a single membership change instead of dozens of grants.
solid answer
~50 sA user (or login) is an authenticated identity. A role is a named privilege container. In most modern engines they are literally the same object type with one difference - whether it may log in - so 'user' really means 'a role with the LOGIN attribute'. The reason to grant to roles is maintenance and reviewability. Define `app_readwrite`, `reporting`, `dba_readonly` once, grant object privileges to those, and then grant role membership to people and services. Onboarding is one statement; offboarding is one statement; and answering 'who can read the customer table?' means listing one role's members rather than scanning every user's grants. Granting directly to users produces privilege sprawl: rights accumulate as people change teams, nobody dares revoke anything because they cannot tell what depends on it, and the effective permission set becomes unknowable. Roles turn access control into something you can review as a set of job functions.
code
sql · 8 linesCREATE ROLE orders_read NOLOGIN;
GRANT SELECT ON app.orders, app.order_items TO orders_read;
CREATE ROLE analyst NOLOGIN;
GRANT orders_read TO analyst;
CREATE ROLE alice LOGIN PASSWORD '...';
GRANT analyst TO alice; -- onboarding is one statementgo deeper
Define both terms and give the maintenance argument: privileges on roles, membership for people, so onboarding and offboarding are single statements.
Add the two-layer pattern (object roles versus business roles) and note engine differences such as PostgreSQL treating users as roles with LOGIN.
Frame it as reviewability and drift control, describe managing roles as code in migrations, and explain how direct grants decay into an unknowable effective privilege set.
Discuss the organisational model - who owns role definitions, how access review works, how role design maps to teams and data classification without exploding in count.
## Two concepts, often one object A **user** is an identity the database authenticates - it has credentials and can open a session. A **role** is a named bundle of privileges that can be granted to identities or to other roles. Engines differ in how separate these are. In PostgreSQL there is a single object type, `ROLE`; `CREATE USER` is just `CREATE ROLE ... LOGIN`. In Oracle, users and roles are distinct object kinds, and roles cannot log in. In MySQL, roles were added later on top of user accounts (8.0+), and an account is identified by user *and* host. The conceptual model is the same everywhere: identity is one thing, privilege bundling is another, and a good design keeps them separate even when the engine merges the underlying type. ## Why bundle at all Suppose an analytics team of ten needs `SELECT` on forty tables. Granting directly means 400 grant statements, repeated for every new hire, and 400 revokes when someone leaves - which in practice means the revokes never happen completely. Now define one `reporting` role, grant it `SELECT` on the forty tables, and grant `reporting` to each analyst. Onboarding: one statement. Offboarding: one statement. Adding a forty-first table: one grant, and everyone gets it. The deeper benefit is **reviewability**. Access control is only as good as your ability to answer 'who can do what?'. With user-level grants, the answer is a query over a sprawling grants table with no structure. With roles named after job functions, the answer is a short list, and reviewers can reason about whether the *function* should have that access rather than whether a specific person should. ## The two-layer pattern A convention that scales well distinguishes: - **Object roles** (sometimes 'permission roles'): `orders_read`, `orders_write`, `billing_read`. These hold actual object privileges and map to data, not to people. - **Business roles**: `analyst`, `support_agent`, `billing_service`. These hold no direct object privileges; they are granted membership in the object roles they need. People and services get business roles only. When a new table appears, you update object roles; when a job changes, you update business roles. The two axes stop interfering with each other, which is what prevents the sprawl. ## Privilege sprawl, concretely Direct grants decay in a specific way: someone gets access for an incident, it is never revoked; someone moves teams and keeps both sets; a service is granted extra rights during a migration and the grant outlives the migration. Nobody removes anything because revoking an unknown-purpose grant risks breaking production. Over a few years the effective privilege set of a mature system is strictly larger than anyone intended, and it is not documented anywhere. Roles do not prevent this by magic, but they make the accumulation visible - an unexpected role membership stands out, while one extra row in a grants table does not. ## What roles are not A role is not an authentication mechanism - it holds no credentials in most engines (a role with LOGIN does, but that is the user aspect). A role is not automatically active in every engine: some require explicit activation of a granted role in the session before its privileges apply. And role membership is not the same as ownership: being a member of the role that owns a table gives you the owner's rights only through that membership, which is a different relationship from owning it yourself. ## Practical guidance - Name roles after *what they permit* or *what job needs them*, not after people or teams that will be renamed. - Grant object privileges to roles only; grant roles to identities. Treat a direct user grant as an exception that needs a reason. - Keep the number of roles proportionate to genuinely distinct access needs - a role per person is just user grants with extra steps. - Manage roles and their grants as code, in migrations, so they are reviewed and reproducible across environments.
- Is 'user' a distinct object type from 'role' in the engines you know?It varies. PostgreSQL has one object type, ROLE, where a user is simply a role with the LOGIN attribute - CREATE USER is an alias. Oracle keeps users and roles as separate kinds and roles cannot connect. MySQL added roles on top of accounts in 8.0, where an account is identified by user and host. The design principle - identity separate from privilege bundle - holds regardless.
- When is granting a privilege directly to a user actually justified?Rarely, and it should be time-boxed: a break-glass grant during an incident, or a one-off that will be revoked the same day. Anything that recurs describes a job function and belongs in a role. If direct grants persist, they become invisible accumulations that nobody can safely revoke later.
A role is a job title with a badge that opens certain doors. You don't re-issue every door permission to each new hire; you give them the title, and the doors follow.
saying these in an interview costs you the question
- Believing roles are only for humans and services must get direct grants - service accounts benefit from the same structure.
- Creating one role per person, which reproduces user-level sprawl with extra indirection.
- Assuming that being granted a role always means its privileges are active in the session - some engines require the role to be activated or are configured not to inherit.
- Treating role membership as equivalent to owning the objects the role can access.