NOTE: I posted the same question yesterday but with different explanation, now I am editing it completely and trying to explain my point from a different aspect.
I am trying to implement authentication process for a multi-tenant site where we will have different companies registered for example: company 1, company 2 and etc and each company will have multiple users like company 1 will have User1, User2 and so on.
Here, I am using separate database for each company due to some strong reasons. I want the user to visit the site and just authenticate himself without using any separate domain. There will be a different domain for each company (e.g company1.mysite.com), but the user will be redirected to his respective domain after login.
So, my question is, how can I authenticate each user from his respective database at the time of login?
Thank You
At the end of the day, you simply cannot ping all DBs fetching the users and hoping for a match. I would, however, recommend the following:
A special mapping DB that holds users and their respective company database. For added security, you can treat emails like a password, storing an encrypted version with a predefined salt and when a user enters their email to login, a lookup is done against the mapping DB using the two encrypted email addresses. You will need to be careful to keep this DB synced well. If your DBs are on the same server, I might recommend insert and update triggers.
At the point of successfully authenticating against their company DB, you would want to store the DB name in the user session to keep from ongoing lookups against the mapping DB. You could alternatively use the subdomain mapping to DB after successful auth.