Attitude to users when they are stored in an external identity provider service

Question

I am trying to create an API and a website client for it. Recently, I read a lot about OAuth2 as a security mechanism and companies offering authentication as a service, for example auth0.com , or even Azure active Directory, and I see the advantages in using them.

Since I’m used to always have users in the same database and tables with relations to the Users table in the form from one to many, for example 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; } } 

Now my questions.

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

• How will the Invoice class handle user attitudes?
• Would it just be adding a new propertyId to the Invoice table, and that would take the value of any identifier assigned by the authentication service?

In the latter case, will the class invoice be lower?

 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 users are stored somewhere else, how do you make a request, for example,

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

Answer 1

Since you mentioned Auth0 as an identity provider, there are several ways to reach the user table in your database. 1. Authentication / user registration using Auth0 will send a response with a profile object that will have all the necessary profile information. Send this profile object back to your own API to store it in the database. This API endpoint must be protected by the access token that you received with the profile object from Auth0. 2. You can create your own rule in Auth0, which sends user information back to your api. This rule is executed on the Auth0 server, so this is a secure call. 3. Identity providers (Auth0 in our case) must disclose an API endpoint that gives us user profile information (for example: https://yourdoamin.auth0.com/userinfo ). You can call this endpoint from your API to get user information.

When a user logs into your application, use one of these methods to create a table of user profile information in your database. It is always recommended that you consider the Identity Provider as the service responsible for authenticating the owner of the resource (user of your application) and providing an access token for secure access to your API / application. If you have a user profile in your database, you do not need to depend on the identity provider as soon as the user is authenticated.

Please let me know if you have further questions.

Thanks, Soma.

Answer 2

We have a similar setting for our site. We use Passport for our user database, and our website does not have a user table at all. This makes life much easier than having multiple duplicate data between the passport and our website. I will use our code as an example of what you are doing, and hopefully this makes sense.

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

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

The license table looks like this (cropped):

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

The license identifies users who are associated with it using a connection table similar to this one (Passport uses UUIDs for user identifiers that make 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 choose in any direction. If we know the user ID, we can request all of their licenses as follows:

 select * from licenses where users_id = ? 

Or, if we know the license identifier, we can ask all users who 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 or / api / user / search endpoint to retrieve one or more user objects. We actually use the Passport Java Client ( https://github.com/inversoft/passport-java-client ), which forces the API to call us, and then returns a List<User> . This is what is stored in the License class on top. This code is as follows:

 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 SQL and returns License objects. C # ORMs use LINQ, but that would be similar.

The nice thing about this setup is that there is no user database table in our database that we need to synchronize. Everything is loaded from the passport through the API. We don't even care about performance. The passport is local and can execute thousands of user requests every second, so we always load data, not cache it.

The only part of your question that requires additional code handles connections when you search for users like name='John Doe' . The only way to handle this is to first query your user database, get all the identifiers, and then upload their invoices. This seems to be dangerous if you have a large user database, but still possible.

In our situation, it might look like this:

 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)); }