Best Practice on Implementing a Multi-Tenant App

[stanlyn]

Hi again,

I'm building a multi-tenant app and need suggestions on making sure tenant999's data stays with them and nowhere else using cursoradapters.

At startup I can figure out what tenant/systemID to use and then set it in a global var that lives the life of the session.

Would best practice be to always add the where clause: " where Table.SystemID = gcSystemID" to both stand-alone and DE based cursoradapters? Then, if any additional where clause needs to appended, then append it. There will be a few cursoradapters that will not use a where clause like one to a list of states or zipcodes.

If there are better ways, please say so...

Thanks,
Stanley

 

[ggreen61]

I too have an application that can manage multiple companies (or tenants). Each table has a field 'companyid' that is indexed. At logon, the user has to select the company that they are logging in as (one user could be responsible for multiple companies). From this I set a global property _VFP.CompanyId that is then used in every SQL call in the where clause (i.e., companyid = _VFP.CompanyId AND ...). The user privileges are also set by company id as well.

Greg

 

[Chris Miller]

Well, a database per tenant also is a possibility. Depends on needs. If you always will only look at one tenant for a user that is that tenant oran eomployee of that tenant, then why have all tenants data in one database anyway?

An application you offer as a service in the cloud could work that way with just one central database of which each tenant only sees his data. But then, the siplest is to configure a tenant1.dbc, tenant2.dbc, etc. isn't it?

And if the application is on premise - on site - of the tenant, even more so, they will have their database.

I see reasons for a centralized dtabase in maintianing db updaates, but they can be managed with rollout of new versions also in a per tenant installation or in case of a centralized application in multipole db updates for all tenants. In case usage time overlaps you can also keep multiple versions of the app as long as a tenants db can't be updated he also uses the applicaation verrsion for his db version. So you have several app versions,ideally just the current and the previous version until all tenantts dbs are updated to current version.

Other DB systems than VFP DBC or DBFs also offer alterations of tables whilw users are connected. This can cause troubles, but can also work fine for a flawless update of db and application.

But I don't even know if that's your concern. In the end I'd opt for separate dbs, unless pulling together all data would be a benefit. Alone the risc of someone seeing data not belonging to him makes it easiest to cope with multiple dbs.

Chriss

 

[stanlyn]

Hi Greg and Chriss,

Greg, I'm doing it exactly as you describe, except for the _VFP property instead of using a global public variable. I use it as it works at design time. I've never done it, but I would assume adding a property to _VFP one would have to issue an addproperty() at runtime. Or can it have custom properties available at design time? If so, tell me how?

Have you ever seen an issue where tenants are fed another tenants data?

Chriss, There are several reasons in keeping it all on a single database at our location. Each client is renting our solution. Each client has our desktop client app that auto updates itself on startup. This takes care of all the db and app version mismatch headaches. The main app checks for the updates and if one is found, it closes itself after starting the Update app, which in-turn will start the main app after the update finishes.

Other reasons include limited resources (time and cash) on our end as flood recovery is taking all available resources and will do so for at least another year, and probably more. I've talked with some flood victims from older earlier floods have all said it will take about 2-3 years to get to a place where you don't think about it every time you go to get or use something only to realize it was lost.

Yet another reason is we have been granted permission by the tenant in offering their data online to the public, accessible via a browser as read only. The public can subscribe to one or more of the tenant's data, therefore having them all in one database really make sense.

Thanks,
Stanley

 

[Chris Miller]

The public can subscribe to one or more of the tenant's data

I don't know enough details, but that surely is another meaning of multitenant than usual. Usually a tenant IS a subscriber of your applicaation and should only see own data and no other tenant. Then the usual reasons for one db is that of maintenance of versions. If you actally want data of multiple tenants - or whatever other term we could use here, doesn't really matter, it makes sense to have this data in one DB, of course. Mainly for that reason.

Where clauses work good, that's not the problem. Other DB systems offer better ways of multi-tenant data, but they all work on the principle you only see data of one tenant. The topic of Row level seccurity of MSSQL databases mentions this -

https://learn.microsoft.com/en-us/s...rity/row-level-security?view=sql-server-ver16

RLS filter predicates are functionally equivalent to appending a WHERE clause. The predicate can be as sophisticated as business practices dictate, or the clause can be as simple as WHERE TenantId = 42.

RLS filter predicates are set on the DB level and not per query. They are added to any running query, so you can't miss such a case. But such a mechanism of course needs a database service that centrally runs all queries and has full control over everything. VFP even lacks a security concept of grants (

https://learn.microsoft.com/de-de/sql/t-sql/statements/grant-transact-sql?view=sql-server-ver16)

you grant on the level of files, not columns and surely not recnos or conditions on a record.

Of course, if you program CA that way, that any query is filtering for a specific tenantid then that's fine as long as you never overlook any case. It can get quite complicated for queries joining multiple tables and in case some of them are not per tenant, too.

Chriss

 

[stanlyn]

Chriss,

Its really simple...

Each independent tenant provides the data specific to their county.
Clay County tenant provides Clay County data and this tenant should never see any other tenant's data.

Fayette County tenant provides Fayette County data and again this tenant should never see any other tenant's data.

And on and on a thousand times over... as these are the independent sources.

All tenant's data goes into a single database for several reasons, maintenance, version, and the sharing of this data (as readonly) for public consumption via a web browser and a .NET/C# from IIS. The subscriber chooses what county or counties they wish to purchase.
The web UI then enables only the counties covered by the subscription. All counties are listed so the subscriber has a chance to see what is available, but can only search the counties they have paid for. We have this working now in non-production. And, nothing to do with VFP at this level.

I'm writing/converting our old VFP table based and .scx forms based single tenant app into the multi-tenant version as described. They each write to the same database that is sold as a subscription.

Hope that clarifies, and thanks,
Stanley

 

[stanlyn]

Chriss,

Usually, a tenant IS a subscriber of your application and should only see own data and no other tenant.

And that is exactly what I'm doing. The tenant gathers and indexes the data and subscribers pay for access. Two completely different things against a single database.

Thanks,
Stanley

 

[Chris Miller]

Well, then you just have two types of users, some are tenants, some are not, some only see their own data, some get access to multiple tenants data.

In the techincally the multi-tenant usage of data by those users subscribing to multiple tenant data determines that you have a database with all data in it, the tenants that fill in only their part of data are the ones for which you need the single tenant filter. The others are special in that topi as the need a multi-tenant filter.

That's all there is to it technically.

In terms of MSSQL and RLS filter predicates that would mean those multi-tenant users will not have a filter predicate as simple as TenantId = 42 but needs something like TenantId In(3,42,103).

The difficulty is still the same, you don't have a server that is your gatekeeper of all data, you want to program a mechanism that adds the necessary filtering on the fly to all queries you do. It's more than just appending a where clause.

Chriss^

Edit: This will not just mean different filter conditions of data. There will be a difference in privileges, i.e. the tenant that maintains his data is the owner of it, the subscriber of multiple tenants would just have rights to read data of them.

 

[stanlyn]

Hi Chriss,

https://learn.microsoft.com/en-us/s...rity/row-level-security?view=sql-server-ver16

Thanks for the reference to SQLs row level security article and since this solution is actually 2 different apps that shares 1 database, I'll work on implementing it for the VFP app that the tenants will be using. After thinking about it a little more, I should be able to use it on the subscriber's app as well.

In terms of MSSQL and RLS filter predicates that would mean those multi-tenant users will not have a filter predicate as simple as TenantId = 42 but needs something like TenantId In(3,42,103).

I did not see any reference to anything similar to your "TenantId In(3,42,103)". Does this imply an "or", such as "tenant3 or tenant42 or tenant103"?

Thanks again,
Stanley

 

[Chris Miller]

In(3,42,103)

Better yet

In (3,42,103)

My keyboard sucked again at the space, this time.

It's simple SQL, it's not an In() function, it's an in (list) clause.

Open Database (Home()+"Samples\Northwind\Northwind.dbc")
Select * from northwind!products where productid in (3,42,11)

And what does it mean, if you simply think about it in English? Yes, it means OR. How could it mean something else, like AND?

It's one of those things that's available to SQL but not xbase FOR clause conditions. You have INLIST() instead. Just like SQL can say "where N between A and Z" but you need FOR BETWEEN(N,A,Z). The SQL ways of such clauses are more natural than the corresponding VFP functions.

And there's one more big advantage in SQL about in, that the list can also come from a subquery. Also in VFP9:

Open Database (Home()+"Samples\Northwind\Northwind.dbc")

Create Cursor pickedproducts (id int)
Insert into pickedproducts values (3)
Insert into pickedproducts values (42)
Insert into pickedproducts values (11)

Select * from northwind!products where productid in (Select id from pickedproducts)

I would prefer an inner join of products and pickedproducts for filtering, but in (subquery) works as a where clause, also among other where clauses.

Chriss