Using single-user or mulitple mysql users for a SaaS PHP / mysql application with multi-user architecture

Question

We create a multi-user application with one database for each client. All customer database structures are identical. Now we are creating a new mysql user (for each client) who has only privileges to work with his own database.

eg. mysql user1 has rights to dbase1. * (database1.alltables), mysql user2 has rights to dbase2. *.

Now we notice that this is already a pain that needs to be dumped to another server as a backup (we do not use replication, but try to upload files once in a while, but the information_schema database cannot be dropped and recreated from the sql file it seems.

In any case, we are wondering if it would be better to use only one user who can access all client databases? Is it more insecure? Or can it be used in a rather safe way? It would be better to do it for sure.

What are your thoughts?

Answer 1

What you might want to do, during creation for the MySQL user, also save the user’s record in a different place (outside the database), and then the script will restore the users and their permissions to the database from the record that you created.