4

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 whole 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?

Marplesoft
  • 6,030
  • 4
  • 38
  • 46
  • I face the same problem so I'm watching this for answers. As far as the recursion is concerned, I'm assuming you're referring the hierarchical nature of folders, look at this question for some ideas: http://stackoverflow.com/questions/4048151/what-are-the-options-for-storing-hierarchical-data-in-a-relational-database – orangepips Nov 10 '10 at 19:08
  • Yes that's what I'm referring to - having to do recursive queries to walk up and down both user group and folder hierarchies/graphs. – Marplesoft Nov 10 '10 at 21:53

2 Answers2

2

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.

Gufran
  • 747
  • 6
  • 14
Sumit
  • 1,661
  • 1
  • 13
  • 18
  • Thanks - we're using MySQL so no "CONNECT BY" that I'm aware of but I think this is the best answer so far. – Marplesoft Nov 12 '10 at 23:13
  • You may be able to use some of the strategies here... http://dev.mysql.com/tech-resources/articles/hierarchical-data.html – Sumit Nov 16 '10 at 18:46
-1

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.

Piotr
  • 4,813
  • 7
  • 35
  • 46
  • Just looking at http://grzegorzborkowski.blogspot.com/2008/10/spring-security-acl-very-basic-tutorial.html and http://orangemile.blogspot.com/2008/07/let-me-count-ways-i-hate-spring.html give me the impression this is not workable. Have you implemented this successfully? – orangepips Nov 10 '10 at 20:01
  • I have used it, but abandoned it because I need the ACL to be part of my domain model - but I think you can probably get a few ideas on how to implement inheritance efficiently - that they have managed to do very efficiently. – Piotr Nov 10 '10 at 20:04