Alternate Multitenant Solution

Classic Servoy
1

From what I’ve seen the most common ways to manage multitenant/multicompany implementations are:
Option A. Add a tenant_id column to every table
Option B. Manage a separate set of tables for each tenant.

Each of the alternatives has is pros and cons, for example:
Option A Pros: Static database model, easier to manage, adding new companies is simpler.
Option A Cons: Security management is more complicated since needs to be managed at record level, backups and restores may affect others, every query needs to take the tenant_id column into account

Option B Pros: Each customer has an independent data store , allows to load balance or move certain databases to other servers, update/restore without affecting other tenant’s data or performance, when a tenant leaves its database could be dumped
Option B Cons: Database model keeps growing which implies management complexities as well

I wouldn’t dare to say which one is the “right one” because the solution has to be designed to fit the needs. Now, Servoy makes it very easy to develop for option A, since, at the solution startup one could add the foundset filters for the current tenant. However, there is no support for those who go for Option B, currently, the workaround is to define all of the possible database servers from the start and then use switchserver to use the appropriate one. For example: if you expect to have 99 tenants, you need to define conn01, conn02, con003… conn99 and at startup point to the appropriate one (and also send the appropriate one when using the raw_sql function ). That, of course is hard to manage, let’s say you now decide to work with another RDBMS (which is one of the beauties of Servoy) you would need to go and change all of those connections.

This could easily be handled on the development side if other parameters for a database connection could be changed, such as the Schema, Username and Password. I do not expect this to be easy to manage in Servoy since due to its N-tier design, the connections are handled by the Servoy Application Server, and if a user is able to change a connection parameter what would happen to the rest? Well I could try talking about an in-memory pool of connections but I would just look dumb because I really don’t know anything about that, you Servoy guys are the experts :). I’m just trying to portray a common situation to see if others relate to it and would really find this functionality useful.

Another thing this would allow to do is to manage a database user for each application user, now, I know Servoy does manage security and logging pretty well. However there are cases where business needs or external requirements prevail and the solution has to cope with that. Such a case would be one a Servoy solution is connecting to the same Database that other applications connect to and an insitutions has an external database auditing tool to get all the read and writes and don’t want a generic database user to be shown.

I hope I explained myself correctly and you didn’t fall asleep while reading this. I tried to properly paint the picture so that others could support the idea.

Regards,

JD

2

In a solution for a big customer (multinational company group) I use both approaches together: separate DB per company, office_id in every table to split the data of each tenant for his offices. It works very well.
The weak point is, as you say, that to add a new tenant (it doesn’t happen so often in my case) you need to define a new db connection and restart the application server. This could be a show stopper if you plan to sell your solution on the cloud to several tenants and you expect them to grow and shrink over time.
Being able to define database connections on the fly would come out very handy: no need to restart the application server, no need to keep several unused db connections open (sucking memory on both servoy side and db server side) but I think it would be quite difficult to implement.

If you open a Feature Request for this you will have my vote, I’m planning a future project in the cloud and I would really love to be able to keep customers data in separate databases without wasting too much resources server side.

3

A very important benefit with option A in our case is the possibility to (easily) share data between a certain subset of owners/tenants in the one database. We made it end user configurable which tables are tennant group accessible and writeable. Example : sharing the same article or stock data between more tenants.

For multi tenant data management we made our own tool in Servoy to pull all data of one tenant out of the database (easy) and to insert all data of one tenant into the database (hard, pk/fk hell).

Regards,

4

Excellent ngervasi, that’s what I’m looking for, before opening a reature request, have people tell their experiences with similar cases and let know if they would benefit from this functionality because, as you say, It would probably be a difficult thing for the Servoy team to implement.

Lwjwillemsen, yes, that would be another advantage of the first option, it’s also much easier to make reports that group information from several companies. That is why It would be nice to have both options, so we as developer could use depending on the needs.

Thanks for the replies,

JD

5

jd2p:
Lwjwillemsen, yes, that would be another advantage of the first option, it’s also much easier to make reports that group information from several companies. That is why It would be nice to have both options, so we as developer could use depending on the needs.

If you need to run reports or aggregate several tenants data and you are using Postgres or Oracle consider the option of using 1 single database and several schemas (one for each tenant), that allows you to easily use JOINs and aggregate over all tenants data. Keep in mind though that all schemas must stay on the same tablespace so if your dbs will get very big you will have to use a very big disk or solve the issue at the filesystem level.

6

Yep, in my current case, we are using Oracle and use the several schema approach. We perform joins between schemas. It just takes I little bit of more logic to do it than it would in the other approach where you just put the conditions in the Where.

7

ngervasi:
If you open a Feature Request for this you will have my vote, I’m planning a future project in the cloud and I would really love to be able to keep customers data in separate databases without wasting too much resources server side.

Hey ngervasi, it’s been a while but I just opened this request, here it is: Jira

Regards, JD

8

You got my vote as promised ;)

9

Thanks!

10

I like to revisit this thread to see if I can gain more supporters. Aside from the Multitenant model implications, as it is, our servoy applications all use the “One Big Application User” model, in which all our application connects to the database with a single database user which would need to have the highest privileges any application user would need at any given moment.

In the case of an Oracle Database and according to their documentation, this approach hinders us from using important security features, to mention two (extracted from Oracle documentation):

  • Auditing: A basic principle of security is accountability through auditing. If all actions in the database are performed by One Big Application User, then database auditing cannot hold individual users accountable for their actions.
  • Of course Servoy provides a solid way of managing the auditing. However, we have to take into account that a whole lot of us are coming from other development tools, and have other programs accessing the database, so the auditing has to be managed by the database…unless everything that is ever going to access the database will only be a Servoy app.
  • Oracle Advanced Security enhanced authentication: Strong forms of authentication supported by Oracle Advanced Security (such as, client authentication over SSL, tokens, and so on) cannot be used if the client authenticating to the database is the application, rather than an individual user.
  • And the following is one of my big concerns :
    When security is enforced in the database itself, rather than in the application, it cannot be bypassed. The main shortcoming of application-based security is that security is bypassed if the user bypasses the application to access data. For example, a user who has SQL*Plus access to the database can execute queries without going through the Human Resources application. The user thus bypasses all of the security measures in the application.

Hope this wasn’t to tedious to read. I wish you all well.

Regards,
JD

11

Being able to define server connections at runtime would nicely bridge the gap between everyone on the same database connection vs everyone on separate servers. A no-brainer feature and it’s been talked about for years so I suspect there is a bit of a technical challenge to making this happen.

Another interesting concept that we’ve run across in another n-tier server tech is being able to establish a connection to another server’s data connection. Makes for some interesting multi-tenant data architecture options.

And then there is NoSQL. Kind of like solution model for your data structures and documents. Never any downtime but it’s a bit of a jump from the SQL world. Can be done with Servoy now.

This and no client-side UI coding (with async to the server) I would say are the main areas of concern if needing to do a large scale cloud app with Servoy.

12

Hi David, thanks for the input… if you would like this feature to be included, you can up-vote the request here: Jira

Regards,

jd

13

David,

There doesn’t appear to be a technical challenge to doing this as we were provided a plugin (using undocumented API) way back in Servoy v4.0 days that accomplishes this. This same plugin (with minor tweaks along the way) continues to work today. I’m really not sure what is preventing Servoy from making the appropriate API calls public. Since they started banging the SAAS drum a while ago I’m surprised this multi-tenancy feature hasn’t made it onto the roadmap.

Corey

14

Yeroc:
There doesn’t appear to be a technical challenge to doing this as we were provided a plugin (using undocumented API) way back in Servoy v4.0 days that accomplishes this. This same plugin (with minor tweaks along the way) continues to work today.

I want! Post to ServoyForge?

15

I want it too!!!

16

Our plugin is not general-purpose as it ties in directly to our infrastructure but if you’re familiar with Java and implementing a Servoy IServerPlugin then you should find it pretty easy to implement yourself. From within a IServerPlugin implementation class you do something like this:

String dbServerName = ...;

IServerManagerInternal manager = ApplicationServer.getInstance().getServerManager();
IServer server = manager.getServer(dbServerName, false, false);
// doesn't exist yet, need to create it...
if (server == null)
{
  ServerConfig dbConfig = new ServerConfig( ... all your db connection parameters go here... );
  manager.createServer(dbConfig);

  // save it...
  manager.saveServerConfig(dbServerName, dbConfig);
}

In our plugin we also check the configuration of existing dbServers and update the configuration on the fly if they’re out of date (eg. we periodically reset the database server passwords). To do that you’ll need to use these other methods:

// get an existing dbServer configuration so you can check the password etc...
ServerConfig dbConfig = manager.getServerConfig(server.getName());

// if you are updating an existing dbserver config delete the existing one first and then re-create as above...
manager.deleteServer(dbConfig);

All error-handling code was removed for brevity.

DISCLAIMER: This is using unpublished API so it can (and does) periodically change even between minor bug fix releases. The code above is for Servoy v6.1.5.

Corey

17

Would make sense to throw in Patrick’s UserManager plugin under the Server node.

18

Hi

Sybase has a quite good article on multi tenancy showing the various possibilities. I attach the PDF I composed from the Web (you can read it there) but you have to read it from the end to the beginning.

Regards, Robert

Multi Tenant.pdf (401 KB)

19

Corey, it’s so cool that you’ve been able to do it within your plugin…

Yeroc:
David,
I’m really not sure what is preventing Servoy from making the appropriate API calls public. Since they started banging the SAAS drum a while ago I’m surprised this multi-tenancy feature hasn’t made it onto the roadmap.

Yep, I was also a bit surprised this hasn’t been a major concern. There is an request you could up vote here if you want to: Jira

David, I agree that having it in Patrick’s userManager plugin would also make absolute sense…

david:
Would make sense to throw in Patrick’s UserManager plugin under the Server node.

20

jd2p:
David, I agree that having it in Patrick’s userManager plugin would also make absolute sense…

david:
Would make sense to throw in Patrick’s UserManager plugin under the Server node.

Now if Patrick would have published his plugins Open Source on ServoyForge as was announced more than a year ago, “anyone” could have integrated that…
Knock, knock, hint, hint… say no more! ;)