I am creating an ERD where i have the following strucutre:
- A tenant can have multiple users.
- A user can be in multiple tenants.
- A user has multiple permissions.
What is the best way to also have the permissions of a user scoped per tenant?
I want a user to have permission x,y,z in tenant A but have permission 1,2,3 in tenant B.
Any help on how to model this would be greatly appriciated!
Current part of the ERD
You can model it like this:
There is a Tenant, a User and a Permission which exist independently.
A Tenant has many Users, and a User is in many Tenants, so there exists a many:many TenantUser relationship.
That TenantUser relationship has many Permissions, and a Permission can be associated with many TenantUser relationships, so there exists a TenantUserPermission.
CREATE TABLE Tenant ( TenantId int PRIMARY KEY, Name varchar(100) not null ); CREATE TABLE [User] ( UserId int PRIMARY KEY, Name varchar(100) not null ); CREATE TABLE [Permission] ( PermissionId int PRIMARY KEY, Name varchar(100) not null ); CREATE TABLE TenantUser ( TenantId int REFERENCES Tenant (TenantId), UserId int REFERENCES [User] (UserId), PRIMARY KEY (TenantId, UserId) ); CREATE TABLE TenantUserPermission ( TenantId int, UserId int, PermissionId int REFERENCES Permission (PermissionId), PRIMARY KEY (TenantId, UserId, PermissionId), FOREIGN KEY (TenantId, UserId) REFERENCES TenantUser (TenantId, UserId) );
TenantUserPermission does not reference the original
User tables, it references the
TenantUser table, so there must exist a relationship for there to be permissions on it. You cannot assign permissions to a user before establishing that a user is part of a tenant.
Obviously you don’t have to use surrogate keys, and you would normally name your constraints explicitly, but this gives a general overview.