Max Data Types in ASP.NET Core Identity Schema
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.