How can I store users and allow them to have the same functionality and interactivity with my database

I am trying to build a bugtracker database in SQL Server written in Entity Framework Core with an ASP.NET Core MVC 2.2 front end. I want to make a login system in Auth0 to connect to the Users table in my database where all my usernames and passwords are stored. This is for the purposes of getting work in IT companies.

I realise now that I can only do this if I was on an Enterprise plan but I likely would not be able to afford that plan. I’ve already tried using the quick start samples and read up about User Profiles. I was originally hopping to have a table of users who’ll have information about them including First name, Last name, whether they are a leader and their contact email address. Also they have a phone number, address, post code, country, mobile number, state, Login name and user password.

1 user will have many registrations and one job will have many registrations. So Registrations will be used as a composite table. So it’s with the Registrations table that I will be able to assign users to a job. So I’m asking what should I do now? Since I know I will have to delete the User table and buy the basic developer package. My question is if it is still possible to create a user profile in Auth0 and allow it to connect to my other tables in my local SQL Server database so that functionality can still be the same? How do I do that, please. I’m sort of unsure as where to start.

Here is a diagram to make more sense of it.

Kind regards,
Jordan Nash

Hi @Jords,

I want to make a login system in Auth0 to connect to the Users table in my database where all my usernames and passwords are stored.

Why do you want/need to keep usernames/password on your end? Is it an option to move that out to an Auth0 user store (Database Connection)?

This is for the purposes of getting work in IT companies.

Are you referring to the fact that IT companies most likely have an Active Directory? Didn’t fully get the meaning behind that argument. Can you clarify, especially how it refers to the sentence prior to this one?

I realise now that I can only do this if I was on an Enterprise plan but I likely would not be able to afford that plan.

Because of which feature? Do you mean because of the need for Custom Database Connection or Enterprise Connection? I don’t see a need for both actually.

My question is if it is still possible to create a user profile in Auth0 and allow it to connect to my other tables in my local SQL Server database so that functionality can still be the same?

That would actually my recommended approach: just standard federation with Auth0 as the central IdP, and in your business logic database tables, you just refer to the Auth0 user based on the user id (which is the sub claim in the ID token you get back from Auth0).

Why do you want/need to keep usernames/password on your end? Is it an option to move that out to an Auth0 user store (Database Connection)?

Because I already developed tables for storing info about the user and it would mean making more changes. But yes it’s an option to move that out to the Auth0 data store because it’s my own project.

Are you referring to the fact that IT companies most likely have an Active Directory? Didn’t fully get the meaning behind that argument. Can you clarify, especially how it refers to the sentence prior to this one?

Sorry I mean I’m integrating my project with Auth0 for employment purposes. I’m just a student.

Because of which feature? Do you mean because of the need for Custom Database Connection or Enterprise Connection? I don’t see a need for both actually.

Yes I would have liked to use Custom Database Connection but I know I can’t afford it because it’s in the enterprise plan. Which means creating a User profile in Auth0.

Luckily I just found the answer here in this ASP.NET Core v3.1: User Profile (quickstart) on how to create it so I assume this will also work for ASP.NET Core 2.2.

I assume these User profiles will allow my application to have the same functionality as it’s always had so I’m just going to create a copy of my database and delete the user table and use the User profiles in Auth0. Thanks.

Luckily I just found the answer here in this ASP.NET Core v3.1: User Profile (quickstart) on how to create it so I assume this will also work for ASP.NET Core 2.2.

Good. That Quickstart using the federated approach I was talking about earlier. It does not store the user passwords on your end.

I assume these User profiles will allow my application to have the same functionality as it’s always had so I’m just going to create a copy of my database and delete the user table and use the User profiles in Auth0. Thanks.

Yes, that’s the recommended approach.

Let us know how it goes.

Hi I’m up to ASP.NET Core v3.1: Authorization https://auth0.com/docs/quickstart/webapp/aspnet-core-3/03-authorization

How would I allow someone with an admin role to access the admin section. The tutorial isn’t working for me. In my HomeController I have added authorisation for both user and admin so it look like this

[Authorize(Roles = "admin")]
[Authorize(Roles = "user")]
public IActionResult Admin()
{
    return View();
}

It’s still not allowing me to access the Admin Section. I would like to know why because I can sign into the app fine with my outlook and gmail accounts and I’ve used the correct tenant for the claims name space which is done in this format. “https://schemas.schemas.dev-rpfdudm5.com”.

UPDATE:

Forgot to input some code here’s mine.

Rule for my app

function (user, context, callback) {

// Roles should only be set to verified users.
if (!user.email || !user.email_verified) {
    return callback(null, user, context);
}

user.app_metadata = user.app_metadata || {};
// You can add a Role based on what you want
// In this case I check domain
const addRolesToUser = function(user) {
    const endsWith = 'jnash51@live.com';

    if (user.email && (user.email.substring(user.email.length - endsWith.length, user.email.length) === endsWith)) {
        return ['admin'];
    }
    return ['user'];
};

const roles = addRolesToUser(user);

user.app_metadata.roles = roles;
auth0.users.updateAppMetadata(user.user_id, user.app_metadata)
    .then(function() {
        context.idToken[`https://schemas.dev-rpfdudm5.com`] = user.app_metadata.roles;
        callback(null, user, context);
    })
    .catch(function (err) {
        callback(err);
    });}

And here is the ConfigureServices method for Startup.cs

public void ConfigureServices(IServiceCollection services)
    {
        services.Configure<CookiePolicyOptions>(options =>
        {
            // This lambda determines whether user consent for non-essential cookies is needed for a given request.
            options.CheckConsentNeeded = context => HostingEnvironment.IsProduction();
            options.MinimumSameSitePolicy = SameSiteMode.None;
        });

        // Add authentication services
        services.AddAuthentication(options => {
            options.DefaultAuthenticateScheme = CookieAuthenticationDefaults.AuthenticationScheme;
            options.DefaultSignInScheme = CookieAuthenticationDefaults.AuthenticationScheme;
            options.DefaultChallengeScheme = CookieAuthenticationDefaults.AuthenticationScheme;
        })
        .AddCookie()
        .AddOpenIdConnect("Auth0", options => {
            // Set the authority to your Auth0 domain
            options.Authority = $"https://{Configuration["Auth0:Domain"]}";

            // Configure the Auth0 Client ID and Client Secret
            options.ClientId = Configuration["Auth0:ClientId"];
            options.ClientSecret = Configuration["Auth0:ClientSecret"];

            // Set response type to code
            options.ResponseType = "code";

            // Configure the scope
            options.Scope.Clear();
            options.Scope.Add("openid");
            options.Scope.Add("profile");
            options.Scope.Add("email");

            // Set the callback path, so Auth0 will call back to http://localhost:3000/callback
            // Also ensure that you have added the URL as an Allowed Callback URL in your Auth0 dashboard
            options.CallbackPath = new PathString("/callback");

            // Configure the Claims Issuer to be Auth0
            options.ClaimsIssuer = "Auth0";

            // Set the correct name claim type
            options.TokenValidationParameters = new TokenValidationParameters
            {
                NameClaimType = "name",
                RoleClaimType = "https://schemas.dev-rpfdudm5.com"
            };

            options.Events = new OpenIdConnectEvents
            {
                // handle the logout redirection
                OnRedirectToIdentityProviderForSignOut = (context) =>
                {
                    var logoutUri = $"https://{Configuration["Auth0:Domain"]}/v2/logout?client_id={Configuration["Auth0:ClientId"]}";

                    var postLogoutUri = context.Properties.RedirectUri;
                    if (!string.IsNullOrEmpty(postLogoutUri))
                    {
                        if (postLogoutUri.StartsWith("/"))
                        {
                            // transform to absolute
                            var request = context.Request;
                            postLogoutUri = request.Scheme + "://" + request.Host + request.PathBase + postLogoutUri;
                        }
                        logoutUri += $"&returnTo={ Uri.EscapeDataString(postLogoutUri)}";
                    }

                    context.Response.Redirect(logoutUri);
                    context.HandleResponse();

                    return Task.CompletedTask;
                }
            };
        });

        // Add framework services.
        services.AddControllersWithViews();
    }

Kind regards,
Jordan Nash