Enterprise Access Control System Design

Introduction

In large applications, managing user access is very important. We need to control who can access what, when they can access it, and what actions they can perform.

This blog explains a simple and scalable design for an enterprise-level access control system using:

• Users
• Roles
• Permissions
• Departments
• Policies
• Time-based authorization

The design supports both RBAC (Role-Based Access Control) and fine-grained permissions.

Core Concepts

1. User

A user is a person who logs into the system.

2. Role

A role defines a group of permissions. Example: Admin, Manager, Employee.

3. Permission

A permission defines what action is allowed on a module. Example: Read, Write, Delete.

4. Module

A module is a feature or resource in the system. Example: Orders, Reports, Users.

5. Department

Departments help organize users. Example: HR, Finance, Engineering.

Database Design

1. User Table

Stores all user information.

Fields:

• id (PK)
• name
• email (unique)
• password_hash
• department_id (FK → Department.id)
• status (active/inactive)
• created_at
• updated_at

2. Role Table

Defines roles in the system.

Fields:

• id (PK)
• name (unique)
• description
• created_at
• updated_at

3. Department Table

Stores departments.

Fields:

• id (PK)
• name (unique)
• description
• created_at

4. ModulePermission Table

Defines permissions available per module.

Fields:

• id (PK)
• module_name
• action (read/write/update/delete)
• description
• created_at

Pivot Tables

5. UserRole Table

Maps users to roles (many-to-many).

Fields:

• id (PK)
• user_id (FK → User.id)
• role_id (FK → Role.id)
• assigned_at

6. RolePermission Table

Maps roles to permissions.

Fields:

• id (PK)
• role_id (FK → Role.id)
• module_permission_id (FK → ModulePermission.id)
• created_at

7. UserPermission Table

Allows assigning permissions directly to users (override role).

Fields:

• id (PK)
• user_id (FK → User.id)
• module_permission_id (FK → ModulePermission.id)
• effect (allow/deny)
• created_at

8. ModulePermissions (Optional Mapping)

Used when modules are grouped or structured.

Fields:

• id (PK)
• module_name
• parent_module (nullable)
• created_at

Advanced Tables

9. Policies Table

Policies define rules for advanced access control.

Fields:

• id (PK)
• name
• description
• rule_type (attribute-based / condition-based)
• rule_json (stores conditions)
• created_at
• updated_at

Example rule_json:

{
"department": "Finance",
"min_role": "Manager"
}

10. TimeBasedAccessControl Table

Controls access based on time.

Fields:

• id (PK)
• user_id (FK → User.id)
• module_permission_id (FK → ModulePermission.id)
• start_time
• end_time
• timezone
• created_at

Access Evaluation Flow

When a user tries to access a resource:

1. Check if user is active
2. Fetch user roles
3. Get permissions from roles
4. Apply user-specific overrides (UserPermission)
5. Evaluate policies (if any)
6. Check time-based restrictions
7. Final decision: Allow or Deny

Priority Order

1. Deny rules (highest priority)
2. UserPermission overrides
3. RolePermission
4. Policies
5. Time-based rules

Example Scenario

User: John
Role: Manager
Department: Finance

• Role allows: Read Reports
• User override: Deny Delete Reports
• Policy: Only Finance can access Finance Reports
• Time: Access allowed only between 9 AM - 6 PM

Final Result:

• Read Reports → Allowed
• Delete Reports → Denied

Indexing Strategy

To improve performance:

• Index user_id in UserRole, UserPermission
• Index role_id in RolePermission
• Composite index on (user_id, module_permission_id)
• Index module_name in ModulePermission

Best Practices

• Always use deny overrides allow
• Keep permissions small and reusable
• Avoid hardcoding roles in code
• Use caching for permission checks
• Log all access decisions for auditing

Optional Enhancements

• Audit Logs (who accessed what)
• Multi-tenant support
• Hierarchical roles
• API-level permission guards
• UI-based permission control

Conclusion

This design provides:

• Flexibility (user + role permissions)
• Scalability (supports large systems)
• Security (policy + time-based rules)
• Maintainability (clean separation of concerns)

You can start simple with RBAC and gradually add policies and time-based access controls as needed.

End of Document