ASP.NET Core Identity Tables

Back to: ASP.NET Core Identity Tutorials

ASP.NET Core Identity Tables

In this article, I will discuss the ASP.NET Core Identity Tables in detail and try to understand the need and use of each column. Please read our previous article discussing ASP.NET Core Identity Setup step by step.

ASP.NET Core Identity Tables

ASP.NET Core Identity uses a set of default tables to store information regarding authentication, authorization, roles, and user management in your application when using Entity Framework Core as the ORM. The following are the database tables generated by ASP.NET Core Identity:

Let us proceed and understand the tables one by one:

AspNetUsers Table in ASP.NET Core Identity:

This table stores the core user information. It includes details like the ID, User Name, Email Address, Password Hash, Phone Number, and any other custom fields you add to your user model. It also contains additional fields for security and tracking purposes, like Security Stamp, Concurrency Stamp, Two Factor Enabled, and Lockout End, etc. The following are the default columns available in this table:

The columns in the AspNetUsers table generally include the following:

• Id: The primary key for the user. A unique identifier for each user, typically a GUID string.
• UserName: The username of the user. It’s unique and used for identification.
• NormalizedUserName: A normalized version of the username for consistent querying.
• Email: The user’s email address.
• NormalizedEmail: A normalized version of the email for consistent querying, typically in uppercase. This is used in case-insensitive comparisons.
• EmailConfirmed: A boolean value indicates whether the user’s email address has been confirmed.
• PasswordHash: The hashed version of the user’s password. ASP.NET Core Identity uses a secure password hashing mechanism.
• SecurityStamp: A random value indicates whether any security-related user information has changed. For example, it changes when a user changes their password, resets the password, or adds an external login. Its primary purpose is to invalidate any existing sessions or cookies when security-related information changes. This is a security measure to ensure that the old sessions are no longer valid if credentials are compromised and then changed.
• ConcurrencyStamp: A unique value that changes whenever a user profile is updated, used for optimistic concurrency control, ensuring data integrity. In scenarios where multiple attempts to modify the same user record simultaneously, ConcurrencyStamp ensures that changes do not conflict.
• PhoneNumber: The user’s phone number.
• PhoneNumberConfirmed: A boolean value indicates whether the user’s phone number has been confirmed.
• TwoFactorEnabled: A boolean value indicates whether two-factor authentication is enabled for the user.
• LockoutEnd: The date and time when the lockout ends (if the user is currently locked out).
• LockoutEnabled: A boolean value indicates whether the account can be locked out for the user.
• AccessFailedCount: The number of failed login attempts. This is used for lockout functionality.

Customizing the AspNetUsers Table

• Extending IdentityUser: We can extend the IdentityUser class to include additional properties we want to store in the AspNetUsers table, such as First Name, Last Name, DOB, etc. This is done by creating a new class that inherits from IdentityUser and then adding the custom properties.
• Applying Changes via Migrations: After modifying the user model, we need to create and apply a new EF Core migration to update the database schema accordingly.

Managing Users in ASP.NET Core Identity

• The UserManager class in ASP.NET Core Identity interacts with the AspNetUsers table to handle user-related operations like creation, deletion, updating profiles, etc.

AspNetRoles Table in ASP.NET Core Identity

In ASP.NET Core Identity, the AspNetRoles table stores information about roles. Each role has a name and an optional description. It plays an important role in role-based authorization, allowing the users to assign specific roles like Admin, User, Member, Moderator, etc, and grant permissions based on these roles. The following are the columns available in this table:

The columns in the AspNetRoles table generally include the following:

• Id: The primary key for the role. A unique identifier for each role, typically a GUID string.
• Name: The name of the role. This is the human-readable name used in your application code when assigning roles to users or authorizing users based on their roles.
• NormalizedName: The normalized version of the Name field, typically the uppercase version of the role name and is used in case-insensitive comparisons.
• ConcurrencyStamp: A unique stamp that handles concurrent edits to the same role record. It helps maintain data integrity by ensuring concurrent operations do not overwrite changes.

Customizing the AspNetRoles Table

• Extending IdentityRole: Like IdentityUser, we can extend the IdentityRole class to include additional properties in the AspNetRoles table. To do this, we need to create a new class that inherits from IdentityRole and add the custom properties.
• Updating Database Schema: After modifying the role model, we need to create and apply a new Entity Framework Core migration to update the database schema.

Managing Roles in ASP.NET Core Identity

• RoleManager: ASP.NET Core Identity provides the RoleManager class to manage roles. This class creates, deletes, and updates roles and assigns roles to users.

AspNetUserRoles Table in ASP.NET Core Identity:

In ASP.NET Core Identity, the AspNetUserRoles table is a join table that links users to roles. It represents the many-to-many relationship between the AspNetUsers and AspNetRoles tables, indicating which roles are assigned to which users. This association allows users to be assigned multiple roles and, conversely, allows roles to be associated with multiple users. The following are the columns available in this table:

The columns in the AspNetUserRoles table generally include the following:

• UserId: The user ID from the AspNetUsers table. Part of the composite primary key. It corresponds to the Id column in the AspNetUsers table. It acts as a foreign key linking to the AspNetUsers table.
• RoleId: The role ID from the AspNetRoles table. Part of the composite primary key. It corresponds to the Id column in the AspNetRoles table. It acts as a foreign key linking to the AspNetRoles table.

Managing User Roles in ASP.NET Core Identity

• UserManager and RoleManager: ASP.NET Core Identity provides UserManager and RoleManager classes to manage users and roles. These classes offer methods to add and remove roles from users, which internally updates the AspNetUserRoles table.

AspNetUserClaims Table in ASP.NET Core Identity:

The AspNetUserClaims table stores claims for users. Claims are used in claims-based authentication. Claims are a way of adding additional user-specific information that can be used for identity management and authorization in your application. Each claim is a key-value pair associated with a user and can represent various attributes such as email address, full name, role, or any other custom data relevant to your application. The following are the columns available in this table:

The columns in the AspNetUserClaims table generally include the following:

• Id: The primary key for the user claim. It is an integer.
• UserId: The ID of the user associated with this claim. It acts as a foreign key linking to the Id column in the AspNetUsers table.
• ClaimType: The type of the claim (e.g., “birthdate”).
• ClaimValue: The value of the claim (e.g., “1980-01-01”).

Managing User Claims in ASP.NET Core Identity

• UserManager Class: ASP.NET Core Identity provides the UserManager class, which has methods to add, remove, and retrieve claims for a user. 

Note: Storing many claims or very large claim values can impact performance, especially if these claims are included in the authentication token.

AspNetUserLogins Table in ASP.NET Core Identity:

The AspNetUserLogins table stores information about user logins, particularly for users who log in using external authentication providers like Google, Facebook, Twitter, Microsoft, etc. Each record associates a user with an external login provider and a provider key. The following are the columns available in this table:

The columns in the AspNetUserLogins table generally include the following:

• LoginProvider: This column stores the name of the external authentication provider (e.g., Google, Facebook, Microsoft, etc.). It is part of the composite primary key for the table. 
• ProviderKey: This column stores the unique identifier from the login provider for the user. For instance, when a user logs in using Google, this field will store the unique ID assigned to the user by Google. It is also part of the composite primary key.
• ProviderDisplayName: This optional column can store the login provider’s display name (e.g., “Google” instead of “google.com”). It is mainly used for display purposes in the UI. 
• UserId: The local user ID that is linked to this login. It acts as a foreign key linking to the Id column in the AAspNetUsers table. It identifies the user associated with the particular login.

Managing External Logins in ASP.NET Core Identity

• SignInManager Class: ASP.NET Core Identity provides the SignInManager class, which includes methods for handling sign-in and sign-out processes with external authentication providers.

AspNetUserTokens Table in ASP.NET Core Identity:

The AspNetUserTokens table stores tokens for users that can be used for various purposes like password reset, email confirmation, or two-factor authentication. The following are the columns available in this table:

The columns in the AspNetUserTokens table generally include the following:

• UserId: The user ID from the AspNetUsers table. Part of the composite primary key.
• LoginProvider: This column specifies the name of the provider that generated the token. For instance, it could be an internal provider (like a password reset or email confirmation system) or an external authentication provider (like Google, Facebook, etc.) if the token is related to external authentication. It is part of the composite primary key.
• Name: This column stores the name of the token, such as an email confirmation token, password reset token, or two-factor authentication token. It could be something like PasswordReset, EmailConfirmation, AccessToken, etc. It is also part of the composite primary key.
• Value: The value of the token. 

Managing User Tokens in ASP.NET Core Identity

• UserManager Class: ASP.NET Core Identity provides the UserManager class with methods to generate and validate the tokens. 
• SignInManager Class: For operations related to sign-in, particularly in cases involving two-factor authentication, the SignInManager class may interact with this table to handle tokens.

AspNetRoleClaims Table in ASP.NET Core Identity:

Similar to user claims, this table stores claims related to roles. These claims can be attached to roles and are then indirectly associated with users through their roles, enabling role-based authorization checks that depend on specific claims. The following are the columns available in this table:

The columns in the AspNetRoleClaims table generally include the following:

• Id: This is the primary key of the table.
• RoleId: The role ID associated with this claim. A foreign key that links to the Id column in the AspNetRoles table. 
• ClaimType: This column stores the type of the claim, such as Permission, AccessLevel, or any other type that makes sense in the context of your application.
• ClaimValue: The actual value of the claim. For example, if the claim type is Permission, the claim value might be Edit_User or View_Reports, etc.

Managing Role Claims

• RoleManager Class: In ASP.NET Core Identity, the RoleManager class can be used to manage roles and role claims. This includes methods for adding, removing, and retrieving claims for a role.

Note: The structure of the tables is consistent across different database providers (e.g., SQL Server, PostgreSQL, MySQL), but there might be minor differences in types or constraints based on the database system used.

In the next article, I will discuss UserManager, SignInManager, and RoleManager Classes in ASP.NET Core Identity. Here, in this article, I try to explain ASP.NET Core Identity Tables in Detail. I hope you enjoy this ASP.NET Core Identity Tables article.