如何处理用户对可以访问多个数据库的应用程序的访问权限

Question

I am considering a way of taking an existing php and mysql application that was developed for use in-house (and thus has a single database with nothing built in in terms of multi-tenancy).

I figured that it's possible and maybe in some way better to run each client of their own database. What I want to know is how best to handle the login of a user given the credentials supplied could be in any one of the databases and in some cases possibly multiple databases.

I had thought along the lines of iterating through each database and checking for valid credentials. If the credentials exist in multiples then return a list that the user chooses from, or if it exists only once then just logging in.

Is that a valid solution? Will it start to perform badly IF the application was to expand to have thousands of users across hundreds of databases?

Answer 1

I recommend installing a different instance of the app for each client, with their own database.

Each client should be given their own url to access the app, instead of having them all access in from 1 location, but pointing to multiple databases.

This will adress a few problems that you might encounter if you run all your clients from 1 location (multiple databases)

1. What happend if the different client has accounts with the same name? Which account will take precedence?
2. What happens if the client want to take over the app and host it seperately from the rest of the clients. How can you easily seperate out this client into a sepetare installation?

Another point to note, connecting and disconnecting to databases are expensive. If you have to do so 100 times when the user logs in to find out which database that user is in. It might bogg down the system or your database server.