[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 218
  • Last Modified:

Group / User based security. Table / SQL question

Hi,

I'm setting up a group / user based security system.

I have 4 tables as follows:

 - user
 - groups
 - group_user_mappings
 - acl

where acl is the mapping between an item_id and either a group or a user.

The way I've done the acl table, I have 3 columns  of note (actually 4th one as an auto-id, but that is irrelevant)

- col 1 item_id (item to access)
- col 3 user_id (user that is allowed to access)
- col 3 group_id (group that is allowed to access)

So for example

item1, peter, ,
item2, , group1
item3, jane, ,

so either the acl will give access to a user or a group.

If I want to have a query that returns all objects a user has access to, I think I need to have a SQL query with a UNION, because I need 2 separate queries that join like..

item -> acl -> group -> user   AND
item -> acl -> user

This I guess will work OK. Is this how its normally done? Am I doing this the right way?
0
BrettskiWork
Asked:
BrettskiWork
  • 2
1 Solution
 
Muhammad KhanManager, ITCommented:
select item_id from acl where user_id = 'peter';
union
select item_id from acl where group_id = (select group_id from group_user_mappings where user_id='peter')
0
 
nasirbestCommented:
> where acl is the mapping between an item_id and either a group or a user.
You should not put group and user ACL in same table as it is not flexible design.

create two seperate ACL table like user_acl and group_acl and then search required ACL in union of these two tables for example

  1. first load user_id and group_id into $user_id and $group_id variable (best way is to store these variable into session when user log in)
  2. store item_id in $item_id variable for item you want to check that current user have access or not
  3. use following query if it return anything then user have access
$query = "SELECT item_id FROM ((select item_id FROM user_acl WHERE user_id = $user_id)
              UNION
              (SELECT item_id from group_acl where group_id = $group_id)) union_acl
          WHERE union_acl.item_id = $item_id
          LIMIT 1";

Open in new window

0
 
BrettskiWorkAuthor Commented:
Thank you, what if I want to return a list of items I have access to, but I only know my user_id, not what groups I belong too.
0
 
nasirbestCommented:
Use following query

remember to replace variables with there actual values like $user_id and $item_id
SELECT item_id FROM (
    (SELECT item_id FROM user_acl WHERE user_id = $user_id)
    UNION
    (SELECT item_id FROM group_acl LEFT JOIN group_user_mappings ON
        group_acl.group_id = group_user_mappings.group_id
    WHERE group_user_mappings.user_id = $user_id)
) union_acl
WHERE union_acl.item_id = $item_id

Open in new window

0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now