MySQL security group view permission query

Question

I have a table of records that are only viewable by users with the correct group permission. The records may be viewable by multiple groups. Users may also be in multiple groups.

record

+----+-------------------+
| id | record_data |
+----+-------------------+
| 1 | Some sample data |
| 2 | More data |
| 3 | Some more data |
+----+-------------------+

groups

+----+-------------+
| id | description |
+----+-------------+
| 1 | Group A |
| 2 | Group B |
| 3 | Group C |
| 4 | Group D |
+----+-------------+

user

+----+------+
| id | name |
+----+------+
| 1 | Levi |
| 2 | Bill |
| 3 | Sam |
| 4 | Jen |
| 5 | Amy |
+----+------+

I have created lookup tables to match the record to the groups that have permission to view them. I have also created a lookup table that matched the users with the groups they are members of.

record_group

+----+-----------+----------+
| id | record_id | group_id |
+----+-----------+----------+
| 1 | 1 | 2 |
| 2 | 1 | 4 |
| 3 | 2 | 1 |
| 4 | 3 | 2 |
| 5 | 3 | 3 |
| 6 | 3 | 4 |
+----+-----------+----------+

So in the table above, record one is viewable by users in group 2 and 4. Record 2 is viewable by users in group 1, and record 3 is viewable by users in group 2,3 and 4.

user_group

+----+---------+----------+
| id | user_id | group_id |
+----+---------+----------+
| 1 | 1 | 1 |
| 2 | 1 | 2 |
| 3 | 1 | 4 |
| 4 | 2 | 1 |
| 5 | 2 | 4 |
+----+---------+----------+

Above, User 1 is in group 1,2 and 4. User 2 is in group 1 and 4.

I am trying to create a query that will show all the records that a particular user has the correct group permissions to view. Example. I want to list all the records that user ID 2 can view and ignore the others.

The query for user 2 should return record 2,3 and 6

I need to do this in a single query as it will be plugged into an existing framework.

My understanding is that I can do this with the MySQL IN function but I cant seem to get it to work with two sets, example (Select id from member_groups) IN ()

So my Question, how do I build a MySQL query that selects all the records user one has permissions to view?

I'm sorry I cant provide more information or example query, I'm not sure how to start.

SQL Fiddle http://sqlfiddle.com/#!9/e40ee6/1

Answer 1

SELECT DISTINCT record.id AS id, details from record 
LEFT JOIN record_group ON record.id = record_group.record_id
WHERE record_group.group_id IN (SELECT group_id from user_group WHERE user_id = 2)

http://sqlfiddle.com/#!9/e40ee6/12

Answer 2

I would use only joins as conditions of the form WHERE column IN (SELECT ... FROm other_table) are not fully optimized in older versions (before 5.6 - 5.7):

SELECT DISTINCT 
 r.id, r.details 
FROM record AS r
 JOIN record_group AS rg ON r.id = rg.record_id
 JOIN user_group AS ug ON rg.group_id = ug.group_id
WHERE ug.user_id = 2 ;

or this (you may need to disable the ONLY_FULL_GROUP_BY sql mode setting):

SELECT 
 r.id, r.details 
FROM record AS r
 JOIN record_group AS rg ON r.id = rg.record_id
 JOIN user_group AS ug ON rg.group_id = ug.group_id
WHERE ug.user_id = 2 
GROUP BY r.id ;

This would also work but I wouldn't expect improved efficiency:

SELECT r.id, r.details 
FROM record AS r
WHERE EXISTS 
 ( SELECT 1
 FROM record_group AS rg 
 JOIN user_group AS ug ON rg.group_id = ug.group_id
 WHERE ug.user_id = 2 
 AND r.id = rg.record_id
 ) ;

A composite index on user_group (user_id, group_id) would help any of these queries (including yours).