1

Assume I have these two tables:

Table User:

ID           Name
1            User A
2            User B
3            User C
4            User D
5            User E

Table Permission:

ID           UserID          PermissionName
1            1               read
2            3               read
3            3               write
4            5               read
5            5               write
6            5               admin

Now I want to have the following queries:

1- All users with write permission

SELECT u.* FROM user u, permission p WHERE p.userID = u.id AND p.name = 'write' GROUP BY u.id;

Seems to be ok. Returns user 3 and 5.

2- All users without write permission

SELECT u.* FROM user u, permission p WHERE p.userID = u.id AND p.name != 'write' GROUP BY u.id;

Returns: 1,3,5 Expected: 1,2,4

This does not work. It returns every user, except those with only the write permission. So how can I make this work in MySQL and SQLite?

3- Combinations of both

  • All users with read and write (3,5)
  • All users with read and not write (1)
  • etc.
Kevin Flachsmann
  • 466
  • 1
  • 4
  • 17

3 Answers3

1

The GROUP BY u.id simply combines all the rows of each user that meet the WHERE conditions. Since user 1, 3 and 5 have permissions other than write they are returned.

The easiest way to solve this problem is to change the permissions table. Like this:

Table Permission:

ID        UserID        CanRead        CanWrite      IsAdmin
1         1             true           false         false
2         3             true           true          false
3         5             true           true          true

And it is now obvious it could become part of the users table:

Table User:

ID     Name        CanRead        CanWrite      IsAdmin
1      User A      true           false         false
2      User B      false          false         false
3      User C      true           true          false
4      User D      false          false         false
5      User E      true           true          true

So you don't even need a permissions table. The query now becomes:

SELECT * FROM user WHERE CanWrite = 'false'

which is so simple.... but do you think I am cheating?

You might have suffered from over-normalization urges.

KIKO Software
  • 15,283
  • 3
  • 18
  • 33
  • The problem is, that this example is very simplified. In the real database, I have an RBAC system with many permissions and a scheme like in this post: https://stackoverflow.com/a/16140262/4028475 – Kevin Flachsmann Jun 21 '19 at 10:00
  • @KevinFlachsmann I suspected as much, but could only work with what was there. – KIKO Software Jun 21 '19 at 10:02
1

You need to join the tables and group by user.id. Then put the conditions in the HAVING clause.
I assume there are no duplicates in table permission.
For the 1st query:

select u.id, u.name
from user u inner join permission p
on p.userid = u.id
group by u.id, u.name
having sum(permissionname in ('read', 'write')) = 2

or:

select u.id, u.name
from user u inner join permission p
on p.userid = u.id
where permissionname in ('read', 'write')
group by u.id, u.name
having count(permissionname) = 2

and the 2nd:

select u.id, u.name
from user u inner join permission p
on p.userid = u.id
group by u.id, u.name
having 
  sum(permissionname = 'read') = 1
  and
  sum(permissionname = 'write') = 0
forpas
  • 160,666
  • 10
  • 38
  • 76
  • I am searching for a universal solution, where the number of permissions is not explicit known. There could be 100 different permissions. Now, for example, I want all users, that do not have one specific permission. Also, when using HAVING, it is necessary to GROUP BY permission_name, what means, I get duplicate results per user. – Kevin Flachsmann Jun 21 '19 at 10:10
  • Then post another question where you explain what you want. This question has been answered. – forpas Jun 21 '19 at 11:46
  • *when using HAVING* it is **not** necessary to GROUP BY permission_name. – forpas Jun 21 '19 at 11:59
  • I think it’s in every programmers mind, not to use the first working, but the best possible solution which can be reused for other similar problems as well. – Kevin Flachsmann Jun 21 '19 at 12:34
  • I agree, but you did not mention this in your comment. All you did is post a new requirement. This is not how it works here. You post a question and you receive answer(s) for that question. If you want something different you post another question. – forpas Jun 21 '19 at 12:38
  • See my answer, that should be fitting all requirements – Kevin Flachsmann Jun 23 '19 at 09:10
0

I've found a working solution, which could be combined with many other filters. Maybe not the most performant way, but seems to be ok.

2- All users without write permission

SELECT * FROM user WHERE id NOT IN (SELECT userID FROM permission WHERE permission = 'write') ;

3- All users with read and not write

SELECT * FROM user WHERE id NOT IN (SELECT userID FROM permission WHERE permission = 'write') AND id IN (SELECT userID FROM permission WHERE permission = 'read') ;
Kevin Flachsmann
  • 466
  • 1
  • 4
  • 17
  • *All users without write permission* you did **not** ask for this query. *All users with read and not write* you are scanning the table **3 times**. Once for the 1st subquery, then for the 2nd subquery and last for the main query. Do you think this is better? – forpas Jun 23 '19 at 09:14
  • See my question, 2nd query. I explicitly asked for: 2- All users without write permission. I definitely see the problem of multiple table scans, especially when combining multiple filters like that. But I do not see any better solution right now. – Kevin Flachsmann Jun 23 '19 at 09:22
  • *I do not see any better solution right now* **Really?** – forpas Jun 23 '19 at 09:25
  • @forpas Do you? – Kevin Flachsmann Jun 23 '19 at 09:27
  • Any of my queries does better than any of yours. Why don't you make a search in SO for similar problems. There are many. All of them contain solutions that have nothing to do with your solution. Read more about sql. – forpas Jun 23 '19 at 09:30
  • I tested with 200 users and 50 permissions. For the first query (all users with write permission) I got 0.0011 seconds for my and 0.0106 seconds for your query. For the second one I got 0.0019 seconds for mine. Your query did somehow return the same as the first. Also I do have the problem, that users with any permission are missing. At least, I think it should be mentioned, that write must not implicit include read. – Kevin Flachsmann Jun 23 '19 at 09:50
  • The queries I posted do what they are supposed to do as per your requirement. If you did not explain clearly then this is your problem. If you test your code on tables with 200 rows of course you will get results in milliseconds. If you are happy with your results this is fine. But don't post inefficient code as the *best solution*(!!!) to a problem here, because this will be read by others who will look for solutions to their problems. Anyway I'm done here. – forpas Jun 23 '19 at 09:58
  • I never said best, I said working. If the requirement is all users without write, those with any permissions should be implicit included, in my opinion. But, this is not the right place to discuss, thanks for your effort. I will further test both solutions an pick the best fitting in my case. – Kevin Flachsmann Jun 23 '19 at 11:10