DB structure/architecture with a auth SASS

My team and I are considering using an authentication SASS.

I am definitely sure that the SASS solution will eventually be more secure than the hand made one (even using proper libs) and in our case we can afford the money.

But the thing that makes me hesitate the most is how this service will discuss with the rest of my app. Will it actually simplify our code or make it a more complicated knot bag in the end?

I understand that user list with credentials, and eventual attributes are stored there.

But then, what should I store in my app's (SQL) DB?

Say I have users that belong to companies and other assets on a 1 - n relationship.

I like to write things like:

if current_user.company.assets includes current_user.assets do
  // logic here
end

Should I:

• only store userIds in these tables?

=> But then, I can't user relationships between user attributes and rest of the DB attributes

• store some kind of cached data in a so-called sessions table so I can use it as a disposable table of active users?

=> It feels less secure and implies duplicated content which kind of sucks.

• making the user object virtual loaded with the auth SASS data and use it there.

=> Slightly better, but I can't make queries over users, even company.users is not available

• other proposition?

I'm highly confused on the profits of externalizing what's usually the core object of an app. Am I thinking too monolithically? :-D

Can anyone make suggestions? Even better would be feedback from devs who implemented it.

I found articles on the web, they talk about security and ease of implementation, but don't tackle this question properly.

Cheers, I hope the question doesn't get closed as I'm very curious about the answer.

I'm highly confused on the profits of externalizing what's usually the core object of an app. Am I thinking too monolithically? :-D

Yes, you are thinking too monolithically by assuming that you have to write and control all the code. You have asked a question about essentially outsourcing Authentication to an existing SASS based solution, when you could just as easily write your own. This is a common mistaken assumption that many developers make, especially in the area of Security for applications.

• Authentication is a core requirement for many solutions, but it is very rarely a core aspect or feature of the solution.

By writing your own solution to what is a generally standard concept (Authentication) you have to write, test and maintain your logic, including keeping up to date with latest security trends over the lifetime of the product. In terms of direct Profit/Cost:

• Costs you a lot of time and effort to get it right
• Your own solution will add a layer of technical debt, future developers (internal or external) will need to familiarise themselves with your implementation before they can even start maintenance or improvement work
• You are directly assuming all the risks and responsibilities to maintain the security of the solution and its data.

Depending on the type of data and jurisdiction of your application you may be asked down the track to implement multi-factor authentication or to force all users to re-register to adopt stronger security protocols, this can be a lot of effort for your own solution, or a simple tick of a box in the configuration of your Authentication provider.

Business / Data Schema

You need to be careful to separate the two concepts of Authentication and a User in the business domain. Regardless of where or what methodology you use to Authenticate your users, from a data integrity point of view it is important that there is a User concept in the database to associate related data for each user.

So there is no way around it, your business domain logic requires a table to represent a User in this business domain.

This User table should have an arbitrary Primary Key that is specific to the Application domain, and in that table store the token that that is used to map that business user to the Authentication process. Then throughout your model, you can create FK references back to the user table.

In this way it may be possible for you to map users to multiple different providers, or to easily change the provider with minimal or zero impact on the rest of the business domain model.

What is important from a business process point of view is that the application can resolve the correct business User from the token or claims provided in the response from the authentication provider.

Authentication

If SSO (Single Sign On) is appealing to you then the choice of which Authentication provider to use can become an issue depending on the nature of your solution and the type of users who will be Authenticating. If the solution is tenanted to other businesses and offers B2B, or B2C focused activities then an Enterprise authentication solution like Azure AD, or Google Cloud Identity might make sense. You would register your product in the client's authentication domain so that they can manage their users and access levels.

If the solution is more public focussed then you might consider other social media Authentication providers as a means of simplifying Authentication for users rather than forcing them to use your own bespoke Authentication process that they will invariably forget their password too...

You haven't mentioned what language or runtime you are considering, however if you do choose to write your own Authentication service, as a bare minimum you should consider implementing an OAuth 2.0 implementation to ensure that your solution adheres to standard practises and is compatible with other providers chould you choose to use them later.

In a .NET based environment I would suggest Identity Server 4 as a base level of security, there are a lot of resources on implementation, other frameworks should have similar projects or providers that you can host yourself. The point is that by using a standard implementation of your own Authentication Service, rather than writing your own one that is integrated into your software you are not re-inventing anything, there is a lot of commercial and community support available to help you minimise the effort and cost to get things up and running.

Conclusion

Ultimately, if you are concerned with Profit, and lets face it most of us are, then the idea that you would re-create the wheel, just because you can adds a direct implementation and long term maintenance Cost and so will directly reduce Profitability, especially when the effort to implement existing Authentication providers into your solution is really low.

Even if you choose today to implement your own Authentication Service, it would be wise to implement it in such a way that you could easily offload that workload to an external provider, it is the natural evolution of security for small to mid sized applications when users start to demand more stringent security requirements or additional features than it is cost effective to provide in your native runtime.

Once security is implemented in your application the rest of the business process generally evolves and we neglect to come back and review authentication until after a breach, if we or the client ever detect such an event, for this reason it is important that we get security as right as we can from the very start of a solution.

Whilst not directly related, this discussion reminds me of my faviourite quote from Eric Lippert in a comment on an SO blog

Eric Lippert on What senior developers can learn from beginners
...The notion that programming can be principled — that we proceed by understanding the abstractions afforded by the language, and then match those abstractions to a model of the business domain of the program — is apparently never taught to a great many programmers. Rather, many programmers proceed as though they’re exploring an undiscovered country, and going down paths more or less at random and hoping they end up somewhere good, no matter how twisted the path is that gets them there...

One of the reasons that we use external Authentication Providers is that the plethroa of developers who have come before us have already learnt the hard lessons on what to do, or not to do and have evolved a set of standards and protocols to provide best practice guidelines on how to protect our users and their data when they are using our software. Many of these external providers represent best practice implementations and they maintain them for us as the standards continue to evolve, so that we don't have to.