Member-Role is N:N relationship.

Member           MemberRole       Role
 ----------       ----------       ---------- 
| Id       |     | MemberId |     | Id       |
| ...      |     | RoleId   |     | Name     |
 ----------       ----------       ---------- 

• There are ~15,000 members, which have a varying and possibly zero number of roles.
• There are ~50 roles.

Outside of the database, I have allow-deny lists I need to check against the database. A list might look like

 a -b  c  d

This means:

1. If has role a,
   1. ALLOW
2. Else,
   1. If has role b,
      1. DENY
   2. Else,
      1. If has role c,
         1. ALLOW
      2. Else,
         1. If has role d,
            1. ALLOW
         2. Else,
            1. DENY

For example, someone with roles a and d would allowed, while someone with roles b and d would be disallowed.

The lists can easily be converted into a mathematical equation by starting from the end, applying a union (∪) for terms and a difference (-) for - terms.

a -b c d ⇒ ( ( ( ∅ ∪ d ) ∪ c ) - b ) ∪ a

Any trailing deny (-) can be ignored, so we know the first will always be a union.

a -b c d ⇒ ( ( d ∪ c ) - b ) ∪ a

From that, I can build the following query:

       SELECT `MemberId` FROM `MemberRole` WHERE `RoleId` = @d
UNION  SELECT `MemberId` FROM `MemberRole` WHERE `RoleId` = @c
EXCEPT SELECT `MemberId` FROM `MemberRole` WHERE `RoleId` = @b
UNION  SELECT `MemberId` FROM `MemberRole` WHERE `RoleId` = @a

Since x - y = x ∩ y', we can also derive

a -b c d ⇒ ( ( d ∪ c ) ∩ b' ) ∪ a

From that, I can build the following query:

SELECT `Id`
  FROM `Member`
 WHERE (
           (
               EXISTS ( SELECT * FROM `MemberRole` WHERE `MemberId` = `Member`.`Id` AND `RoleId` = @d )
               OR
               EXISTS ( SELECT * FROM `MemberRole` WHERE `MemberId` = `Member`.`Id` AND `RoleId` = @c )
           )
           AND
           NOT EXISTS ( SELECT * FROM `MemberRole` WHERE `MemberId` = `Member`.`Id` AND `RoleId` = @b )
       )
       OR
       EXISTS ( SELECT * FROM `MemberRole` WHERE `MemberId` = `Member`.`Id` AND `RoleId` = @a )

What's the best way to check if a member is allowed? (The answer will usually be yes.) Would using WITH help?

Note that the items of the allow-deny list can be role ids (number) or role names (not a number).

CodePudding user response：

This allow rule a -b c d can be described by logical expression a !b(c d).

WITH MemberRole_acl AS (
  SELECT memberId,
    SUM(roleId = 'a') AS rolesA,
    SUM(roleId = 'b') AS rolesB,
    SUM(roleId = 'c') AS rolesC,
    SUM(roleId = 'd') AS rolesD
  FROM MemberRole
  GROUP BY memberId
)
SELECT m.*
FROM Member m
JOIN MemberRole_acl r ON r.memberId = m.id
WHERE rolesA OR NOT rolesB AND (rolesC OR rolesD)

db<>fiddle

CodePudding user response：

If you have the names of the roles as @a, @b, @c and @d, you can join the tables, aggregate and set the conditions in the HAVING clause:

SELECT mr.memberid
FROM MemberRole mr INNER JOIN Role r
ON r.Id = mr.RoleId
WHERE r.Name IN (@a, @b, @c, @d)
GROUP BY mr.memberid
HAVING MAX(r.Name = @a) OR NOT MAX(r.Name = @b); 

If you have the ids of the roles as @a, @b, @c and @d, it is simpler:

SELECT memberid
FROM MemberRole 
WHERE RoleId IN (@a, @b, @c, @d)
GROUP BY memberid
HAVING MAX(RoleId = @a) OR NOT MAX(RoleId = @b);