Designing a robust RBAC system – Binary Reflections

RBAC stands for “Roles based Access Control”. It’s a standard system of controlling user access to different entities in a system.

The core idea is to allow or disallow certain actions on entities in the system.
Eg: View and edit on Customer entity or Create on Order entity.

Users, Roles, Permissions

Users: Actual people who will use the system.

Roles: Roles are grouped permissions that are assigned to users.
Eg: Admin, Manager, Author, Intern

Permissions: Permissions allow the ability to perform various actions on the data in a table.
Eg: Actions like View, View Any, Create, Edit, Delete

Assignment

A user can have multiple roles
A user can also have direct permissions
A role can have multiple permissions

Database Schema

Core Tables
- Users
- Roles
- Permissions
- Actions
Junction Tables
- UserRoles (many-to-many)
- RolePermissions (many-to-many)
- UserPermissions

-- 1. Users
CREATE TABLE Users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(100) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE,
    password_hash TEXT
);

-- 2. Roles
CREATE TABLE Roles (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) UNIQUE NOT NULL,
    description TEXT
);

-- 3. Actions
CREATE TABLE Actions (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) UNIQUE NOT NULL,    -- e.g., 'view', 'edit', 'delete'
    description TEXT
);

-- 4. Permissions
CREATE TABLE Permissions (
    id SERIAL PRIMARY KEY,
    action_id INT NOT NULL REFERENCES Actions(id) ON DELETE CASCADE,
    entity VARCHAR(50) NOT NULL,         -- e.g., 'Orders', 'Customers'
    UNIQUE (action_id, entity)
);

-- 5. UserRoles
CREATE TABLE UserRoles (
    user_id INT NOT NULL,
    role_id INT NOT NULL,
    PRIMARY KEY (user_id, role_id),
    FOREIGN KEY (user_id) REFERENCES Users(id) ON DELETE CASCADE,
    FOREIGN KEY (role_id) REFERENCES Roles(id) ON DELETE CASCADE
);

-- 6. RolePermissions
CREATE TABLE RolePermissions (
    role_id INT NOT NULL,
    permission_id INT NOT NULL,
    PRIMARY KEY (role_id, permission_id),
    FOREIGN KEY (role_id) REFERENCES Roles(id) ON DELETE CASCADE,
    FOREIGN KEY (permission_id) REFERENCES Permissions(id) ON DELETE CASCADE
);

-- 7. UserPermissions
CREATE TABLE UserPermissions (
    user_id INT NOT NULL,
    permission_id INT NOT NULL,
    PRIMARY KEY (user_id, permission_id),
    FOREIGN KEY (user_id) REFERENCES Users(id) ON DELETE CASCADE,
    FOREIGN KEY (permission_id) REFERENCES Permissions(id) ON DELETE CASCADE
);

SQL

Core & Junction Tables

RBAC Implementation Logic

Step 1: Identify the user
Eg: charlie (user_id = 3)

Step 2: Determine all their permissions

Permissions assigned to the user via roles
Permissions directly assigned to the user

-- Get all effective permissions for a user
SELECT DISTINCT a.name AS action, p.entity
FROM Permissions p
JOIN Actions a ON p.action_id = a.id

-- Role-based permissions
LEFT JOIN RolePermissions rp ON rp.permission_id = p.id
LEFT JOIN UserRoles ur ON ur.role_id = rp.role_id AND ur.user_id = :user_id

-- Direct user permissions
LEFT JOIN UserPermissions up ON up.permission_id = p.id AND up.user_id = :user_id

-- Include permission only if matched by role or directly
WHERE ur.user_id IS NOT NULL OR up.user_id IS NOT NULL;

SQL

This returns the complete list of what the user is allowed to do. Something like this:

+----------+--------+
|  Action  | Entity |
+----------+--------+
| View     | Orders |
| Create   | Orders |
| View_All | Orders |
+----------+--------+

SQL

Step 3: Check the requested action

Let’s say Charlie (user_id = 3) tries to: view_all on Orders.

Run this query and if it returns a result then Charlie has permission to perform the requested action. If not, he does not have permission.

SELECT 1
FROM Permissions p
JOIN Actions a ON p.action_id = a.id
WHERE a.name = 'view_all'
  AND p.entity = 'Orders'
  AND p.id IN (
    -- All permission_ids the user has
    SELECT permission_id FROM RolePermissions WHERE role_id IN (
      SELECT role_id FROM UserRoles WHERE user_id = 3
    )
    UNION
    SELECT permission_id FROM UserPermissions WHERE user_id = 3
  )
LIMIT 1;

SQL

Step 4: Apply logic in the code

Create a function like this to implement RBAC logic in code:

def user_has_permission(user_id, action_name, entity):
    # Run SQL like the one above
    # Return True/False

Python

Overlapping Roles in RBAC

When a user has multiple roles, they accumulate permissions from all assigned roles. This is known as permission union. RBAC grants access if any assigned role or direct permission allows it.

Notes

Pre-populate the Actions table with all valid actions.

Pre-populate the Permissions table with all valid combinations of actions and entities.

Say you have the following:
Entities: Customers, Products, Orders
Actions: View, View_All, Create, Edit, Delete

Then your Permissions table will look like this:

+----+-----------+-----------+
| id | action_id |  entity   |
+----+-----------+-----------+
| 1  | 1         | Customers |
| 2  | 2         | Customers |
| 3  | 3         | Customers |
| 4  | 4         | Customers |
| 5  | 5         | Customers |
| 6  | 1         | Orders    |
| 7  | 2         | Orders    |
| …  | …         | …         |
+----+-----------+-----------+

# That’s 5 actions × 3 entities = 15 rows in permissions.

Python

Standard Roles:

Admin: Create, Read, Update, Delete and manager users
Manager: Create, Read, Update, Delete, Approve (scope: self & others)
Employee: Create, Read, Update, Submit for Approval (scope: self only)
User: Read only

Usually, transactions are allowed for employee level roles and masters are allowed for manager level roles.

Simple 3-tables RBAC

If the above 7 tables based RBAC system feels like an overkill for a small application then use a simpler 3 tables based RBAC as below:

-- Only 3 core tables:
users(id, username, role_id)
roles(id, name)
role_permissions(role_id, action, entity)

Python

Table users {
  id integer [primary key]
  username varchar [unique, not null]
  email varchar
  password_hash text
  role_id integer [not null]
}

Table roles {
  id integer [primary key]
  name varchar [unique, not null] // e.g., 'Admin', 'Manager'
  description text
}

Table role_permissions {
  role_id integer [not null]
  action varchar [not null]       // e.g., 'view', 'edit'
  entity varchar [not null]       // e.g., 'Orders', 'Customers'
  primary key (role_id, action, entity)
}

Ref: users.role_id > roles.id
Ref: role_permissions.role_id > roles.id

Python

Query to check permissions:

SELECT 1
FROM users u
JOIN role_permissions rp ON u.role_id = rp.role_id
WHERE u.id = :user_id
  AND rp.action = :action
  AND rp.entity = :entity
LIMIT 1;

-- If this returns a row → ✅ permission granted.

Python

Limitations of this 3-table RBAC system:

Each user can have only 1 role
User cannot have direct permissions (role is required)
Works best for apps having less than 5 roles and simple access logic

Build a UI for this 3-tables based system:

Users list view allows assigning a role to the user
Role form shows permissions as a matrix like this:

      | view | edit | delete |
------|------|------|--------|
Orders|  ✔️  |  ✔️  |   ❌   |
Users |  ✔️  |  ❌  |   ❌   |

Python

Users, Roles, Permissions

Assignment

Database Schema

Core & Junction Tables

RBAC Implementation Logic

Overlapping Roles in RBAC

Notes

Simple 3-tables RBAC

You might also like

How to force logout all users from WordPress (no plugin)

Confused between Android & iOS

From cloud backups to offline backups