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:

Users:
user_id int (PK)
Account nvarchar(25)
password nvarchar(25)

Groups:
group_id int (PK)
name nvarchar(25)

UserGroups:
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:
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.

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 '))

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:
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.

Purpose

I thought it was about time that I start posting some technical articles that are more in my area of expertise. This blog will work in conjunction with my Web Site Development Tips which takes a much less technical approach to web development.

I have been programming for about 30 years, and creating web sites with programming for over 10 years now. I like to use the best technology for the job, not necessarily what is in vogue or is new and cool.

I believe that programming is a wonderful combination of logic and art. Lines of programming syntax are crafted together to make something useful and elegant. One should follow "best practices" with an eye for a secure application. Consistent code produces consistent results; and consistent good code almost always produces consistent good results. But, it is also important to know when to break the rules to produce something magical. Yes, even a bit of spaghetti code has it's place once in a while.

I have been working extensively in ColdFusion for a number of years, so many examples will be in that language. I also enjoy GIS and creating mash-ups using readily available resources on the web, including Google apps, ESRI JavaScript API's, OpenId and other neat things.

I will be taking care to not expose actual backend and database code from working sites. That means that some of my examples will be made up and may not work properly, though they will be based on actual working code elsewhere.

So, let's see what we can discover together!