2

In a multi tenant application with separated databases for each tenant, what would be the best way to authenticate connection to each of these separate databases. My goal is to:

  • Main security requirement is to avoid accidental or malicious data access of different tenant
  • Achieve this in MySQL.
  • For failure tolerance, being able to restore from backup is more than enough.

One approach I can think of is store tenant identity and authentication details in a table in a separate database and then fetch the db username and db password for a tenant and then use that to connect to its specific tenant.

I don't feel very secure about this approach as I will be quering to a database which pretty much has authentication of all the tenants and I am picking one from the list. If this was compromised somehow, having different database with individual username and password would not matter at all.

Any suggestions?

  • There is no "best" solution. There are lots of thinkable different solutions. Which is best suited for a specific scenarion depends on a hell of other factors like what kind of database you have in mind, the usage scenario, the environment, the security requirements, the requirements for failure tolerance. – Doc Brown Feb 26 '16 at 12:17
  • Do they databases need to be separate? It seems you're introducing another database just to handle the other databases which is both redundant and a security risk. Could you not just use different schemas on the same database? – Robbie Dee Feb 26 '16 at 12:47
  • @RobbieDee, They don't essentially need to separate, but the plan is to go with separated databases for options like backup/restore, database corruption. e.t.c – Starx Feb 26 '16 at 12:49
  • @DocBrown, I totally agree that there is no best solution, but I have to know what I need to consider on what scenario, which I don't to be honest. So any suggestions are welcome. – Starx Feb 26 '16 at 12:50
  • @DocBrown, I have added some details specific to your comments. – Starx Feb 26 '16 at 12:56
4

Don't put all the authentication data in the shared database.

Instead of storing tenant identity and authentication details in a separate database, you can store identity (username) and redirection details (server/instance.databasename). Then you can handle authentication at the tenant's database.

I'm assuming admins are going to send invitations to their users. All of the account information is predominantly handled in their own database. Just use the identification and redirection database to help in managing duplicate usernames. When a new account is created, you just need to create a record here with the database identify information (This should be part of the create user transaction.). There isn't anything highly sensitive in this shared part of your data. You could probably redirect a tenant to a specific application server as well. Internally, if you move a database (Maybe a huge client gets their own server.), just change the redirect records.

Salesforce.com doesn't allow duplicate user names across their entire system, so you should be able to do it as well.

  • I am not sure if this applies on what I am trying to do. Storing redirection details on a shared database and then authenticating it against its own db is fine but the connection used will have access to all tenant database then which is what I am trying to restrict. – Starx Feb 28 '16 at 12:39
  • Your app can dynamically choose a specific account to access the appropriate db to do the user authentication. – JeffO Mar 1 '16 at 21:02
  • So you mean, database based username restriction on the database? Something like GRANT ALL ON tenantdb1.* TO 'tenant1'@'localhost'; – Starx Mar 2 '16 at 8:26
  • In many apps, each user doesn't have a unique database login, but you track them in some sort of user table with their access information. The application itself uses a single account to login/access the database. In your case, each database would use a different account. – JeffO Mar 8 '16 at 23:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.