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 discrete resource. Something like this:

TABLE acl ( user_id INT, resource_id INT, access_type INT )

... where access_type is a number representing something like:

0 (or lack of record for user_id and resource_id) means no access

1 means read-only

2 means full control

However it starts getting trickier when you've got scenarios like users can be a member of one or more groups and groups can contain other groups. Then a resource could be a folder that contains other resources.

Other than the obviously poor approach of doing a who开发者_运维技巧le bunch of recursive queries at runtime to determine the level of access a user should have to a resource, how do these scenarios tend to get handled? Are there commonly-accepted designs for modelling an ACL like this?

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

Table Group //Just the parent groups
{
    groupCode varchar
    groupDesc
}

Table groupMap //associates groups with other groups
{
    parentGroup
    childGroup
}

table userGroup //can assign user to more than one group
{
    userId
    groupCode
}

then use connect by to get all child groups for user

SELECT rm.CHILDGroup as roleCode
FROM groupMap rm
CONNECT BY PRIOR rm.CHILDGroup = rm.PARENTGroup
START WITH 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.

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.