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?