Best approach to multi-tenancy


#1

My application, and specifically the database, needs multi-tenancy.

Set up is pretty standard with MySQL on the backend. Right now I have DSP wired to one database as the out-of-the-box configuration.

I don’t want to do record-based multi-tenancy for security reasons.

I don’t want to do a different MySQL server per tenant for cost reasons.

I could do either a) one database (and service?) per tenant or b) a table name prefix with each tenant having their own set of prefixed tables. Right now I’m headed down the path of b. I’ll have to script a fair bit for setting table-level role-based permissions in DSP.

Anyone have anything elegant here they’d suggest? Anything you learned where you did something similar?

What can I do to reduce throwaway wrt to the product roadmap?

Many thanks.


#2

Hey Gary, DreamFactory Enterprise has multi-tenant support. It’ll be on Bitnami Oct 6th.

Enterprise is a separate distro used to deploy, manage, report on, and manage API calls on many instances of the DreamFactory 2.0 open source runtime. It’s a commercial product but free to use for development.

More info coming soon. We’ll have a webinar coming up and new docs on all this.


#3

Many thanks Ben.

I’m exploring lighter weight multi-tenancy techniques, specifically within an instance.

I can thoroughly see how multi-tenancy across many instances fits for enterprise use cases.

Might you have some patterns for what works well with intra-instance multi-tenancy in lower volume scenarios?


#4

Hey Gary, let me have Lee take a look and offer some suggestions. He’s slammed right now but may have ideas. One thing that’s around the corner is the ability to add any number of SQLite instances which reside in the local file system in a DreamFactory instance.


#5

What would be awesome is something that maybe looked this…

When you create a user, you give them a tenant id, an additional field.

When you create tables in the database (and other services too?), there’s a checkmark whether or not the table is common, per tenant, or both.

Role access configuration would specify what permissions you grant related to common, per tenant, or both.

Accessing a common table would be as it is:

/db/(table)

Accessing a multi-tenancy table would be:

/db/(tenant id)/(table)

There are several ways you could handle in the DBMS. This would give DSP the capability to take care of the consistency of the structures across each of the tenants, so I wouldn’t have to by hand.

Right now, how I’m doing it is like:

/db/(tenant id)(table)

Where I prefix tables. It’s rather cumbersome and laborious, especially caring for the downstream permissions.

I’m open to ideas.