Complete Permission System Design: RBAC Evolution, Constraints & Database Schemas

Why Permission Management Matters

Permission issues accompany daily work: new hires need network access, code repository rights, monitoring platform login, and operational data queries. Strict permission management protects data security — for example, a payment company's operational backend contains merchant info, legal representative details, transaction records, and fee configurations. If all employees could modify fee rates, accidental changes could cause massive losses. Similarly, leaking merchant data to competitors causes severe harm. Permission management ensures different roles and levels see and operate only appropriate data: financial data for finance, configuration data for operations.

Permission Models

2.1 Permission Design

Permissions categorize into data view, data modification, page, menu, and button permissions. Menus may have multiple levels (e.g., CSDN's two-level menu). Buttons belong to pages. Designing permissions as a tree structure clarifies hierarchy: buttons under second-level menus, second-level under first-level menus. This makes permission requests transparent.

2.2 Why Roles Are Needed

Direct user-permission mapping works for few users but becomes unmaintainable at scale. Each user needs individual permission assignment, wasting admin time and creating complex maintenance. Many users share identical permissions for the same business module. Introducing a role as an intermediary — assigning permissions to roles, then roles to users — yields the classic RBAC (Role-Based Access Control) model. A company with 10,000+ employees might need only hundreds of roles, drastically reducing maintenance.

2.3 Evolution of Permission Models

2.3.1 RBAC Model (RBAC0)

Permissions assigned to roles; users assigned to roles. Many-to-many relationships: user-role, role-permission. Roles bridge users and permissions. This is the most universal model, saving significant maintenance cost.

2.3.2 Role Inheritance RBAC (RBAC1)

Organizational hierarchies require higher-level roles to inherit lower-level permissions plus additional ones. Example: Finance Director inherits Finance Supervisor permissions, who inherits Cashier permissions. Two structural forms:

Tree structure : each child role has one parent; a parent can have multiple children. Common because users typically have one direct manager.

Directed Acyclic Graph (DAG) : child roles can have multiple parents; parents can have multiple children. More flexible but less common.

2.3.3 Constrained RBAC (RBAC2)

Security constraints include:

Role Mutex : a user cannot hold mutually exclusive roles simultaneously (e.g., Accountant and Auditor). If assigned Accountant, must remove Auditor first.

Cardinality Constraints : limit users per role (e.g., only one Super Admin), roles per user, permissions per role.

Prerequisite Constraints : to gain a senior role, user must first hold the junior role (e.g., Tech Lead requires regular Engineer role).

2.4 User Division

2.4.1 User Groups

When many users need the same role (e.g., 500+ customer service staff), assigning individually is tedious. User groups combine users with shared attributes (department, project, job level). Assign role to group; all members inherit. Difference: user group = collection of users; role = bridge between users and permissions . Permission groups similarly bundle permissions (e.g., all query pages and buttons for payment operations) to simplify role-permission assignment.

2.4.2 Organizations

Company org structure drives permission allocation. Members of same organization often share base permissions. Benefits:

Automated permission assignment : new hires placed in org auto-receive org's permissions; transfers auto-adjust permissions.

Data permission control : org members see only their org's data (e.g., Marketing sees scattered clients; Key Accounts sees large clients).

User-org is many-to-many; org-role typically one-to-one (configurable).

2.4.3 Positions

Positions within orgs (e.g., Finance Director, Supervisor, Accountant, Cashier) map to different roles. User-position is one-to-one; position-role is many-to-many.

2.5 Ideal RBAC Model

Combining RBAC0, RBAC1, RBAC2, user groups, organizations, and positions yields a model supporting large-scale, complex business. Relationship cardinalities depend on actual business: org-position usually one-to-many, but many-to-many possible. Not all companies need this complexity upfront — small teams (10-20 people) can use simple user-permission mapping; RBAC suits thousands; ideal model fits larger scale and complexity.

Database Table Design

3.1 Standard RBAC Table Design

Six tables represent User-Role-Permission relationships:

User table

Role table

Permission table

User-Role relation table (many-to-many)

Role-Permission relation table (many-to-many)

ER diagram illustrates these six tables and their foreign keys.

3.2 Ideal RBAC Table Design

Extended model requires more tables to maintain user groups, organizations, positions, role inheritance, and constraints (e.g., role mutex table). ER diagram shows the full schema. Role mutex constraints can be placed on roles or permissions per business needs.

Conclusion

Choose models based on company size, business type, and headcount. Companies under 1,000 people typically suffice with basic RBAC. Over-engineering adds unnecessary complexity. The best model fits your specific context — like design patterns, models solve problems, not for their own sake.