Disclosure: I build Schemity, a desktop ERD tool - this post is from our blog and uses it for the examples.
TL;DR: A complete multi-tenant RBAC schema needs seven tables: tenants, users, and members for identity, roles and permissions for access, and two junction tables for the N:N relationships between them. This part derives the identity foundation from use cases; Part 2 builds roles and permissions on top and generates the permission list from the routing table.
Multi-Tenant RBAC Data Model, Part 1: Tenants, Users, and Members
A complete multi-tenant RBAC schema is seven tables: tenants, users, and members for identity; tenant-scoped roles and system-wide pems (permissions) for the vocabulary of access; and two junction tables - members_roles and roles_pems - carrying the N:N relationships that make role-based access control work. Here is the finished model:
This part derives the identity foundation - tenants, users, and members, where a member is one user's membership in one specific tenant - from use cases, so you can see why each table has to exist. Part 2, linked at the end, builds roles and permissions on top of it and shows how the routing table becomes the source of truth for what permissions exist.
Behavior Description
Assume we are building a KPI management platform. Multiple companies can create accounts (tenants) and let their employees access the platform.
- A user can be assigned to one or several roles. Their permissions are the sum of those roles' permissions.
- A user cannot access features outside their permissions. Even if they know the API specs and bypass the UI, they will still be blocked at the API layer.
- Admins can change a user's roles or a role's permissions at any time. The only inconvenience is that the affected user needs to log out and log back in for the changes to take effect.
Sounds straightforward, right? Let's look at what happens under the hood.
Deriving the Data Model from Use Cases
Before implementing, I take time to think about what the system needs to do and what data structures are required to support those use cases.
The first thing we hear in the spec is "multiple tenants." A system that serves multiple companies and their users. That gives us at least two objects to start with: tenants and users.
Note: I use the plural convention for table names throughout this post.
Defining the Relationship Between Tenants and Users
Now we have the foundation, but the relationship between tenants and users is not yet defined. To define it, we need to find the right constraint. There are two cases:
- Case 1: One tenant can have many users. One user must use a unique email per tenant.
- Case 2: One tenant can have many users. One user can use the same email across different tenants.
Consider this scenario: you are a contractor working for two different companies that both use this platform. If we go with Case 1, the best you can do is maintain two separate emails for two separate tenants on the same system. That is a real inconvenience.
Notice I used the word person instead of user here. Technically, every tenant is independent. There is no good reason to force a person to use a different email just to become a user of another tenant.
Case 2 is the preferred design.
Tenant Identification at Login
This raises a natural question: if a user can log in with the same email across multiple tenants, how do we know which tenant they belong to?
The answer is that when a user logs in, we need to identify the tenant. There are a few approaches:
- Ask the user to type a tenant code in the login form. Bad idea.
- Provide a typeahead field to help the user find their tenant code. Still a bad idea.
- Give each tenant a subdomain or sub-path, such as
tenant1.ourplatform.comorourplatform.com/tenant1. This is the widely accepted approach, especially the subdomain variant. The only tradeoff is that instead of a standard SSL certificate, you need a wildcard SSL certificate.
The Members Table
Another natural question follows: if users share an email across tenants, can they also share a password?
No. Password policies can differ per tenant, so sharing passwords across tenants is not acceptable. This leads us to a third object: the members table. A member represents a user's membership in a specific tenant, containing tenant's specific information.
The naming is doing real work here. users answers "who is this person?" and members answers "who are they inside this tenant?" - two different questions, so two different tables.
The relationship is clear: one user can have many members, and each member belongs to exactly one tenant. With this simple three-table model - tenants, users, and members - we satisfy the core constraint cleanly: a person can use the same email across multiple independent tenants.
Recognizing the Many-to-Many Relationship
Look closely at the diagram. Both the N (of 1:N) ends from users and tenants point to the members table. This is a sign that users and tenants have an N:N relationship - a user can belong to multiple tenants, and a tenant can have multiple users. The members table is effectively the join table.
With that insight, we can express the data model more precisely:
That is the normalization instinct doing its job, and the reflex that comes with it is to make the two foreign keys the primary key - the pairing itself becomes the row's identity. For a pure join table that is the correct shape. For members it is not, and two signals say so before you write a line of SQL.
A member has a lifecycle of its own. A pure junction row has no state beyond the pairing it records: this role has this permission, and that is the whole story. members is already carrying a password, a failed-login count, and tenant-scoped settings, and it will grow more - invited, active, suspended, removed. The moment a row has its own state and its own history, it is an entity that happens to sit between two others, not a link between them.
Other tables are going to point at it. Roles assigned to a membership, one-time passwords, audit records, anything owned by a person inside a tenant - each one needs a foreign key to the membership. With a composite primary key, every one of those becomes a two-column foreign key, repeated in every child table, forever. And if the pairing ever has to change, everything referencing it has to change with it.
So members gets a surrogate id as its primary key, keeps tenant_id and user_id as ordinary foreign key fields, and enforces the N:N with a composite unique constraint over the pair - one membership per user per tenant. The guarantee is identical; what changes is the answer to "what is this row?" That is the general rule for junction tables: keep the composite primary key for a pure pairing, and give the junction its own id when it has a lifecycle or gets referenced. We will meet the other side of that rule in Part 2, where roles and permissions join through a table that really is nothing but a pairing.
Part 2 builds on this foundation and designs the roles and permissions model - why roles are tenant-scoped but permissions are not, and how your routing table becomes the source of truth for what permissions exist.
Top comments (0)