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?

Improve this question
9
  • 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
    Commented Feb 26, 2016 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
    Commented Feb 26, 2016 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
    Commented Feb 26, 2016 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
    Commented Feb 26, 2016 at 12:50
  • @DocBrown, I have added some details specific to your comments.
    – Starx
    Commented Feb 26, 2016 at 12:56

1 Answer 1

Reset to default
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.

Improve this answer
4
  • 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
    Commented Feb 28, 2016 at 12:39
  • Your app can dynamically choose a specific account to access the appropriate db to do the user authentication.
    – JeffO
    Commented Mar 1, 2016 at 21:02
  • So you mean, database based username restriction on the database? Something like GRANT ALL ON tenantdb1.* TO 'tenant1'@'localhost';
    – Starx
    Commented Mar 2, 2016 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
    Commented Mar 8, 2016 at 23:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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