Fog Creek Software
Discussion Board

CAMEL Record Locking

By popular request, I posted a description of the record-locking strategy we'd devised over on the CAMEL discussion board.


Tuesday, April 29, 2003

This scheme goes back to the CICS/VSAM days, when application locks were a requirement as DBMS did not exist.

That being said, I am curious.  Is the implication that an individual team member can have permissions that must match what the team access is on the case? 
JSMITH belongs to TEAM1.
UserID     Role         Perm
JSmith     Team1         Full
JSmith     Team1         Read

Is it possible to be FULL but not READ? 

It then appears you wish to lock cases by team.  This appears to conflict with the later example where the same team can have two different types of access.

Would it be simpler to have a user to team, team to case table?  For example:
Team Table
Userid        Team    
BSmith        Team1
JJones         Team1
BJones        Team2

Case Table
Team        Case    Perm
Team1        1421    FULL
Team2        1421    READ
Team1        1422    FULL

This would reduce the number of rows to one per team. 
Would you elaborate please?  I feel I missed part of the conversation by which to understand the context.  If so, accept my apology in advance.


Mike Gamerland
Tuesday, April 29, 2003

It's possible for a user to have access to cases their current team does not. In addition, putting a team table then presumes a hierarchy - you've now locked yourself into the business rule that users are always on a team.

Our architecture puts the entire hierarchy in metadata, so that if (when) the powers that be decide they don't want teams any more, they want to do it some other way, you just have to code the UI to deal with the new situation instead of reworking the entire app.

The reason for putting READ *and* FULL - I'd like to try to handle the records solely with joins, and imposing a hierarchy on actions (full presumes read) prevents that.


Tuesday, April 29, 2003


Could you be more clear about the semantics of Perm in the User Roles table. Is this supposed to be some filter in a universal role hierarchy.
e.g. Even though the A-Team has Universal permissions on the X-Case, and Philo is a member of the A-Team, all his actions as a member of the A-Team are reduced to ReadOnly (a subrole of the full Universal role) and as a consequence Philo has ReadOnly permission on the X-Case.

To be honest, this smells a bit like premature optimization to me from a general User-Groups Objects-Roles approach.

Just me (Sir to you)
Tuesday, April 29, 2003

The issue is the following:
Law Enforcement Officers

LEO's can move from team to team. LEO's have read-only access on all cases within their team. If they move to another team they may still have responsibilities on cases on their old team, so they need read/write permissions on those cases.

In addition a lot of data entry is done by a regional admin staff, who may have the caseload partitioned among them.

There are regional administrative coordinators who may or may not need read-only access to all/most/some cases.

If there is an outside audit of cases, the auditors need read-only access to some specified subset of cases.

Remember this is law enforcement data, so access *should* be restricted to the lowest level - if you don't have a specific reason to access a case, you don't get access.

I'm not sure if this answers the question?


Tuesday, April 29, 2003


I don't know if this is of any help, but once I designed a similar system once for an engineering information management system. Basically the requirement was that engineers (users) could have different roles in different projects teams (groups), which sounds pretty similar to CAMEL's requirement. The added spin was that within a team different members might have different rights, for example some might have rights to create or modify work schedules, whereas others might be entitled to work on mechanical equipment, but not electrical or control systems.

To implement it I modified an idea I'd first encountered in Apollo Computer's Domain/OS file system, that of inheritable access control lists (this was in the days before they were bought by HP and a highly innovative OS - for its day - was consigned to the scrap heap). A subject identifier (SID) is a ternary association between a user, a role and a group. Each object had an access control list (ACL) composed of a set of entries (ACE), each assigning permissions over that object to a group, and optionally a specific role within that group.

Because the data was organised hierarchically we reduced the number of ACEs and ACLs by allowing an object to inherit its ACL from one 'higher' in the tree. We called this 'inheriting' an ACL - knowing full well that it was not classical type inheritance, but rather something which now, it seems (at least in the Zope world) is called 'acquisition'. I didn't know that at the time! We also arranged groups into a hierarchy, at the root of which was predefined group called 'All Users'. Likewise, at the top of the object hierarchy was a prefined object called 'World' that had a predefined 'global' ACL.

When users logged in the system asked first for their name, then used the SID table to find the list of groups to which they belonged (i.e the list of project teams to which they were assigned). The user picked a group and, if he had more than one possible role in that group (again gleaned from the SID table), which role. This combination identified a unique SID and it was this SID, not the user/group/role, that the system associated with a session. The SID also carried the password that authenticated the user.

To determine permissions on a particular object the system had to recursively climb the acquisition tree looking in each object's ACL for an ACE that referenced the SID's group (or any of its parents upto 'All Users') and role and applied the permissions of that ACE.

The neat this about this was that it was very flexible and administration was very simple. By setting very restrictive permissions on the global ACL and relaxing those permissions as one descended down the data hierarchy very tight security could be established with minimal maintenance. Basically it was a case of adding a new project somewhere beneath World and creating an ACL for it that allowed all roles within a given project team to access it. If it was necessary to allow someone from a different project team to see the project, then anyone who had administrative rights over the project could just add a specific ACE granting them permission. Alternatively a system administrator could create a new SID adding them to project team that 'owned' the project.

I must admit that we did this in code rather than with stored procedures, but that was because our software was database independant and we didn't want to write the same SP for all the difference databases we used. Turning a vice into a virtue what we actually did was to have our object loader do all the recursion and cache a 'composite ACL' along with the object. As the loader did a lot of other nifty things, including making one-to-many relationships between entities navigable from both ends (i.e. FOREIGN A.X REFERENCES B.Y became a 'reference to B' attribute in class A and a 'set of references to A' in class B), looking up the permissions granted over an object for a given SID became a matter of scooting around a couple of references and testing for the existence of a reference in a few (quite small) sets.

David Roper
Tuesday, April 29, 2003

I am still confused.
Let me try another way: Why is this scheme better than say having one security table with colums
Object_type Object_ID SID Operation
Object Type tells you the "class" of the object e.g. Case, and forms a composite GUID together with Object_ID.
SID is the same as in Windows, refering to a group or account, and Operation can be any security role (as defined on the type of the securable object.
The other table we need is of course a Account_SID Group_SID for group membership.
(Yes, this is a very simple implementation of the DACL/ACE scheme applied to DB rows)
If it works for Windows, why not for your system? You might also want to incorporate explicit deny_ permissions and SACLs for auditing etc.

Just me (Sir to you)
Wednesday, April 30, 2003


Actually the system I referred to was mostly designed before Windows NT came along, so the only implementations I'd seen of ACLs were in Domain/OS and Unix V extended access rights.

We thought about a scheme using a security table of the form you suggested, but ran into the difficulty that we wanted to be able to define or change permissions at one level in the object hierarchy and have them immediately propagated to every object below. For example, when the design of a particular piece of equipment was signed off, we wanted to be able to touch just that item in order to lock (render read only) all of the parts from which it was made and its connections (relationships) to other items.

If we held a row in the security table for every object/SID combination, then we faced the problems of (a) determining the set of objects that acquired their permissions from the object in question (e.g. all the parts of an engine), and (b) updating thousands, perhaps tens of thousands, of rows in a single transaction. Alternatively, if the security table held only rows for objects that had their ACL set, then we still faced the problem of determining from which ACL in the acquisition hierarchy an object should acquire its rights. Either way we needed recursion.

Oracle provided a nice 'WHERE XXX = PRIOR YYY' clause in its SELECT statement, which is specifically there to implement recursive queries, but at the time SQL Server was still really Sybase and their T/SQL didn't have a similar capability (or at least if it did, I didn't know). Certainly, it wasn't in the SQL92 standard which formed our ground rules for portability. As the system was distributed and we were building an object cache that intermediated between applications and the persistent storage, it was logical to implement security/access control there where it could be universally enforced.

I'm certainly not saying that what we did was the only way of achieving the ends we desired, but it worked well for us and provided a degree of security that other systems didn't have. Of course, you have to rely on the DBA to set strong passwords on the actual database....!

David Roper
Wednesday, April 30, 2003


I should have been more clear. My comments of confusion were more directed to Philo than to you.

Still, it seems to me that your system makes determining data access rights determination more costly in a tradeof to ease of implementing the access rigth administration.
Can these concerns not be separated by keeping the object-object relational information in a separate management app? Yes, the "change the access right of every object in this system becomes more costly, but in most systems would happen a lot more infrequently than simply accessing an object.
Furthermore, if I understand it correctly in your system the user must select an operational role at login time. Does this not assume that his multiple roles are fully corresponding and closely matched to the tasks? Did you ever encounter problems of modality? e.g. User A has permission to access object X because he is part of group M permission to access object Y as a member of group N. For this operation however he needs access to both simultaniously, what to do?
Also, were all objects in a single strict single hierarchical relationship, or could e.g. a certain widget belong in two projects? Was this possible? How were conflicts in the "inheritance" in this case dealt with?

Just me (Sir to you)
Wednesday, April 30, 2003

I had a very similar design discussion about a recent project I was working on.  I agree with Sir's comment.  Quicker/easier administration seems to come at the cost of more complex access-checking during normal use.  I would not be in favour of this trade-off since adminstration is (apparently) much less frequent than simply logging in.

Thursday, May 1, 2003

*  Recent Topics

*  Fog Creek Home