Designing Flexible Role-Based Access Control (RBAC) in Relational Databases
A pragmatic model for managing menus, permissions, and hierarchical roles with MySQL and relational joins.
The Challenge of Permission Bloat
Most applications start with a simple boolean `isAdmin`. As business requirements mature, organizations need granular distinctions: who can read reports, who can create invoices, and who can access specific navigation menus.
The 4-Table Normalized Model
A robust RBAC architecture relies on four core entities: 1. **Roles:** e.g., `ADMIN`, `MANAGER`, `DISPATCHER`, `USER`. 2. **Users:** Associated with a primary `role_id`. 3. **Menus / Resources:** Hierarchical menu tree with parent-child relationships and unique resource paths. 4. **Role Permissions:** Granular matrix flags (`can_read`, `can_create`, `can_update`, `can_delete`, `can_report`).
This structure guarantees that altering permissions for an entire department requires only updating the matrix, without modifying any user records.
Zail Yan Zali
Full-Stack Software Engineer
Software engineer specializing in modern TypeScript ecosystems, distributed backend systems, and responsive user interfaces. Focused on code cleanliness, rigorous type safety, and minimal, high-impact user experiences.
Artikel Terkait Lainnya
Eksplorasi tulisan dan catatan arsitektur sistem lainnya
Architecting High-Throughput Web Applications with Next.js & Hono on Bun
A deep dive into decoupling presentation from edge API runtimes, achieving sub-20ms cold starts, and sharing types end-to-end.
Why We Swapped Prisma for Drizzle ORM in Production Workloads
Comparing cold start latencies, binary size overhead, SQL predictability, and developer ergonomic trade-offs between Prisma and Drizzle.
Have a project in mind? Let's build it with clarity.
I am currently open to engineering contracts, full-stack development projects, and architectural advisory.