MS SQL Server - Value exists in a graph

Question

I'm trying to implement some security based functionality and need to test if a individual has permissions to a specific entity. The data is in MS SQL Server and the graph functionality seemed suited to it.

There will be organisational units that can contain indiviudal and groups. A group can contain individuals as well as groups.

An entity can be granted permissions using any combination of the security identifiers (individuals/groups/org units).

Below is example data of organisation, 3 indviduals, 3 groups and an entity. Indentaion implies group membership - so Group A has Persone C/D as members. The first number implies the object ID - so Person C has Id 3 etc.

1 Org X
2 Group A
3   Person C
4   Person D
5 Group B
3   Person C
6   Person E
7 Group C
5   Group B
4   Person D
8 Ent A
    Group C
9 Ent B

Below is the SQL to build sample data - I've used a Node table to represent individual/group/org.

Entity A contains permissions for Group C. I want to test it for Person E.

This is where I get stuck - want to traverse the graph below Entity A for any instance of Person E.

Here's a SQL example I've tried to piece together that doesn't work:

SELECT entity.Name, LAST_VALUE(PNode.Name) WITHIN GROUP (GRAPH PATH) AS LastNode
        FROM Securable.Entity entity, Securable.Node FOR PATH AS PNode, Securable.MemberOf FOR PATH AS mof1 
        WHERE MATCH(SHORTEST_PATH(Entity(-(mof1)->PNode)+))
        and entity.Name = 'Ent A' and LastNode='Person E'

---sample data
   CREATE TABLE Securable.Node (
        Id bigint PRIMARY KEY,
        Name NVARCHAR(100) NOT NULL,
        ) AS NODE;  

   CREATE TABLE Securable.Entity (
        Id bigint PRIMARY KEY,
        Name NVARCHAR(100) NOT NULL,
        ) AS NODE;  


truncate table Securable.Node
truncate table Securable.MemberOf
truncate table Securable.Entity

insert into Securable.Node (Id,Name)
values (3,'Person C'),(4,'Person D'),(6,'Person E')

insert into Securable.Node (Id,Name)
values (2,'Group A'),(5,'Group B'),(7,'Group C')

insert into Securable.Node (Id,Name)
values (1,'Org X')

insert into Securable.Entity (Id,Name)
values (8,'Ent A')
insert into Securable.Entity (Id,Name)
values (9,'Ent B')

--Users C/D member of Group A
INSERT INTO Securable.MemberOf ($from_id, $to_id) VALUES (
    (SELECT $node_id FROM Securable.Node  WHERE Id in (4)), 
    (SELECT $node_id FROM Securable.Node  WHERE Id = 2));

INSERT INTO Securable.MemberOf ($from_id, $to_id) VALUES (
    (SELECT $node_id FROM Securable.Node  WHERE Id in (3)), 
    (SELECT $node_id FROM Securable.Node WHERE Id = 2));


--Users C/E member of Group B
INSERT INTO Securable.MemberOf ($from_id, $to_id) VALUES (
    (SELECT $node_id FROM Securable.Node  WHERE Id =3), 
    (SELECT $node_id FROM Securable.Node WHERE Id = 5));

INSERT INTO Securable.MemberOf ($from_id, $to_id) VALUES (
    (SELECT $node_id FROM Securable.Node  WHERE Id=6), 
    (SELECT $node_id FROM Securable.Node WHERE Id = 5));

--Group B, Ind D member of Group C
INSERT INTO Securable.MemberOf ($from_id, $to_id) VALUES (
    (SELECT $node_id FROM Securable.Node  WHERE Id =3), 
    (SELECT $node_id FROM Securable.Node WHERE Id = 7));

INSERT INTO Securable.MemberOf ($from_id, $to_id) VALUES (
    (SELECT $node_id FROM Securable.Node  WHERE Id=6), 
    (SELECT $node_id FROM Securable.Node WHERE Id = 7));

--Groups A/B/C member of Org X
INSERT INTO Securable.MemberOf ($from_id, $to_id) VALUES (
    (SELECT $node_id FROM Securable.Node   WHERE Id =7), 
    (SELECT $node_id FROM Securable.Node  WHERE Id = 1));

INSERT INTO Securable.MemberOf ($from_id, $to_id) VALUES (
    (SELECT $node_id FROM Securable.Node   WHERE Id =2), 
    (SELECT $node_id FROM Securable.Node  WHERE Id = 1));

INSERT INTO Securable.MemberOf ($from_id, $to_id) VALUES (
    (SELECT $node_id FROM Securable.Node  WHERE Id =5), 
    (SELECT $node_id FROM Securable.Node WHERE Id = 1));

--Entity A has Group C
INSERT INTO Securable.MemberOf ($from_id, $to_id) VALUES (
    (SELECT $node_id FROM Securable.Node  WHERE Id =7), 
    (SELECT $node_id FROM Securable.Entity WHERE Id = 8));

MS SQL Server - Value exists in a graph

0 Answers