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

views:

answers:

+1 Q:

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

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?

+1 A:

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.

Amber 2010-07-11 23:01:39

thanks, that's a good tip, but we still would have to manage all these user accounts. Is there a way to do this with one account and still keep things safe?

Jorre 2010-07-12 07:23:19

Well, the advantage of multiple user accounts is that it gives you another layer of isolation - if for some reason there's a SQL injection performed, the most it can touch is a single customer's data.Aside from that, you could theoretically write your own encapsulation layer for the database that would enforce access to only the current customer's DB which would give you similar benefits; you're just trusting your coding instead of that of the MySQL team.

Amber 2010-07-12 08:06:02

ansaurus

tags:

views:

answers:

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

related questions