Access Control Lists - Part 2

Following up from my first post, we'll talk about extending the ACL system. We can get a substantially increase in the power of our ACL system with inheritance. Here are some examples of useage:

• If we had a project tracking database where we assigned access rights projects, we could create sub-projects with their own access controls but inherit the managers of the project for automatic access to the sub-project.
• In a forum or blog concept, we could have comments that inherited the access of the forum category or blog entry which reduces the work needed to control access. But we could then break the inheritance for specific comments creating specialized threads for specific groups.
  

The first step is to specify a new acl_type of 'A ' with the acl_type_id then referencing the acl_id of the ACL you are planning to inherit. This will create a self referencing primary key - foreign key pair in your ACL table. This is similar to a 'parent_id' field, but is not hard coded.

SELECT DISTINCT 1
FROM acl a
WHERE acl_id = @acl_id
AND ((acl_type = 'U ' AND acl_type_id = @user_id)
OR EXISTS (SELECT group_id
FROM usergroups ug
WHERE a.acl_type_id=ug.group_id
AND ug.user_id = @user_id
AND a.acl_type_id = 'G ')
OR (a.acl_type = 'A ' AND checkACL(a.acl_type_id,@user_id)=1))

You can also do this type of recursion using a SQL 'WITH' statement, but I found that recursively calling my function was faster in my environment. The SQL also seems a tad bit cleaner and easier to understand. Warning: This code can generate an infinite loop and bring down your database! Care must be taken to ensure that your acl's do not chain in a loop.