What if you want something a little more granular than a role based approach? How about an application where each record needs to be controlled - who can view, create, edit or approve it. A system based on Access Control Lists might be the answer.
Let's take a look based on database tables. This can be extended to additional sources like LDAP or OpenID with a bit of work.
Assuming some standard tables for users and groups with minimal columns as follows:
user_id int (PK)
group_id int (PK)
UserGroups:The UserGroups table simply links users to groups in a many to many relationship.
Now we can create the basic Access Control List table.
ACL:Note that the acl_id is not a primary key. This table can have multiple rows. When creating a new acl, I leave a row with null values for acl_type and acl_type_id as a placeholder. The acl_type indicates if that acl is referencing users or groups. So we can allow for the following acl_type codes:
acl_type char (2)
- 'U ' = user with acl_type_id = user_id
- 'G ' = group with acl_type_id = group_id
Here is a query to determine if a user has access based on the ACL.
SELECT DISTINCT 1I like to put this ACL query into a User Defined Function in the database and force it to return a zero (o) when no rows are returned. The function would be defined to accept two parameters, acl_id and user_id.
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
AND ug.user_id = @user_id
AND a.acl_type_id = 'G '))
Now say that we have a project tracking database with committee members providing requirements from throughout your organization. Further, the development groups also needs access. The main project table is defined as follows:
Project:Now we can write a simple query against the project table like this:
SELECT *, checkACL(editor_acl,@user_id) AS isEditorThis query will return all projects that a user is allowed to view, and further tell us if the user is allowed to edit details of the project.
Next, we'll look at extending the system to give it some real power.