Group / User based security. Table / SQL question

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. Any one line in the ACL table with either have an item -> user mapping, or an item 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?

Seems a little messy. I was thinking I could get around it by creating a single user group for each person, so I only ever deal with groups in my SQL, but this seems a little messy as well..

Answer 1

You don't need 2 separate queries per se:

select
    * /* correct to columns needed */
from
    users u
        left join
    group_user_mappings g
        on
            u.user_id = g.user_id
        inner join
    acl
        on
            (acl.user_id = u.user_id or
             acl.group_id = g.group_id)
/* TODO - Add more tables, where clause, etc */