Role Based Access Control Table Design
A good role based access control table design uses five core tables to manage rights. You link users to roles and map those roles to specific permissions through join tables. Most engineering teams start by building a basic role column right on the user table. That simple setup breaks down fast when one person needs multiple job roles at once. Teams then grant broad admin roles to fix access blocks, which creates major security risks. Core tables in a role based access control table design A
Role-Based Access Control (RBAC) + DevSecOps Pipeline Design: the complete guide
Architecture patterns, implementation strategies, and security best practices. Delivered to your inbox.
A good role based access control table design uses five core tables to manage rights. You link users to roles and map those roles to specific permissions through join tables. Most engineering teams start by building a basic role column right on the user table. That simple setup breaks down fast when one person needs multiple job roles at once. Teams then grant broad admin roles to fix access blocks, which creates major security risks.
Core tables in a role based access control table design
A clean schema separates people from the tasks they can run in the app. You start with three base tables for users, roles, and permissions. The users table holds account details like email addresses and login status. The roles table lists job functions such as billing manager, support agent, or viewer. The permissions table defines exact actions like reading invoices or deleting user accounts.
Next, you add two join tables to connect these three base tables together. The user roles table pairs a user ID with a role ID. This lets one person hold several roles across the team at the same time. The role permissions table pairs a role ID with a permission ID. When a person logs in, the app reads these two join tables to check rights.
Adding resource scope and role groups to the schema
Flat roles work well for small apps with one shared workspace. Growing apps need to limit roles to one project, team, or customer account. You can add a group or tenant ID column to the user roles table. This column scopes a role so a manager only controls their own team data. Without a scope column, you must create hundreds of duplicate roles for each customer.
Some teams also build role hierarchies to cut down on repetitive permission mapping. In this model, a parent role passes its rights down to a child role. You track this by adding a parent role column on the roles table. Keep this tree shallow so queries stay fast when the app checks user rights. Deep role trees slow down login checks and make audits hard to read.
Role-Based Access Control (RBAC) + DevSecOps Pipeline Design: architecture patterns and best practices
Continue reading? Get the full guide.
Free. No spam. Unsubscribe anytime.
Where database role tables fall short in daily operations
This five-table setup solves access control inside your own app screens and menus. It gives product teams a clear way to check who can click a button. You can also audit which app users hold admin rights by querying the join tables. For product features, a solid relational schema is the right starting point.
Still, app tables do not control engineers who query the database directly. Database engines use their own internal role systems to check who can log in. Once a user logs in with a read role, the database returns full rows. Native database roles decide who connects, not what individual queries do in flight. They do not rewrite sensitive fields like emails, phone numbers, card numbers, or national IDs. They also cannot ask a team lead for approval before a drop query runs.
Govern statements and responses across databases and APIs
Good config and scoped roles are needed setup. They do not check single queries in flight or mask sensitive fields in returned rows. Across Postgres, MySQL, SQL Server, MongoDB, ClickHouse, Kubernetes APIs, SSH, and internal HTTP endpoints, hoop.dev runs beside each supported service as an open-source proxy, a sidecar for your database or API.
First, hoop.dev guardrails block banned queries before they run. Then hoop.dev data masking rewrites matched sensitive values in responses in memory. The audit trail records every statement and verdict per resource. Furthermore, hoop.dev holds SOC 2 Type II and is open source on GitHub.
Frequently asked questions
How many tables do you need for role based access control?
A standard setup uses five tables in total. Three tables store users, roles, and permissions as separate records. Two join tables link users to roles and connect roles to permissions.
Should you assign permissions straight to a user account?
Avoid linking permissions straight to users in your main schema. Direct links make audits messy when people change jobs or leave the team. Always group permissions inside a role and assign that role to the user.
How do you handle multi-tenant roles in the database?
Add a tenant or workspace ID to the user roles join table. This lets a user hold an admin role in one workspace and a viewer role in another. Every permission check then filters by both the user ID and the active workspace ID.