Saturday, June 27, 2009

Access Control Lists - Part 1

When it comes to authentication, there are many choices. On the enterprise network, LDAP is a prime choice. For small sites, If you can't set up SSL for your own authentication system, OpenID is a good choice. Once authenticated, the next step is providing authorization. Often times, a role based approach is fine - create a group, assign the users and grant access within the application based on membership in the group.

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)
Account nvarchar(25)
password nvarchar(25)
group_id int (PK)
name nvarchar(25)
group_id int
user_id int
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_id int
acl_type char (2)
acl_type_id int
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:
  • 'U ' = user with acl_type_id = user_id
  • 'G ' = group with acl_type_id = group_id
Then we can assign the primary keys from those tables into the acl_type_id field as indicated above.

Here is a query to determine if a user has access based on the ACL.
FROM acl a
WHERE acl_id = @acl_id
AND ((acl_type = 'U ' AND acl_type_id = @user_id)
FROM usergroups ug
WHERE a.acl_type_id=ug.group_id
AND ug.user_id = @user_id
AND a.acl_type_id = 'G '))
I 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.

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_id int
project_name varchar(50)
viewer_acl int
editor_acl int
Now we can write a simple query against the project table like this:
SELECT *, checkACL(editor_acl,@user_id) AS isEditor
FROM project
WHERE checkACL(viewer_acl,@user_id)=1
This 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.

No comments:

Post a Comment