RBAC Schema Design
You'll learn to
- -Model roles, permissions, and role assignments so access control lives in the schema, not scattered application checks
- -Design ownership relationships (who a portfolio or account belongs to) alongside role-based permissions
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.
The Three-Table Core
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)
);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.
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;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.
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 rather than trying to encode ownership rules as more and more granular roles - roles multiply quickly if they try to capture per-row ownership too.
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.
Design the Broker Registry and Client Portfolios schemas in the LLD Lab's Wolf of Wall Street act.