Database Schema - Multi-Tenant Claim Based Identity for ASP.NET Core - Part 3 of 10

---

This part 3 of 10 part series which outlines my implementation of Multi-Tenant Claim Based Identity. For more details please see my index post.

http://www.spaprogrammer.com/2015/11/multi-tenant-claim-based-identity.html

---

I implemented Multi-Tenant feature on top the ASP.NET Identity Framework. Hence I will be leveraging the Identity tables and in addition I will have my own database tables.

First briefly look at the Identity tables provided by the ASP.NET Identity Framework. Please note that I renamed these table names to fit with rest of my schema naming convention. Also I created these tables under security database schema. I am utilizing database schemas to logically partition my application. For more details, please look at my other blog post Microservices and Database Schemas

Here is a brief description on these tables and how I intended to use them:

IdentityUser (AspNetUsers): This table as the name suggests contains list of users in application. UserName should be unique. Even for multi-tenancy we need to maintain this unique user across all companies. I am planning to use Email address as user name across all tenants.

IdentityUserLogin (AspNetUserLogins): This is used for connecting to different providers such as facebook etc. As this is not my current requirement, I am not using this functionality.

IdentityUserClaim (AspNetUserClaims): This table contains the claims attached to the user. As there will be numerous claims when using claim based access control, using this table to permission each user is hard. Instead I use this table as an override feature to the role based authorization. So if I want to provide additional claims than what roles provides or remove claims from a user than I use this table.

IdentityRole (AspNetRoles): As the name indicates, this contains all the roles. In my application, roles are dynamically created by the end users (administrators). In addition, the system also provides few super user or administrative roles.

IdentityRoleClaim (AspNetRoleClaims): This table persists the claims attached to a role. Similar to the IdentityRole, this table also gets populated when the user assigns claims for a particular role

In addition to the above tables I created the below tables to fit my requirements outlined in my previous post (Requirements - Multi-Tenant Claim Based Identity for ASP.NET Core)

Here are the details and purpose of these additional tables:

UserProfile: This table contains the Users of the system. As we need to support Multi-Tenancy, I added CompanyId as a foreign key to this table.

Company:  This, as the name indicates, contains the Companies in the system. Company is the tenant key for our system. In addition, I am also providing a group company concept, where there will be a parent company and a bunch of child companies. The system needs to permission this concept also.

UserCompany: This is many-to-many relationship between Company and User. In other words, a Company can have multiple users and a User can belong to multiple companies. If a user has permissions to more than one company, the application should allow User to pick a company and work within that company.

IdentityClaim: This table contains all the claims within the system. This is the master data for the claims.

IdentityCompanyClaim: This table stores the permissions for each company. With this table we can selectively assign permissions to each company. For example out of 8 modules, we can assign 6 or 7 modules for a company. Similarly within a module, we can remove certain features.

IdentityPage: This table contains all the controller actions (APIs) within the system. This will be a like a façade layer which keeps list of the features exposed by the system.

IdentityMenuPage: This table helps us to dynamically create the menu.

IdentityPageClaim: This contains the claims required for accessing or invoking each page action method. This table will be used for authorizing the user to call the action methods in the system.

IdentityRoleHierarchy: This table helps us to define the relationship between administrative roles. System assumes that there is parent child relationship within the admin roles. For example, a GroupAdmin role will have CompanyAdmin as children, similarly a CompanyAdmin will have ModuleAdmins as children

Finally here is my DbContext code:

public class SecurityDbContext : IdentityDbContext<ApplicationUser, ApplicationRole, string>

{

    public DbSet<IdentityClaimEntity> IdentityClaims { get; set; }

    public DbSet<IdentityPageEntity> IdentityPages { get; set; }

    public DbSet<IdentityMenuPageEntity> IdentityMenuPages { get; set; }

    public DbSet<IdentityPageClaimEntity> IdentityPageClaims { get; set; }

    public DbSet<IdentityRoleHierarchyEntity> IdentityRoleHierarchies { get; set; }

    public DbSet<IdentityCompanyClaimEntity> IdentityCompanyClaims { get; set; }

    public DbSet<UserProfileEntity> UserProfiles { get; set; }

    public DbSet<UserCompanyEntity> UserCompanies { get; set; }

    public DbSet<CompanyEntity> Companies { get; set; }

    protected override void OnModelCreating(ModelBuilder builder)

    {

        builder.Entity<IdentityRoleHierarchyEntity>(entity =>

        {

            entity.HasKey(e => new { e.RoleId, e.ChildRoleId });

        });

        builder.Entity<UserCompanyEntity>(entity =>

        {

            entity.HasKey(e => new { e.UserProfileId, e.CompanyId });

        });

        base.OnModelCreating(builder);

        //renaming identity tables

        builder.Entity<ApplicationUser>().ToTable("IdentityUser", "security");

        builder.Entity<ApplicationRole>().ToTable("IdentityRole", "security");

        builder.Entity<IdentityRoleClaim<string>>().ToTable("IdentityRoleClaim", "security");

        builder.Entity<IdentityUserClaim<string>>().ToTable("IdentityUserClaim", "security");

        builder.Entity<IdentityUserLogin<string>>().ToTable("IdentityUserLogin", "security");

        builder.Entity<IdentityUserRole<string>>().ToTable("IdentityUserRole", "security");

    }

}