ASP.Net Identity - Use custom Schema

Question

I am using MVC5 + Ef6 code first with ASP.Net Identity 1.0 and wish to have the tables created in a custom schema. i.e. a schema that is not the dbo schema.

I reversed engineered my databse using the Ef power tools and set the schema name for all other tables in the mapping class to the following

this.ToTable("tableName", "schemaName");

I tried doing this for the ASP.Net tables but it kept giving me a lots of errors and eventually I gave up. If I exclude the (reverse engineered) ASP.Net Identity tables from my project they will be created but always in the dbo schema

Anyone know how to do this?

Perhaps you can overwrite the classes and use the `TableAttribute` — TGlatzer, Feb 03 '14 at 14:02
For `.NET 6`, check the first part of this answer: https://stackoverflow.com/a/74856286/8644294 — Ash K, Dec 19 '22 at 21:40

score 20 · Accepted Answer · edited Mar 04 '19 at 20:15

20

public class MyDbContext : EntityDbContext<ApplicationUser>
{
    public DbSet<ApplicationUser> Users { get; set; }

    public MyDbContext() : base()
    {
    }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);

        // You can globally assign schema here
        modelBuilder.HasDefaultSchema("schemaName");
    }
}

edited Mar 04 '19 at 20:15

gtu

707
1
10
22

answered Feb 03 '14 at 14:14

Brad Christie

100,477
16
156
200

This works in that it creates the tables in the desired schema, however when I use the roleManager in code it is looking for the tables in the dbo schema. Any Ideas? – martin Feb 03 '14 at 14:46
Ok , I got it working. I had 2 contexts, an IdentityManagerContext and an applicationContext that inherits IdentityManagerContext. I had set the HasDefaultSchema in the applicationContext but should have set it in the OnModelCreating override in the IdentityManagerContext. When I did this it all worked. Thanks – martin Feb 03 '14 at 15:37
@martin: Glad to hear it! ;-) – Brad Christie Feb 03 '14 at 15:47
2

Doesn't this change the default schema for ALL DBSets that may appear within this context? Is there a way to do it for JUST the "ApplicationUser"? – Prisoner ZERO Jun 19 '15 at 01:01
@iokevins base not necessary, per [docs](https://msdn.microsoft.com/en-us/library/system.data.entity.dbcontext.onmodelcreating(v=vs.113).aspx). _The default implementation of this method does nothing, but it can be overridden in a derived class such that the model can be further configured before it is locked down._ – Brad Christie Oct 31 '17 at 19:29
Make sure you create a new migration after adding this and update the model. I was trying to do this from the start and couldn't figure out why update database was still creating the tables in the default dbo schema. – Dan Oct 24 '19 at 18:39

Prisoner ZERO · Answer 2 · 2015-06-21T00:33:33.037

Here is a late entry explaining what I did. Not sure if there is a better way, but this is the ONLY thing that worked for me.

To be fair, I have more than a single model in my context. Which is why this was better for me.

Generate the tables in a database ahead of time (while tables are still in 'dbo')
Execute add-migration on your project and let it create a migration
Change all the schemas within your migration code to the desired schema
Execute update-database to get those changes updated
Delete your original migration file (its' hash is useless to you)
Execute add-migration again and let it create a new migration
Update the OnModelCreating method of your configuration with the code below
Run your application and start registering users

NOTE:
You DO NOT want this.

// This globally assigned a new schema for me (for ALL models)
modelBuilder.HasDefaultSchema("security");

CONFIGURATION: OnModelCreating
This assigned a new schema for ONLY the mentioned tables

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    base.OnModelCreating(modelBuilder);

    modelBuilder.Entity<ApplicationUser>().ToTable("AspNetUsers", "security");
    modelBuilder.Entity<CustomRole>().ToTable("AspNetRoles", "security");
    modelBuilder.Entity<CustomUserClaim>().ToTable("AspNetUserClaims", "security");
    modelBuilder.Entity<CustomUserLogin>().ToTable("AspNetUserLogins", "security");
    modelBuilder.Entity<CustomUserRole>().ToTable("AspNetUserRoles", "security");
}

INITIAL MIGRATION LOOKS LIKE

public partial class Initial : DbMigration
{
    public override void Up()
    {
        CreateTable(
            "security.AspNetRoles",
            c => new
                {
                    Id = c.String(nullable: false, maxLength: 128),
                    Name = c.String(nullable: false, maxLength: 256),
                })
            .PrimaryKey(t => t.Id)
            .Index(t => t.Name, unique: true, name: "RoleNameIndex");

        CreateTable(
            "security.AspNetUserRoles",
            c => new
                {
                    UserId = c.String(nullable: false, maxLength: 128),
                    RoleId = c.String(nullable: false, maxLength: 128),
                })
            .PrimaryKey(t => new { t.UserId, t.RoleId })
            .ForeignKey("security.AspNetRoles", t => t.RoleId, cascadeDelete: true)
            .ForeignKey("security.AspNetUsers", t => t.UserId, cascadeDelete: true)
            .Index(t => t.UserId)
            .Index(t => t.RoleId);

        CreateTable(
            "security.AspNetUsers",
            c => new
                {
                    Id = c.String(nullable: false, maxLength: 128),
                    FirstName = c.String(nullable: false, maxLength: 250),
                    LastName = c.String(nullable: false, maxLength: 250),
                    Email = c.String(maxLength: 256),
                    EmailConfirmed = c.Boolean(nullable: false),
                    PasswordHash = c.String(),
                    SecurityStamp = c.String(),
                    PhoneNumber = c.String(),
                    PhoneNumberConfirmed = c.Boolean(nullable: false),
                    TwoFactorEnabled = c.Boolean(nullable: false),
                    LockoutEndDateUtc = c.DateTime(),
                    LockoutEnabled = c.Boolean(nullable: false),
                    AccessFailedCount = c.Int(nullable: false),
                    UserName = c.String(nullable: false, maxLength: 256),
                })
            .PrimaryKey(t => t.Id)
            .Index(t => t.UserName, unique: true, name: "UserNameIndex");

        CreateTable(
            "security.AspNetUserClaims",
            c => new
                {
                    Id = c.Int(nullable: false, identity: true),
                    UserId = c.String(nullable: false, maxLength: 128),
                    ClaimType = c.String(),
                    ClaimValue = c.String(),
                })
            .PrimaryKey(t => t.Id)
            .ForeignKey("security.AspNetUsers", t => t.UserId, cascadeDelete: true)
            .Index(t => t.UserId);

        CreateTable(
            "security.AspNetUserLogins",
            c => new
                {
                    LoginProvider = c.String(nullable: false, maxLength: 128),
                    ProviderKey = c.String(nullable: false, maxLength: 128),
                    UserId = c.String(nullable: false, maxLength: 128),
                })
            .PrimaryKey(t => new { t.LoginProvider, t.ProviderKey, t.UserId })
            .ForeignKey("security.AspNetUsers", t => t.UserId, cascadeDelete: true)
            .Index(t => t.UserId);

    }

    public override void Down()
    {
        DropForeignKey("security.AspNetUserRoles", "UserId", "security.AspNetUsers");
        DropForeignKey("security.AspNetUserLogins", "UserId", "security.AspNetUsers");
        DropForeignKey("security.AspNetUserClaims", "UserId", "security.AspNetUsers");
        DropForeignKey("security.AspNetUserRoles", "RoleId", "security.AspNetRoles");
        DropIndex("security.AspNetUserLogins", new[] { "UserId" });
        DropIndex("security.AspNetUserClaims", new[] { "UserId" });
        DropIndex("security.AspNetUsers", "UserNameIndex");
        DropIndex("security.AspNetUserRoles", new[] { "RoleId" });
        DropIndex("security.AspNetUserRoles", new[] { "UserId" });
        DropIndex("security.AspNetRoles", "RoleNameIndex");
        DropTable("security.AspNetUserLogins");
        DropTable("security.AspNetUserClaims");
        DropTable("security.AspNetUsers");
        DropTable("security.AspNetUserRoles");
        DropTable("security.AspNetRoles");
    }
}

Much better than using .HasDefaultSchema() just to alter the schema on a small handful of tables — Brendan, Oct 19 '15 at 01:34
Interesting stuff - but why do we have to jump through all these hoops? Surely it should just work as-is? — noelicus, Sep 08 '16 at 08:53
@noelicus You are correct...it WILL work in the default DBO schema. However, what you "may not" understand is WHY someone would want to get OUT of the "DBO" schema. When you put things in DBO you are officially OUT of security-options. Whereas, putting tables in other schema's gives you MANY MORE options and allows you to limit access in creative and succinct ways. — Prisoner ZERO, Sep 12 '16 at 11:53

score 1 · Answer 3 · answered Jul 15 '18 at 22:48

Sorry my english, i use google translator.

Some steps indicated by Prisioner ZERO are not necessary. The indications provided are based on the standard template with individual user accounts security.

First we must verify that our project is clean (insert commands in Package Management Console):

If you already have a database created with the default ASP.NET Identity schema, you have to delete the database with the following command (or delete directly in SQL Server) :

Drop-Database

If you have the default migration of the ASP.NET Identity template, execute the following command to remove it:

Remove-Migration

Now that our project is clean, we must modify the ApplicationDbContext class. We must overwrite the method OnModelCreating to indicate the scheme to which each of the tables generated by ASP.NET Identity will belong. The following link shows the entities used to map each of the tables as well as information about custom builders and options to change the data type of the primary key of each table: Identity Model Customization.

public class ApplicationDbContext : IdentityDbContext {
    public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options) : base(options) { }

    protected override void OnModelCreating(ModelBuilder builder) {
        base.OnModelCreating(builder);
        builder.Entity<IdentityUser>().ToTable("AspNetUsers", "myschema");
        builder.Entity<IdentityRole>().ToTable("AspNetRoles", "myschema");
        builder.Entity<IdentityUserClaim>().ToTable("AspNetUserClaims", "myschema");
        builder.Entity<IdentityUserRole>().ToTable("AspNetUserRoles", "myschema");
        builder.Entity<IdentityUserLogin>().ToTable("AspNetUserLogins", "myschema");
        builder.Entity<IdentityRoleClaim>().ToTable("AspNetRoleClaims", "myschema");
        builder.Entity<IdentityUserToken>().ToTable("AspNetUserTokens", "myschema");
    }
}

Now we only have to generate our migration. For this in the Package Management Console enter the following command (optionally you can indicate the output route with the -OutputDir parameter):

Add-Migration InitialSchemaIdentity -OutputDir Data\Migrations

Then we apply the changes in our database with the command:

Update-Database

ASP.Net Identity - Use custom Schema

3 Answers3

Linked

Related