Group / User based security. Table / SQL question


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?
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Muhammad KhanManager, ITCommented:
select item_id from acl where user_id = 'peter';
select item_id from acl where group_id = (select group_id from group_user_mappings where user_id='peter')
> 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)
              (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

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.
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)
    (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


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.