CODEWITHSUNDEEP
mysqlweb-applicationssaas
arnaslu
arnaslu

Reputation: 1054

One user per database vs single user for all databases

I'm working on SaaS application that uses the one DB per client model. It also has common "accounts" database where some basic information about the account is kept and also provides log-in functionality.

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

Upvotes: 6

Views: 3014

Answers (3)

DOK
DOK

Reputation: 32841

It's easy to think about creating all those databases.

But also please think about how you are going to maintain them all in the long run.

  • Will you have to run your database scripts on an ever-increasing number of databases?
  • You will have a script to run when you add a new client's database, and that will have to be continuously updated.

I'm not saying don't create multiple databases. I'm just suggesting that you think about the consequences.

Upvotes: 3

Drathier
Drathier

Reputation: 14519

I would create new databases, but it depends. Basically whatever floats your boat :)

one database per user:
+ security is easier
+ async parallel requests (if your server can handle it)
- a bit heavier on disk

one database:
+ one file to handle instead of a bunch (if that's even a +)
+ little bit more space efficient
- slow when data reaches big amounts
- no simultaneous connections meaning a heavy sql request from one user will dos all other

Upvotes: 2

Konstantin Tarkus
Konstantin Tarkus

Reputation: 38378

If security is the concern, user per database is a way to go.

Upvotes: 4

Related Questions