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