How to create a data model for an access control list (ACL)

Question

Obviously enough, how to model a database table that acts like an access control list (ACL) when you are simply dealing with individual users who have some level of access to a discrete resource. Something like that:

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

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

0 (or no entry for user_id and resource_id) means no access

1 means read only

2 means full control

However, it starts to get trickier when you have scripts, such as users may be members of one or more groups, and groups may contain other groups. Then the resource can be a folder containing other resources.

Besides the obviously poor approach to executing a number of recursive queries at runtime to determine the level of access a user must have on a resource, how are these scripts typically handled? Are there common ACL modeling projects like this?

Answer 1

Are you using a database with support connect byor something like that? In oracle, I 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 byto 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 request will receive all groups that were assigned to the user in userGroup, and all child groups assigned to the groups to which the user belongs.

Answer 2

Spring ACL ACL java. , , , .