I have been working on some ASP.NET Core web applications recently that use the Identity APIs for user management, email confirmation, etc. I noticed that the default “Users” table, which is produced by Entity Framework migrations, has some odd choices for column sizes. Since it kind of stinks to be stuck with bad defaults from the framework, I thought I’d write up how to improve this in your new projects that use Identity.

Getting a Basic .NET Core App

As long as you already have .NET Core installed, you can generate a new app with Identity-based user management from the command line like this (copied straight from the docs page linked above):

dotnet new webapp --auth Individual -uld -o WebApp1

Creating the Database Tables

The template comes with a pre-baked, autogenerated migration class (00000000000000_CreateIdentitySchema.cs), which includes the table definitions. Running this command:

dotnet ef database update

Produces the basic database and schema for Identity. This includes a “Users” table called AspNetUsers with the following definition:

CREATE TABLE [dbo].[AspNetUsers]
(
    [Id] [nvarchar](450) NOT NULL,
    [UserName] [nvarchar](256) NULL,
    [NormalizedUserName] [nvarchar](256) NULL,
    [Email] [nvarchar](256) NULL,
    [NormalizedEmail] [nvarchar](256) NULL,
    [EmailConfirmed] [bit] NOT NULL,
    [PasswordHash] [nvarchar](max) NULL,
    [SecurityStamp] [nvarchar](max) NULL,
    [ConcurrencyStamp] [nvarchar](max) NULL,
    [PhoneNumber] [nvarchar](max) NULL,
    [PhoneNumberConfirmed] [bit] NOT NULL,
    [TwoFactorEnabled] [bit] NOT NULL,
    [LockoutEnd] [datetimeoffset](7) NULL,
    [LockoutEnabled] [bit] NOT NULL,
    [AccessFailedCount] [int] NOT NULL,

    CONSTRAINT [PK_AspNetUsers] PRIMARY KEY CLUSTERED ([Id])
)

There are 4 nvarchar(max) columns in the middle of that table definition that are the source of my consternation:

  • PasswordHash
  • SecurityStamp
  • ConcurrencyStamp
  • PhoneNumber

“PhoneNumber” is what initially caught my attention, but really none of these needs to be a “max” anything.

In case you’re not aware, “max” data types can cause query performance and concurrency issues (due to inflated estimates about the data size and needed memory grants).

Better Data Type Choices

What are better choices for each of these columns?

Phone Number

This might be application specific, but it’s generally accepted that fifteen digits is the most you need for a phone number. Strip out special characters before storing. If you need dial strings, extensions, etc - consider putting those in another column.

I get that Identity is part of a general purpose framework, but maybe it could stand to be a little more opinionated in this area. To that end, I think varchar would suffice, as all that will be stored here is digits.

Suggestion: [PhoneNumber] [varchar](15) NULL

Security Stamp

Glancing through the built-in implementation of the IdentityUser object (which is what gets mapped to this table), SecurityStamp is set to a Guid by default:

public IdentityUser()
{
    Id = Guid.NewGuid().ToString();
    SecurityStamp = Guid.NewGuid().ToString();
}

Based on that alone, it seems that uniqueidentifier would be good. However, anytime that stamp gets updated, it’s set using this method:

private static string NewSecurityStamp()
{
    byte[] bytes = new byte[20]; #if NETSTANDARD2_0
    _rng.GetBytes(bytes); #else
    RandomNumberGenerator.Fill(bytes); #endif
    return Base32.ToBase32(bytes);
}

This results in a 32 character string, which is a hash of random bytes. Because of the minor variability, it might be wasteful to use char(36), since the majority of rows over time will only have 32 characters (the stamp gets updated when users change their passwords).

Suggestion: [SecurityStamp] [varchar](36) NOT NULL

Concurrency Stamp

The concurrency stamp is also initialized to a Guid here:

public virtual string ConcurrencyStamp { get; set; } = Guid.NewGuid().ToString();

It remains a Guid across updates as well, so this one is a little less complex.

Ideally we could use uniqueidentifier, but that requires overriding the IdentityUser class and hiding the existing ConcurrencyStamp property. To make this more easily applicable while still improving on nvarchar(max), I’ll go with char(36) in this case due to the C# type being a string.

Suggestion: [ConcurrencyStamp] char(36) NOT NULL

Password Hash

The PasswordHash length is a little trickier to sort out. I took a look at some test data from apps I’ve worked on, and the PasswordHash is always 84 characters.

Looking at the “V3” version of the password hashing code:

private byte[] HashPasswordV3(string password, RandomNumberGenerator rng)
{
    return HashPasswordV3(password, rng,
        prf: KeyDerivationPrf.HMACSHA256,
        iterCount: _iterCount,
        saltSize: 128 / 8,
        numBytesRequested: 256 / 8);
}

private static byte[] HashPasswordV3(string password, RandomNumberGenerator rng, KeyDerivationPrf prf, int iterCount, int saltSize, int numBytesRequested)
{
    // Produce a version 3 (see comment above) text hash.
    byte[] salt = new byte[saltSize];
    rng.GetBytes(salt);
    byte[] subkey = KeyDerivation.Pbkdf2(password, salt, prf, iterCount, numBytesRequested);

    var outputBytes = new byte[13 + salt.Length + subkey.Length];
    outputBytes[0] = 0x01; // format marker
    WriteNetworkByteOrder(outputBytes, 1, (uint)prf);
    WriteNetworkByteOrder(outputBytes, 5, (uint)iterCount);
    WriteNetworkByteOrder(outputBytes, 9, (uint)saltSize);
    Buffer.BlockCopy(salt, 0, outputBytes, 13, salt.Length);
    Buffer.BlockCopy(subkey, 0, outputBytes, 13 + saltSize, subkey.Length);
    return outputBytes;
}

The size of the resulting hash is 13 + 16 (salt size) + 32 (hash function result) = 61 bytes. These bytes are then Base-64 encoded as a string.

According to information on The Internet, the number of characters output by Convert.ToBase64String can be calculated as 61 (inputs bytes) + 2 * (4/3) = 84 bytes (at one byte per character).

In the same file there is a “V2” hash with a lower output size (68 characters).

Considering that changing the hash function would be a “breaking” change anyway (and thus the migrations and defaults could be updated at that time), I don’t see any harm in setting the database field to the smallest size needed.

Suggestion: [PasswordHash] [char](84) NULL

How to Fix It

For a new project, before running the initial migration, delete the included migration files:

  • 00000000000000_CreateIdentitySchema.cs
  • 00000000000000_CreateIdentitySchema.Designer.cs
  • ApplicationDbContextModelSnapshot.cs).

Then, for any project, update the ApplicationDbContext to include this method:

protected override void OnModelCreating(ModelBuilder builder)
{
    base.OnModelCreating(builder);

    builder.Entity<IdentityUser>(u =>
    {                                                                                                                            
        u.Property(user => user.PhoneNumber)
            .IsUnicode(false)
            .IsFixedLength(false)
            .HasMaxLength(15);

        u.Property(user => user.PasswordHash)
            .IsUnicode(false)
            .IsFixedLength(true)
            .HasMaxLength(84);

        u.Property(user => user.ConcurrencyStamp)
            .IsUnicode(false)
            .IsFixedLength(true)
            .HasMaxLength(36)
            .IsRequired(true);

        u.Property(user => user.SecurityStamp)
            .IsUnicode(false)
            .IsFixedLength(false)
            .HasMaxLength(36)
            .IsRequired(true);
    }
}

Then, create a new migration using the command line:

dotnet ef migrations add CreateIdentitySchema -o "Data\Migrations"

Finally, run the DB migrations to get the new table.

Fixing This in ASP.NET Core Identity

It would be nice if this were fixed in the framework / template itself. I was planning to submit an issue, but found that one exists already:

Default Values for IdentityUser

However, it hasn’t gotten a lot of attention. Please go add some 👍 reactions, or comment on the issue, and maybe it will get added to the roadmap!

In the meantime, hopefully the instructions above will be helpful in setting up your new Identity projects for success.