I'm setting up a group / user based security system.
I have 4 tables as follows:
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?