MySQL security group view permission query

MySQL

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

Best Answer

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

Related Solutions

Mysql – Combining a tree structured Group model with its HABTM User model in one VIEW

This can't be done with a view.

Views in MySQL cannot reference variables. Try defining even a simple view that references a session variable, and there's no joy. Take a working query that references a variable and try creating a view using that query:

ERROR 1351 (HY000): View's SELECT contains a variable or parameter

Granted, there's a hack-around for this: you can call a stored function that you wrote, which returns the value of the variable you want to be accessible to the view. But, oops, we're already out of the scope of "view".

Additionally, a view, like a query, has no ability to iterate through rows and do something in "this" row because of what happened in "that" row.

Granted, again, there are hacks with variables in queries that can emulate some of that behavior but on the best days they can only look backwards and not forwards, and they don't always "see" the rows in the same sequence that the rows are returned.

I see three possible approaches to this, if SQL is where you really want to do it:

Option 1 involves a stored procedure and a temporary table. A stored procedure can iterate through rows with a cursor, and you'd need a temporary table since stored procedures don't have arrays or hashes. Iterate through your source data, either with a cursor or in a loop that starts by finding the top parent node and then each subsequent select based on what you've found so far... populating the temporary table as you go, taking precautions to avoid infinite loops caused by circular references in parent_id values (gotcha!), and then SELECT from your temporary table within the proc to return its contents as a result set to the client.

You might even end up with a second stored procedure that you call from the first one, which then recursively calls itself, to traverse your tree and build your nested set.

Option 2 involves insert, update, and delete triggers on the group and user tables, which would rebuild your MPTT structures every time any modification is done to group and user.

This would, in a sense, be the "most correct" way to do it, since your left and right ids would never be inconsistent with the underlying data... but as @dogmatic69 has pointed out, it's a very expensive operation... and not without its own snags, since you're limited to what you can to to table_x while you're inside a trigger on table_x.

On the other hand, with this option, when you needed to see the tree, the work to build it has already been done... so, much faster on SELECT.

Option 3 is to calculate the tree values with something other than SQL... so, really, I lied earlier, and there were only two SQL options I've come up with. Calculate it all in PHP, Perl, etc., and then populate the database with the values you calculated, which is how I generally do it. :) But I get away with it, because the parent/child relationships in my databases are not updated by any other process. If they were, I'd be staring down the barrel of Option 2.

MySQL – Is This Database Structure Correct?

For the Users, Groups and userGroupLink tables you have the relationships correct.

I would suggest you change groups.user_id to owner_user_id just to make it a little bit clearer, but that's a minor point of preference. Since userGroupLink has no attributes of its own nor is it the parent in any relationships you could remove the ID column from this table without any loss of meaning or utility. Indeed you will get a small performance boost by storing more rows per page.

To find all the groups of which a user is a member you would join the tables together:

select
    u.id
    ,u.username
    ,g.group_name
from mydb.users as u
inner join  mydb.userGroupLink as l
    on l.user_id = u.id
inner join mydb.groups as g
    on g.id = l.group_id
where u.id = <the user id you are looking for>

If you are looking for the groups which a user owns that would follow the other relationship.

Best Answer

Related Solutions

Mysql – Combining a tree structured Group model with its HABTM User model in one VIEW

MySQL – Is This Database Structure Correct?

Related Question