Designing Role-Based Access Control and Permission Data Model

We commonly use role‑based access control (RBAC) where users are linked to roles and roles to permissions, forming a "User‑Role‑Permission" authorization model with many‑to‑many relationships.

A role can be understood as a collection of permissions; for example, a forum’s "admin" or "moderator" roles grant different sets of actions without assigning each permission directly to users.

When the number of users is large, assigning roles individually becomes cumbersome; introducing user groups allows batch authorization, where a user’s effective permissions are the union of personal and group permissions.

Permissions may cover menu access, functional operations, file modifications, or UI element visibility, leading to a "User‑Role‑Permission‑Resource" model. Storing both functional operations and resources in a unified permission table can improve extensibility.

The permission table includes a "PowerType" column to distinguish types such as MENU, OPERATION, FILE, and ELEMENT.

Design benefits include: (1) no need to separate permissions from resources, and (2) easy extensibility by adding new association tables and defining new PowerType values.

Each menu addition requires corresponding entries in the permission table, permission‑menu association table, and menu table; similarly for files, page elements, and operations.

To simplify, the permission table can directly reference the menu ID, using the PowerType field to identify the resource type.

An additional operation log table is added for auditing.

For large systems, role groups can be introduced to categorize roles (distinct from user groups) without participating in authorization, useful for multi‑company or multi‑department scenarios.

Data dictionary tables are provided for users, roles, user‑role associations, user groups, group‑user associations, group‑role associations, menus, page elements, files, permissions, permission‑menu, permission‑element, permission‑file, operations, permission‑operation, role‑permission, and operation logs.

... (additional diagram images omitted for brevity) ...

(End)