We are building a multiuser app that has one database per customer. All customer database structures are identical. Right now we are generating a new mysql user (per client) that only has privileges to work on its own database.
e.g. mysql user1 has rights on dbase1.* (database1.alltables), mysql user2 has rights on dbase2.*.
We are now noticing that this is already a pain to get dumped to another server as backup (we don't use replication but try to dump files once in a while but the information_schema dbase cannot be dropped & recreated from an sql file it seems.
Anyway, we are wondering if it would be better to just use 1 user that can access all client databases? This is more insecure right? Or can it be used in a rather secure way? It would be better to manage for sure.
What are your thoughts?
What you may want to do is, at the time of creation for the MySQL user, also store a record of that user's creation somewhere else (outside the DB), and then have a script to restore users and their permissions into the DB from that record you've created.