Jump to content


Photo
- - - - -

Security - One DB per SaaS tenant


  • Please log in to reply
4 replies to this topic

#1 DaveWr

DaveWr

    Forum Newcomer

  • Members
  • Pip
  • 2 posts

Posted 14 September 2017 - 10:02 AM

Hi

 

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?



#2 BrowserBugs

BrowserBugs

    Unhinged

  • Privileged
  • PipPipPipPipPip
  • 2,138 posts
  • Gender:Male
  • Location:Surrey, UK
  • Experience:Intermediate
  • Area of Expertise:I'm Learning

Posted 14 September 2017 - 01:31 PM

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, 14 September 2017 - 01:32 PM.


#3 DaveWr

DaveWr

    Forum Newcomer

  • Members
  • Pip
  • 2 posts

Posted 15 September 2017 - 08:44 AM

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.

 

Thanks!!



#4 BrowserBugs

BrowserBugs

    Unhinged

  • Privileged
  • PipPipPipPipPip
  • 2,138 posts
  • Gender:Male
  • Location:Surrey, UK
  • Experience:Intermediate
  • Area of Expertise:I'm Learning

Posted 15 September 2017 - 10:20 AM

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 :)



#5 rallport

rallport

    Laravel 5 Rocks

  • Moderators
  • PipPipPipPipPipPip
  • 5,951 posts
  • Gender:Male
  • Location:England, UK
  • Experience:Web Guru
  • Area of Expertise:Web Developer

Posted 22 October 2017 - 05:33 PM

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? 






0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users