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