I'm planning a DB-based system that is going to support hierarchical multi-tenancy along with inheritance of capabilities and privileges. A schematic structure would be something like the following:
[0]
/ \
/ \
[1] [2]
/ / \
/ / \
[11] [21] [22]
/ \ \
/ \ \
[211] [212] [221]
/ \
/ \
[2211] [2212]
Here the numbers denoting each node in the hierarchy are set for the sake of simplicity only and nothing should be inferred from them.
A second dimension would be given by the different types of users, each type defining a set of privileges (e.g. type of access to the different types of objects being handled by the system).
Each user will be assign to a specific user type and a specific tenant (i.e. a node in the above diagram). A user assigned to a node will only have visibility (if the needed privileges are assigned to the relevant user type) to the objects within the same tenant and all tenants under the one the user belongs to.
A third dimension is going to be given by the fact that modifications can take place to the default privileges assigned to user types. For instance, an Administrator user type may create new users except at the sub-branch [221] (and down). This exception will be defined as a "mask" at the tenant [221].
So, whenever an administrator logs into the system whose tenant is, say, [2212], the privileges he receives is the super-position of privileges as:
Privileges defined at [2212] overriding the
privileges defined at [221] overriding the
privileges defined at [22] overriding the
privileges defined at [2] overriding the
privileges defined at [0]
As an example, suppose that there are 5 different object types, namely O1, O2, O3, O4 and O5, and the privileges are "N" (none), "R" (read) and "W" (read/write). As a default, the administrator user type will have "W" privileges on all object types.
Now, we define privilege "R" on object types O3 and O5 at node [22], and "N" privilege at object type O5 at level [221]. As a result, an administrator assigned to node (tenant) [2212] will inhering the following privileges:
Object Type O1 O2 O3 O4 O5
=================================================
From [0] W W W W W
From [2] _ _ _ _ _
From [22] _ _ R _ R
From [221] _ _ _ _ N
From [2212] _ _ _ _ _
------------------------------------------------
Result ===> W W R W N
Now, three challenges need to be dealt with:
When a user logs into the system at node (tenant) X, all the sub-nodes of X need to be collected to identify the objects on which the user will (potential) have visibility and action right,
When a user logs into the system, his privileges need to be assessed by looking upwards till the root node,
Is the presented approach a reasonable one for a DB implementation (SQL Server)?
Out these three questions, "1" can be simply solved by using CTE to implement a recursion. We are then left with questions 2 and 3. How should I tackle this?
If you normalize your privilege table to something like (admin_id, org_id, obj, perm) instead of (admin_id, org_id, o1,o2,o3,o4,o5), this can be done with a query that gets all ancestors of an org id and cross references that list with the permissions table for the admin you are looking up and gets the permission for the node closest to the one being logged into.
rextester setup: http://rextester.com/MZRF65032
create table org(id int not null primary key, parentid int null);
insert into org values
(0,null)
,(1,0),(11,1)
,(2,0),(21,2),(211,21),(212,21)
,(22,2),(221,22),(2211,221),(2212,221);
create table objects (obj char(2));
insert into objects values ('O1'),('O2'),('O3'),('O4'),('O5');
create table admin (id int not null primary key, name varchar(32));
insert into admin values (1,'Zim');
create table permissions(
admin_id int not null
, org_id int not null
, obj char(2)
, perm char(1)
);
insert into permissions (admin_id, org_id, obj, perm)
select 1, 0, obj, 'W' from objects
union all select 1, 22, 'O3', 'R'
union all select 1,221, 'O5', 'R'
union all select 1,221, 'O5', 'N';
After the test setup, one option goes like this:
/* without pivot */
;with cte as (
select
p.parentid
, p.id
, step=0
from org p
where p.id = 2212
union all
select
c.parentid
, c.id
, step=p.step+1
from org as c
inner join cte p on p.parentid = c.id
)
select o.obj, x.perm
from objects o
cross apply (
select top 1
perm.perm
from cte
inner join permissions perm on cte.id = perm.org_id
and perm.admin_id = 1
where perm.obj = o.obj
order by step
) as x
results without pivot:
+-----+------+
| obj | perm |
+-----+------+
| O1 | W |
| O2 | W |
| O3 | R |
| O4 | W |
| O5 | R |
+-----+------+
If you need the result to be one row, you can pivot the results of the previous query:
/* with pivot */
;with cte as (
select
p.parentid
, p.id
, step=0
from org p
where p.id = 2212
union all
select
c.parentid
, c.id
, step=p.step+1
from org as c
inner join cte p on p.parentid = c.id
)
select o.obj, x.perm
from objects o
cross apply (
select top 1
perm.perm
from cte
inner join permissions perm on cte.id = perm.org_id
and perm.admin_id = 1
where perm.obj = o.obj
order by step
) as x
pivot (min(perm) for [obj] in ([o1],[o2],[o3],[o4],[o5])) as p
results with pivot:
+----+----+----+----+----+
| o1 | o2 | o3 | o4 | o5 |
+----+----+----+----+----+
| W | W | R | W | R |
+----+----+----+----+----+