How To Build A Data Model For An Access Control List (acl)

It's fairly obvious how to model a database table that would act as an access control list (ACL) when you're just dealing with discrete users who have some level of access to a dis

Solution 1:

Are you using a DB with support for connect by, or something similar? In oracle, I've implemented the following.

TableGroup//Just the parent groups
{
    groupCodevarchargroupDesc
}

TablegroupMap//associates groups with other groups
{
    parentGroupchildGroup
}

tableuserGroup//can assign user to more than one group
{
    userIdgroupCode
}

then use connect by to get all child groups for user

SELECT rm.CHILDGroup as roleCode
FROM groupMap rm
CONNECTBY PRIOR rm.CHILDGroup = rm.PARENTGroup
STARTWITH rm.CHILDGroup in
  (SELECT ur.groupCode
   FROM userGroup ur
   WHERE ur.userId =&userId);

This query will get all the groups that were assigned to the user in userGroup and all the child groups assigned to the groups that the user belongs to.

Solution 2:

Spring ACL is a solid implementation of ACL with inheritance for java. It is open source so I would check it out if it is what you are looking for.