Use single or mulitple mysql users for SaaS PHP/mysql application with multi-tenant architecture

We are building a multiuser app that ha开发者_C百科s 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.

This somewhat depends on your requirements (especially related to restoring a client from backup), but I am using a single database/schema for all of my tenants with no potential for a tenant seeing another tenant's data.

1. Create a mysql user per tenant (sounds like you already have this)
2. Add a tenant_id column (VARCHAR) to all tables
3. Use a trigger to automatically put the current mysql user into the tenant_id column on INSERT
4. Create a view for each table that only shows rows where tenant_id = current_mysql_user (don't include the tenant_id in these views)
5. Only give access to these views to the tenant mysql users
6. Determine which tenant is connecting (possibly by URL) to determine which mysql user you should use to connect to the database.

Since your application would be connecting to the database using a tenant-specific user that only has access to rows where tenant_id = their user, data will be segmented by tenant.

I was able to use this technique to convert a large single-tenant application to multi-tenant in a weekend with very few changes. I documented the full solution in my blog: https://opensource.io/it/mysql-multi-tenant/