Managing user access is one of the most important parts of building secure web applications. A Role-Based Access Control (RBAC) system helps control what users can see and do within an application.
In this tutorial, you'll learn how to design a scalable Role & Permission Database Structure from scratch.
What Are Roles and Permissions?
Role
A role is a collection of permissions assigned to users.
Examples:
- Admin
- Manager
- Editor
- Employee
- Customer
Permission
A permission defines a specific action a user can perform.
Examples:
- Create User
- Edit User
- Delete User
- View Reports
- Manage Products
Real-World Example
Imagine an Employee Management System:
| Role | Permissions |
|---|---|
| Admin | All Permissions |
| Manager | View Employees, Approve Leave |
| Employee | View Profile, Submit Leave |
Instead of assigning permissions directly to every user, we assign permissions to roles and roles to users.
Database Structure
A professional RBAC system usually contains these tables:
users
roles
permissions
role_permissions
user_roles
Database Relationship:
Users
|
|
User Roles
|
|
Roles
|
|
Role Permissions
|
|
Permissions
Step 1: Create Users Table
CREATE TABLE users (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(255),
email VARCHAR(255) UNIQUE,
password VARCHAR(255),
created_at TIMESTAMP NULL,
updated_at TIMESTAMP NULL
);
Sample Data:
| id | name |
|---|---|
| 1 | John |
| 2 | David |
Step 2: Create Roles Table
This table stores system roles.
CREATE TABLE roles (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100) UNIQUE,
description TEXT,
created_at TIMESTAMP NULL,
updated_at TIMESTAMP NULL
);
Sample Data:
| id | name |
|---|---|
| 1 | Admin |
| 2 | Manager |
| 3 | Employee |
Step 3: Create Permissions Table
This table stores all available permissions.
CREATE TABLE permissions (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(150) UNIQUE,
created_at TIMESTAMP NULL,
updated_at TIMESTAMP NULL
);
Sample Data:
| id | Permission |
|---|---|
| 1 | create-user |
| 2 | edit-user |
| 3 | delete-user |
| 4 | view-report |
Step 4: Create Role Permissions Table
This is a pivot table connecting roles and permissions.
CREATE TABLE role_permissions (
role_id BIGINT,
permission_id BIGINT,
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
);
Example:
| role_id | permission_id |
|---|---|
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
| 1 | 4 |
| 2 | 4 |
Meaning:
Admin
├─ create-user
├─ edit-user
├─ delete-user
└─ view-report
Manager
└─ view-report
Step 5: Create User Roles Table
Users can have one or multiple roles.
CREATE TABLE user_roles (
user_id BIGINT,
role_id BIGINT,
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
);
Example:
| user_id | role_id |
|---|---|
| 1 | 1 |
| 2 | 2 |
Meaning:
John → Admin
David → Manager
Complete Database Diagram
+-----------+
| users |
+-----------+
| id |
| name |
| email |
+-----------+
|
|
v
+--------------+
| user_roles |
+--------------+
| user_id |
| role_id |
+--------------+
|
|
v
+-----------+
| roles |
+-----------+
| id |
| name |
+-----------+
|
|
v
+------------------+
| role_permissions |
+------------------+
| role_id |
| permission_id |
+------------------+
|
|
v
+--------------+
| permissions |
+--------------+
| id |
| name |
+--------------+
How Permission Checking Works
When a user logs in:
Example
User:
John
Roles:
Admin
Permissions:
create-user
edit-user
delete-user
view-report
Permission Check Logic:
if ($user->hasPermission('create-user')) {
// Allow access
}
Flow:
User
↓
Role
↓
Permission
↓
Access Granted
Multiple Roles Per User
Some systems allow users to have multiple roles.
Example:
John
├─ Manager
└─ Editor
Combined Permissions:
view-report
edit-post
publish-post
This provides greater flexibility.
Advanced Design (Enterprise Level)
Large applications often add extra tables:
departments
teams
modules
user_permissions
Structure:
users
roles
permissions
modules
role_permissions
user_roles
user_permissions
Benefits:
- Module-level security
- Department-based access
- Team-based permissions
- Custom user permissions
Example Modules
| Module | Permissions |
|---|---|
| Users | Create, Read, Update, Delete |
| Products | Create, Read, Update, Delete |
| Orders | Create, Read, Update, Delete |
| Reports | View, Export |
Permission Naming Convention:
users.create
users.read
users.update
users.delete
products.create
products.read
products.update
products.delete
orders.create
orders.read
orders.update
orders.delete
This approach is clean and scalable.
Best Practices
1. Use Pivot Tables
Avoid storing permissions directly in the users table.
✅ Good
users
roles
permissions
❌ Bad
users.permission
2. Use Meaningful Permission Names
Good:
users.create
users.edit
users.delete
Bad:
permission1
permission2
3. Use Database Indexes
CREATE INDEX idx_role_id
ON role_permissions(role_id);
CREATE INDEX idx_permission_id
ON role_permissions(permission_id);
This improves query performance.
4. Cache Permissions
In large systems:
Database → Cache → Application
Popular options:
- Redis
- Memcached
Laravel Recommendation
For Laravel applications, the most popular package is:
Spatie Laravel Permission
Features:
- Role Management
- Permission Management
- Middleware Support
- Blade Directives
- Team Support
- Cache Optimization
Example:
$user->assignRole('Admin');
$user->givePermissionTo('users.create');
$user->hasRole('Admin');
$user->can('users.create');
Conclusion
A well-designed Role & Permission system is essential for application security and scalability. The standard RBAC structure uses five core tables:
users
roles
permissions
role_permissions
user_roles
This design is flexible, easy to maintain, supports multiple roles per user, and can scale from small applications to enterprise systems with thousands of users.
