One user for each database and one user for all databases

Question

I am working on a SaaS application that uses one DB for each client model. It also has a common database of “accounts”, which stores some basic information about the account, and also provides login functions.

My question is: is it worth creating a new database user for each client database that has permissions only to this database, or does a user with a single database having access to all client databases make more sense (i.e. " account\_% . * ")?

Answer 1

If security is a problem, the user in the database is the way to go.

Answer 2

I would create new databases, but it depends. In principle, what your boat is floating :)

one database for each user:
+ security is easier
+ asynchronous concurrent requests (if your server can handle this)
- a bit heavier on disk

one database:
+ one file for processing instead of a bunch (if it is +)
+ a little more free space

- slow when data reaches large amounts

- simultaneous connections do not mean that a heavy sql request from one user will use all the others

Answer 3

It is easy to think about creating all of these databases.

But also think about how you are going to support them in the end .

• Do you need to run your database scripts on an ever-growing number of databases?
• You will have a script to run when you add a new client database, and this should be constantly updated.

I am not saying that do not create multiple databases. I just suggest you think about the consequences.