5

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?

Jorre
  • 17,273
  • 32
  • 100
  • 145

2 Answers2

3

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
  • 507,862
  • 82
  • 626
  • 550
  • 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 Jul 12 '10 at 07:23
  • 1
    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 Jul 12 '10 at 08:06
0

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/

Rob Jenks
  • 341
  • 2
  • 9