CODEWITHSUNDEEP
sqldatabasedatabase-design
Marplesoft
Marplesoft

Reputation: 6240

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

Upvotes: 4

Views: 7706

Answers (2)

Sumit
Sumit

Reputation: 1669

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.

Upvotes: 2

Piotr
Piotr

Reputation: 4963

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.

Upvotes: -1

Related Questions