we're building a web-based system and one of the design decisions (database related) was whether to have 1 main DB manage all client data VS having 1 DB per client. After reading a lot on the matter (especially in these forums), it appears that 1 DB per client is overall a better approach, especially on the mid to long-run and especially in our use-case.
The basic architecture
Our system is mostly a front-end system that pulls data from a RESTful webservice (where the client data is located), to access that webservice we use unique keys, each key belonging to a specific pair of CLIENT:DATABASE.
Where things get tricky
When a new client creates an account, the idea is to have the webservice auto-create a new DB, DB user and API key for that account. I've looked for a standard method of implementing this, but failed to find anything conclusive. Obviously, SaaS providers automate this stuff, but where I fail to understand is how they do this safely.
The only current solution I have is to create a sort of MASTER MySQL user, which has absolute total full über power over MySQL, which we would use to create these DB, DB user pairs. But this feels like such a dangerous set up that it CANT be the only solution. Any compromise to this user would be fatal. This is where I need your help...
Another side-question, is how to limit DB size with MySQL/innoDB. The only things that I have found is that MySQL can only limit TABLE SIZE. And, in more sophisticated contexts, that DBs are stored in separate directories which have a specific disk-quota (this seems to be used by some web hosting providers... but I can't confirm). Does anyone know of any other way to control DB size?