Skip to content
LLD Learn/Advanced Transactional Modeling
Browsing as a guest. Sign in to save your progress and earn XP as you complete chapters.

RBAC Schema Design

5 min read

You'll learn to

  • -Model roles, permissions and role assignments so access control lives in the schema, not scattered checks
  • -Trace a permission check through the join chain using sample rows
  • -Design ownership (who a portfolio belongs to) alongside role permissions as a separate, composable check
  • -Index for the reverse question, and name common extensions: audit columns and role hierarchy

Role-Based Access Control assigns permissions to roles, and roles to users, rather than assigning permissions directly to individual users - the schema-design mirror of the Interface Segregation Principle from Phase 1: a small, focused set of capability groupings, rather than one giant permission check per user.

An Analogy: Hotel Key Cards

A hotel does not cut a custom key for every guest and member of staff, listing each door they may open. It issues a few types of card: guest, housekeeping, manager. Each type opens a defined set of doors, and a person can hold more than one type. To change who can enter the spa, the hotel edits the card type once, and everyone holding it is updated. Role-based access control is that idea in a database.

  • -A card type is a role.
  • -A door is a permission.
  • -The set of doors a card type opens is the role_permissions table.
  • -A person holding a card is a row in user_roles.

The Three-Table Core

Users, roles, and permissions, joined through two junction tables
CREATE TABLE roles (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL UNIQUE           -- 'broker', 'compliance_officer', 'admin'
);

CREATE TABLE permissions (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL UNIQUE           -- 'execute_trade', 'view_client_portfolio', 'approve_withdrawal'
);

CREATE TABLE role_permissions (         -- many-to-many: roles <-> permissions
    role_id INTEGER NOT NULL REFERENCES roles(id),
    permission_id INTEGER NOT NULL REFERENCES permissions(id),
    PRIMARY KEY (role_id, permission_id)
);

CREATE TABLE user_roles (               -- many-to-many: users <-> roles
    user_id INTEGER NOT NULL REFERENCES users(id),
    role_id INTEGER NOT NULL REFERENCES roles(id),
    PRIMARY KEY (user_id, role_id)
);

Sample Rows

Here is a small data set. Roles: broker (1) and compliance_officer (2). Permissions: execute_trade (1), view_client_portfolio (2) and approve_withdrawal (3). The role_permissions rows are broker to 1 and 2, and compliance_officer to 2 and 3. Users: Priya holds broker; Sam holds both broker and compliance_officer.

  • -Priya's effective permissions: execute_trade and view_client_portfolio.
  • -Sam's effective permissions: the union of both roles, so all three.
  • -If the business decides compliance officers may also execute trades, one new row in role_permissions gives it to every compliance officer, and no user row changes.

A user's effective permissions are the union of every permission attached to every role they hold - checking "can this user execute_trade" becomes a join across user_roles, role_permissions, and permissions, rather than a permission column checked per-user. This is exactly the many-to-many technique from the Relational Modeling module, applied twice, chained together.

Checking a user's effective permission through the role chain
SELECT EXISTS (
    SELECT 1 FROM user_roles ur
    JOIN role_permissions rp ON ur.role_id = rp.role_id
    JOIN permissions p ON rp.permission_id = p.id
    WHERE ur.user_id = :user_id AND p.name = 'execute_trade'
) AS has_permission;

Run that query for Priya and approve_withdrawal, and the join chain finds no row, so has_permission is false. For Sam, user_roles gives roles 1 and 2, role 2 maps to permission 3, and the answer is true.

Indexes for the Reverse Question

The composite primary key on each junction table makes the forward lookup fast: starting from a user, find their roles, then those roles' permissions. A different question is also common: who is allowed to approve withdrawals? That starts from the permission, which is the second column of the role_permissions key, so add an index with the columns in the other order, (permission_id, role_id). It costs a little write speed and keeps audits and admin screens quick.

Ownership: A Different Kind of Access Rule

A broker with the "broker" role can view client portfolios generally, but should typically only manage the specific clients assigned to them - a rule that is not really about roles at all, but about ownership. This needs its own relationship, separate from RBAC: a portfolios table with an assigned_broker_id foreign key, checked alongside (not instead of) the role permission.

Ownership as a separate check layered on top of role permissions
CREATE TABLE client_portfolios (
    id INTEGER PRIMARY KEY,
    client_id INTEGER NOT NULL REFERENCES users(id),
    assigned_broker_id INTEGER REFERENCES users(id),
    total_value DECIMAL(15, 2) NOT NULL DEFAULT 0
);
-- "can this broker view this specific portfolio" combines role AND ownership:
--   has 'view_client_portfolio' permission via role  AND  (is admin OR assigned_broker_id = :user_id)

Keep role-based permissions (what actions a role can perform in general) and ownership (which specific rows a user can act on) as two separate, composable checks. Do not try to encode ownership rules as more and more granular roles, because roles multiply quickly if they try to capture per-row ownership too.

Extensions Worth Mentioning

  • -Audit columns: add granted_at and granted_by to user_roles, so you can answer who gave this person access, and when.
  • -Role hierarchy: a parent_role_id on roles lets a senior role inherit a junior role's permissions, at the cost of a recursive query.
  • -Expiry: an optional expires_at on user_roles supports temporary access, such as covering a colleague's leave.
  • -Caching: permissions rarely change, so cache them per session, remembering that a revoked permission then stays active until the cache expires.

Interview Signal is part of Pro

See a real weak answer next to a real strong one for this exact topic.

Quiz is part of Pro

Test what you just read with a short quiz, and bank the XP.

Ready to Build This?

Design the Broker Registry and Client Portfolios schemas in the LLD Lab's Wolf of Wall Street act.

Discussion0

Join the Discussion

Sign in to leave comments, reply to others, or like insights.

Sign In to ScaleDojo

No comments yet. Be the first to start the thread!