mORMot Open Source

Milos

Member

Registered: 2021-01-04

Posts: 36

Row-level (multitenant) access control

Sorry if this was already asked, I only found one semi-related topic that doesn't really match the question.

From what I understand, if I use interfaces or server methods to return data I can ensure row-filtering by utilizing the user's tenant ID which I can get by looking for the AUTH record with ID = ServiceContext.Request.SessionUser - hopefully this is the valid approach. Is there a way to do the same for plain ORM calls? I see there is a BeforeURI event but I am not sure it provides all the info, and even if it does I am not sure how to force/inject the parameter value into subsequent ORM? Or maybe there is some other way?

For example, let's say employees work in different departments, they have access to the Documents table but each should only see the documents of his department. Is there a way to ensure this on server side without relying on clients being "honest" and supplying their tenant ID as part of the query filter? Basically look up user's tenant (department) ID and force it into the where clause or something like that?

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,244

Website

Re: Row-level (multitenant) access control

Don't use the ORM methods on the client side.
Encapsulate the data access into some interface-based service.

What we do sometimes is to create the User ID with a bitset containing the organization ID.
The ORM can create a sequential user ID, but you can pre-compute the ID and set ForceID=true when adding.
Then you could use the low bits of the User ID as a counter (e.g. 24 bits), and the high bits as an organization/company ID.
So in your ORM requests, you can easily compute the OrgID of the UserID by using shl 24 for instance.

Then, either:

1. You can add an OrgID to the Documents table, with a proper Index if needed, then include a condition in the Where clause.

2. Or/and you can create a SQLite3 DB per organization. It is my prefered way, since the data will be physically separated, so can be purged or archived very easily, even transferred to another server (or disk).
And you will never get the data from another organization... so it is the safest option.
A dedicated DB will make it also faster, since the organization data will be grouped on disk, and you won't need to use an external OrgID and maintain its index.

Milos

Member

Registered: 2021-01-04

Posts: 36

Re: Row-level (multitenant) access control

OK, thanks for the answer, I will think about it some more