ASP.Net Newbie Questions on Identity RRS feed

  • Question

  • User769259327 posted

    tldr: when does the database actually get queried for functions like register user and login?


    I'm new to web development but somewhat familiar with object oriented programming and sql databases.  I got a big project in mind, but right now just fiddling with things trying to figure them out.

    I'm playing with the Identity namespace to handle user accounts and sessions.  My project will be pretty heavy in database development so, I opened the database it created and started looking through the procedures that were generated.  I'm really wanting to know what procedures are called when so I know how the database is being altered as users interact with the website.

    Here's what's tripping me up... on the C# side, I cannot figure out when the database procedures are executed.  For example, creating a user.

    Create User Stored Procured:

    USE [CTMSasp]
    /****** Object:  StoredProcedure [sys].[sp_adduser]    Script Date: 5/28/2021 7:04:02 PM ******/
    ALTER procedure [sys].[sp_adduser]
    	@loginame       sysname,	    -- user's login name in syslogins
    	@name_in_db     sysname = NULL, -- user's name to add to current db
    	@grpname		sysname = NULL  -- role to which user should be added.
    	set nocount on
    	declare @ret        int
    	if not exists (select * from master.dbo.syslogins where loginname = @loginame
    			and (isntuser = 1 or isntname = 0))
            and @loginame <> 'guest'
            return (1)
        if @grpname is not null and
    	   not exists (select * from sysusers where name = @grpname and issqlrole = 1)
    	    return (1)
        if @name_in_db is null
            select @name_in_db = @loginame
    	-- In Hydra only the user dbo can do this --
        if (not is_member('dbo') = 1)
            dbcc auditevent (109, 1, 0, @loginame, @name_in_db, @grpname , NULL, NULL, NULL, NULL)
    		return (1)
        EXEC @ret = sys.sp_grantdbaccess @loginame, @name_in_db OUT
        if (@ret <> 0)
            return (1)
        -- ADD USER TO ROLE IF GIVEN. NOP FOR 'public' --
        if (@grpname is not null) and (@grpname <> 'public')
            EXEC @ret = sys.sp_addrolemember @grpname, @name_in_db
            if @ret <> 0
    			-- "ROLLBACK" THE ABOVE sp_grantdbaccess --
    			exec sys.sp_revokedbaccess @name_in_db
                return (1)
        -- RETURN SUCCESS --
        return (0) -- sp_adduser

    This seems pretty straightforward but there are a couple things I am not familiar with.

    Register.cshtml.cs OnPostAsync

    public async Task<IActionResult> OnPostAsync(string returnUrl = null)
                returnUrl = returnUrl ?? Url.Content("~/");
                ExternalLogins = (await _signInManager.GetExternalAuthenticationSchemesAsync()).ToList();
                if (ModelState.IsValid)
                    var user = new IdentityUser { UserName = Input.Email, Email = Input.Email };
                    var result = await _userManager.CreateAsync(user, Input.Password);
                    if (result.Succeeded)
                        _logger.LogInformation("User created a new account with password.");
                        var code = await _userManager.GenerateEmailConfirmationTokenAsync(user);
                        code = WebEncoders.Base64UrlEncode(Encoding.UTF8.GetBytes(code));
                        var callbackUrl = Url.Page(
                            pageHandler: null,
                            values: new { area = "Identity", userId = user.Id, code = code, returnUrl = returnUrl },
                            protocol: Request.Scheme);
                        await _emailSender.SendEmailAsync(Input.Email, "Confirm your email",
                            $"Please confirm your account by <a href='{HtmlEncoder.Default.Encode(callbackUrl)}'>clicking here</a>.");
                        if (_userManager.Options.SignIn.RequireConfirmedAccount)
                            return RedirectToPage("RegisterConfirmation", new { email = Input.Email, returnUrl = returnUrl });
                            await _signInManager.SignInAsync(user, isPersistent: false);
                            return LocalRedirect(returnUrl);
                    foreach (var error in result.Errors)
                        ModelState.AddModelError(string.Empty, error.Description);

    This looks to me like the method executed when a user submits the form for new user.  Specifically, these seem like the important lines:

    var user = new IdentityUser { UserName = Input.Email, Email = Input.Email };

    var result = await _userManager.CreateAsync(user, Input.Password);

    So we create a new user object and then pass it, along with a password, to the CreateAsync Method in the UserManager class.  I'm tracking...

    On the aspnet github:

            public virtual async Task<IdentityResult> CreateAsync(TUser user, string password)
                var passwordStore = GetPasswordStore();
                if (user == null)
                    throw new ArgumentNullException("user");
                if (password == null)
                    throw new ArgumentNullException("password");
                var result = await UpdatePassword(passwordStore, user, password).WithCurrentCulture();
                if (!result.Succeeded)
                    return result;
                return await CreateAsync(user).WithCurrentCulture();

    I don't really know what any of that means.  But none of it looks like a command sent to a sql database.  I went through the whole UserManager class and I can't figure out when exactly the database is sent a command.  As I said, it's important because I plan on customizing the database and really want to know exactly what the website will do when to it.

    Saturday, May 29, 2021 12:13 AM

All replies

  • User-821857111 posted

    The sp_adduser procedure you have found there is nothing to do with ASP.NET identity. It is a system stored procedure that belongs to SQL Server. Identity doesn't use stored procedures. By default it uses Entity Framework to connect to the database and execute commands. EF generates SQL on the fly. The UserManager and SignInManager UserStore class contains the code that connects to the database and performs the relevant queries.

    If you want to customise the database, you should use EF migrations to do that: https://docs.microsoft.com/en-us/aspnet/core/security/authentication/customize-identity-model?view=aspnetcore-5.0

    Saturday, May 29, 2021 8:02 AM
  • User769259327 posted

    I'm not going to lie, I'm pretty lost.  I get WHAT you are saying and it makes sense.  I just don't see how it works.  I've been combing through this documentation: AspNetIdentity/src at main · aspnet/AspNetIdentity · GitHub


    By default it uses Entity Framework to connect to the database and execute commands.

    AspNetIdentity/IdentityDbContext.cs at main · aspnet/AspNetIdentity · GitHub

    This is the only file I found that looks like it contains any sql.  Surely, unless a stored procedure is called, there must be a string created that has "INSERT", somewhere?


    The UserManager and SignInManager classes contain the code that connects to the database and performs the relevant queries.

    I spent a lot of time in the UserManager class.  It seems to be doing a lot with Task, which I'm not familiar with.  I don't see anything that looks like db connections I've made, like this example while I was playing around:

            public void Write(string value)
                SqlConnection connection = new SqlConnection("Data Source=DESKTOP-3AI932I\\SQLEXPRESS;Initial Catalog=CTMS;Integrated Security=True");
                //first try
                //vulnerable to injection
                //SqlCommand cmd = new SqlCommand("INSERT INTO [dbo].[testtriggertable]([value]) VALUES ('"+value+"')", connection);
                //second try
                //stored procedure to simplify code and let the sql server do all the thinking
                //also vulnerable to injection
                //SqlCommand cmd = new SqlCommand("execute updatetrigger '" + value + "'", connection);
                //third try
                SqlCommand cmd = new SqlCommand("execute updatetrigger @value", connection);
                var parameter = new SqlParameter("@value", SqlDbType.Text, 50);
                parameter.Value = value;
    //end third try, begin rest cmd.ExecuteNonQuery(); connection.Close(); }

    Are there any other tutorials or documentation you recommend I look through?


    Saturday, May 29, 2021 2:42 PM
  • User-821857111 posted

    Sorry about the confusion - the manager classes don't contain the EF code. That's actually in a UserStore class: https://github.com/dotnet/aspnetcore/blob/main/src/Identity/EntityFrameworkCore/src/UserStore.cs

    You won't see any SQL because the default userstore uses Entity Framework, which generates SQL from LINQ statements at runtime.


    Sunday, May 30, 2021 7:00 AM
  • User769259327 posted

    Thank you Mike.  I've been down a few rabbit holes and starting to piece this together.  This video has been helpful: Adding and Customizing Identity in ASP.NET Core - YouTube

    A couple of frustrations.  1, who ever made this video seems to be on a different version than I am.  Some of the interfaces are slightly different.  I thought I followed his instructions as precisely as I could with the differences, but I still had to spend hours researching error messages and installing nuget packages to get things to work that were very seamless for him.

    2, the more important one.  I tried to jazz up his customizations with a couple of my own.

        public class AppUser : IdentityUser<long>
            public string FirstName { get; set; }
            public string LastName { get; set; }
            public bool Active { get; set; }
            public DateTime DateCreated { get; set; }

    I added a flag to be able to set users active and inactive, and I want to record the date the user was created.

    When the schema was created, I modified the columns to set default values:

                        Active = table.Column<bool>(type: "bit", nullable: false, defaultValue:1),
                        DateCreated = table.Column<DateTime>(type: "datetime2", nullable: false, defaultValueSql:"GETUTCDATE()"),

    Using Sql Server Management Studio, those changes appeared to work as desired.  However, when I created my first user, Active was 0 and DateCreated was 0001-01-01 00:00:00.0000000.  It looks to me like the user was created with a 0 value for both.  How can I tell Entity to just leave those values blank upon a new user, and let the server figure it out?

    Monday, May 31, 2021 3:03 AM
  • User769259327 posted

    Answered my own question, at least frustration #2:

            public bool Active { get; set; }
            public DateTime DateCreated { get; set; }

    Added the "[DatabaseGenerated(DatabaseGeneratedOption.Computed)] " modifier

    Monday, May 31, 2021 3:14 AM