Jump to content

Security - One DB per SaaS tenant

Recommended Posts



I'm building a small SaaS web app that I plan on selling to a handful of local small businesses in the future. I've decided to use the following setup:


- Master DB with user and tenant table.

- Each tenant has their own copy of the app DB.

- Each tenant provides a 'domain' name, which is used to identify multiple users of the same business.


The logon process is:


1. User enters their 'domain' name, username and password to request logon.

2. Logon class makes SELECT query for domain and user in master DB, then returns hashed password if match found.

3. Password verified using popular third party auth library.

4. Domain is used to look up tenant DB connection details that are stored in the master DB tenant table.

5. Returned tenant DB connection details are used from this point onwards to GET/PUT/DELETE data.


By following the above process, each user can only access the DB belonging to their employer, which keeps each set of customer's data segregated from the others.


My problem is ...


Steps 4 and 5 of the logon process seem a little flimsy and insecure. The most secure solution I can think of, would be to encrypt all of the tenant DB connection details in the relevant table and decrypt on the fly as required, so that anybody who gains access to the master DB, can't use the information to access tenant DBs.


My biggest concern is that retrieval of the tenant DB connection details is all down to the domain supplied. If somebody was able to use any valid username/password, but trick my app into providing another tenant's domain during logon, they would immediately be able to access that company's data.


Does the process I've designed appear secure enough if the tenant DB details are encrypted at rest, or am I missing a layer of security that would harden it, please?

Share this post

Link to post
Share on other sites

Wow looks complex, I can see your concern with point 4. Thinking off the cuff could you also store a random string like a key with the login which can be returned at point 2, then also store the key in the tennant db so you could verify the key + domain to verify the lookup? They would then have to spoof the domain and the hidden key?

Edited by BrowserBugs

Share this post

Link to post
Share on other sites

Hmmmmm... that might work. It would certainly add an additional layer.


The authentication library I'm using has the ability to hold additional information within the session, so I could store the key in there and use it to validate each DB access attempt.


I'll have a play around with that and see how it looks.



Share this post

Link to post
Share on other sites

It's certainly a layer and a random string would be extremely hard to guess, and they would also need to know it existed and what it's for in the first place. I'm a bit of a paranoid developer too, always looking to see if it can be tighter and faster, let me know how it pans out.


So refreshing to see quality questions mate, beats the "what's the best CMS" clutter :)

Share this post

Link to post
Share on other sites

Are you using a framework for this?


I've recently made one of Laravel my projects 80% Saas based in about 10 minutes using a global Eloquent query scope.


I'm using Laravel Passport to handle auth. - https://laravel.com/docs/5.5/passport


Seems silly to waste time writing all boilerplate code like that and get on with building something.


The part in your approach that seems odd is storing tennant database connection details in your master table. No sure what the point of that is - is this due to the fact all data access by the tennant is performed via an API?


Also where you say "Each tenant has their own copy of the app DB" - what is reasoning behind this? I understand the standard security related answer, but your app should be able to restrict access to data easily. Or, will each tennant be huge?

Share this post

Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Recently Browsing

    No registered users viewing this page.

  • Member Statistics

    • Total Members
    • Most Online

    Newest Member
  • Forum Statistics

    • Total Topics
    • Total Posts