0

For example, some actions that users can do, require a set of permissions.

Users are stored in the Users table:

UserId
1
2

and permissions in the Permissions table. Users can have permission A, B, or both.

UserId Permission
1 A
1 B
2 A

Required permissions are stored in RequiredPermissions. From this, the POST action requires both permissions A and B

ActionType Permission
POST A
POST B
GET B

I want to query all users that can POST (user 1). This query would give users that have any permissions (that is, user 1 and 2):

select distinct u.UserId
from Users as u
join RequiredPermissions as rp
  on rp.ActionType = "POST"
join Permissions as p
  on u.UserId = p.UserId

How do I query users which have both permissions A and B with this schema?

0

3 Answers 3

1

Relational division is a less common type of query than the more typical joins and unions. It's a little more awkward in SQL.

You want to know the users who have all the permissions required by POST.

Another way to state this is to return users for whom no permission required for POST is missing from their permissions.

SELECT u.UserID
FROM Users AS u
WHERE NOT EXISTS (
  SELECT *
  FROM RequiredPermissions AS rp
  LEFT OUTER JOIN Permissions AS p
    ON rp.Permission = p.Permission AND u.UserId = p.UserId
  WHERE rp.ActionType = 'POST'
    AND p.UserId IS NULL)

The way it works is that the OUTER JOIN will make p.* NULL if there is no match. So if the outer query wants there to be no case where a required permission has no match for the respective user, then that user must have all the required permissions.

This automatically handles actions with 3 or more permissions. The other solutions for relational division that I've seen require you to make code changes to your query for the number of items you are searching for.

1

There are basically two ways to do relational division in SQL. The first version is described by Bill Karwin and uses an extension of De Morgan's laws into predicate logic:

FORALL p(x) == NOT EXISTS NOT p(x) 

The other version described by nbk compares the cardinality of the domain with the cardinality of each user. Something like:

SELECT userid
FROM Permissions
JOIN RequiredPermissions
    USING (permission)
GROUP BY userid
HAVING COUNT(distinct ActionType) 
     = (SELECT COUNT(distinct ActionType) FROM RequiredPermissions);

The latter is a bit easier to generalize into queries like "Which users have more than X percent of ...."

SELECT userid
FROM Permissions
JOIN RequiredPermissions
    USING (permission)
GROUP BY userid
HAVING COUNT(distinct ActionType) 
     >= 0.4*(SELECT COUNT(distinct ActionType) FROM RequiredPermissions);
0

You can sum up how many permissions a user has and check it against a number

CREATE TABLE Permissions (
  `UserId` INTEGER,
  `Permission` VARCHAR(1)
);

INSERT INTO Permissions
  (`UserId`, `Permission`)
VALUES
  ('1', 'A'),
  ('1', 'B'),
  ('2', 'A');
CREATE TABLE RequiredPermissions (
  `ActionType` VARCHAR(4),
  `Permission` VARCHAR(1)
);

INSERT INTO RequiredPermissions
  (`ActionType`, `Permission`)
VALUES
  ('POST', 'A'),
  ('POST', 'B'),
  ('GET', 'B');
SELECT DISTINCT `UserId` FROM Permissions WHERE `Permission` iN
(SELECT `Permission` FROM RequiredPermissions WHERE `ActionType` = 'POST')
| UserId |
| -----: |
|      1 |
|      2 |
SELECT `UserId`
FROM Permissions 
GROUP BY `UserId`
HAVING SUM((`Permission` =  'A') + (`Permission` =  'B')) = 2
| UserId |
| -----: |
|      1 |

db<>fiddle here

2
  • Thanks, that's an option. But what if we introduce permission C and user has permissions A and C? Apr 30, 2021 at 16:01
  • HAVING SUM((Permission` = 'A') + (Permission = 'C')) = 2` if he has 3 permissions. Runn the code against your database and you see it works. you can do more sophiosticated Sum so that you get can exactly determine which permission a user has like a binary system For permissions like A and C are 101 and ABC is 111 and so on
    – nbk
    Apr 30, 2021 at 16:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.