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.