Designing Scalable Role‑Based Access Control: A Complete Database Blueprint

We commonly use role‑based access control (RBAC), where users are linked to roles and roles to permissions, forming a "user‑role‑permission" model that is usually many‑to‑many.

Roles are collections of permissions; for example, a forum "admin" or "moderator" role aggregates specific actions such as managing posts or users.

When the number of users grows, assigning roles individually becomes cumbersome; introducing user groups allows batch authorization, granting the same permissions to all members of a group with a single assignment.

Permissions can be represented as menu access (page‑level), operation actions (function‑level), file modifications, or UI element visibility, forming a "user‑role‑permission‑resource" model. Storing both operations and resources in a single permission table simplifies modeling and extensibility.

Note: The permission table includes a "PowerType" column to distinguish types such as MENU (menu access), OPERATION (function operation), FILE (file modification), and ELEMENT (UI element visibility).

The benefits of this unified design are twofold: (1) no need to separate operations from resources, and (2) easy extensibility by adding new association tables and defining a new PowerType.

When a permission is linked to a menu, the permission‑menu association, permission‑menu, and menu tables have one‑to‑one relationships; adding a menu therefore requires inserting records into all three tables.

For large systems, role groups can be used to categorize roles without participating in authorization, while user groups handle batch permission assignment.

Data Dictionary

1. User table

2. Role table

3. User‑Role association table

4. User group table

5. User‑Group‑User association table

6. User‑Group‑Role association table

7. Menu table

8. Page element table

9. File table

10. Permission table

11. Permission‑Menu association table

12. Permission‑Page element association table

13. Permission‑File association table

14. Operation table

15. Permission‑Operation association table

16. Role‑Permission association table

17. Operation log table