Relation to users when these are stored in an external Identity provider service

Question

I'm trying to create an API and a website client for it. Lately I've been reading a lot about OAuth2 as a security mechanism and companies that offers authentication as a service such as auth0.com or even Azure active Directory and I can see the advantages in using them

Because I'm used to always having the users in the same database and tables with relationships to the Users table in the form of One to Many such as below

public class User
    {
        public string subjectId { get; set; }

        public virtual List<Invoice> Invoices { get; set; }
        /*
        More properties in here
        */
    }

public class Invoice
    {
        public int InvoiceId { get; set; }
        public string PaymentNumber { get; set; }
        public DateTime Date { get; set; }
        public double Amount { get; set; }
        public string Description { get; set; }

        public virtual User User { get; set; }
    }

My questions is then.

If the users are stored in an external authentication service such as Auth0.com,

• How the Invoice class will handle the relation to the user?
• Would it be just adding a new property subjectId in the Invoice table and this will take the value of whatever id the authentication service assigned?

In the latter case, would the class Invoice be something like below?

public class Invoice
        {
            public int InvoiceId { get; set; }
            public string PaymentNumber { get; set; }
            public DateTime Date { get; set; }
            public double Amount { get; set; }
            public string Description { get; set; }

            public string SubjectId{get;set;}
        }

Also, if the users are stored someplace else, how do you make a query like,

Select * from Users u inner join Invoices i where Users.Name='John Doe' and i.Date>Somedate.

Answer 1

Since you have mentioned Auth0 as your Identity provider there are multiple ways to achieve the user table in your database. 1. Authenticating/ registering the user with Auth0 will send a response with Profile Object which will have all the basic profile information you need. Post this profile object back to your own API to save it to database. This API endpoint should be secured with the access token you received along with the profile object from Auth0. 2. You can create a custom rule in Auth0 that posts the user information back to your api. This rule gets executed on Auth0 server so this is a secure call. 3. Identity providers (Auth0 in our case) are required to expose an API endpoint that gives us user profile data (ex: https://yourdoamin.auth0.com/userinfo). You can make a call to this endpoint from your API to receive the user information.

When user Registers to your application, please use one of these techniques to establish a User profile information table in your database. It is always a good idea to treat the Identity Provider as a service responsible for authenticating the resource owner (the user of your application) and providing an access token for securely accessing your API/ application. If you have the profile of the user in your database, you do not have to depend on the Identity Provider once the user is authenticated.

Please let me know if you have any further questions.

Thank you, Soma.

Answer 2

We have a similar setup for our website. We use Passport for our user database and our website doesn't have a user table at all. This makes life much simpler than having a a bunch of duplicate data between Passport and our website. I'll use our code as an example of what you are doing and hopefully it makes sense.

Our website has a License object that looks like this (Java not C#, but they are similar):

public class License {
  public String companyName;
  public List<User> users;
}

The License table looks like this (trimmed down):

CREATE TABLE licenses (
  id           UUID         NOT NULL,
  company_name VARCHAR(255) NOT NULL,
  PRIMARY KEY (id)
);

The License identifies the users that are associated with it via a join table like this (Passport uses UUIDs for user ids making life simple again):

CREATE TABLE users_licenses (
  users_id    UUID NOT NULL,
  licenses_id UUID NOT NULL,
  PRIMARY KEY (users_id, licenses_id),
  CONSTRAINT users_licenses_fk_1 FOREIGN KEY (licenses_id) REFERENCES licenses (id)
);

Then we can select in either direction. If we know the user id, we can ask for all their licenses like this:

select * from licenses where users_id = ?

Or if we know the license id, we can ask for all the users that have access to the license:

select * from users_licenses where licenses_id = ?

Once we have one or more user ids, we can call the Passport /api/user endpoint or the /api/user/search endpoint to retrieve one or more user objects. We are actually using the Passport Java Client (https://github.com/inversoft/passport-java-client) which makes the API call for us and then returns a List<User>. This is what is stored in the License class from above. That code looks like this:

License license = licenseMapper.retrieveById(licenseId);
List<UUID> userIds = licenseMapper.retrieveUserIdsFor(licenseId);
ClientResponse<SearchResponse, Errors> clientResponse = passportClient.searchUsers(userIds);
license.users = clientResponse.successResponse.users;

LicenseMapper is a MyBatis interface that executes the SQL and returns the License objects. C# ORMs use LINQ, but it would be similar.

The nice thing about this setup is that we don't have a user database table in our website database that we have to keep in sync. Everything is loaded from Passport via the API. We aren't ever concerned about performance either. Passport is on-premise and can do thousands of user lookups each second, so we always load the data instead of caching it.

The only piece of your question that requires additional code is handling the joins when you are searching for arbitrary users like name='John Doe'. The only way to handle this is to query your user database first, retrieve all the IDs, then load their invoices. This seems like it could be dangerous if you have a large user database, but still doable.

That could would look like this in our situation:

UserSearchCriteria criteria = new UserSearchCriteria().withName("John Doe");
ClientResponse<SearchResponse, Errors> clientResponse = passportClient.searchUsersByQueryString(criteria);
List<User> users = clientResponse.successResponse.users;
Set<License> licenses = new HashSet<>();
for (User user : users) {
  licenses.addAll(licenseMapper.retrieveByUserId(user.id));
}