A common error in implementing RBAC in SQL Server (and .Net)

Reading the academic literature on RBAC I noticed a qualification which is very significant but is rarely emphasized to get on the typical reader’s radar. The qualification is stated in the early works from 1996 and in the recent National Institute of Standards and Technology (NIST) RBAC documents. The qualification is that only one role may be active at a time.

 

It is not unusual to see all access rights being consolidated with something like this in TSQL:

 

SELECT DISTINCT AccessRight 
FROM [UserRole]
JOIN [RoleAccessRight]
ON [UserRole].RoleID=[RoleAccessRight].RoleID
WHERE UserID=@UserID

The problem is that this violated one role being active at a time!  This is not a problem if users are allowed to be assigned to only one role at time, unfortunately that is rarely seen.

 

Let me explain with a simple example. Prof Turning is an Instructor and assigned the Role AI_Instructor with rights (View Students taking AI in current year, Upsert Marks). In other words, he is able to give and change marks for his students.

 

Prof. Turning is also assigned to the Instructor_Performance_Review_Committee with rights (View Students in Faculty for all years).

 

If you do the above TSQL, Prof.Turning can view and change any grades for any student, current and past, in the faculty. This was not intended by any party.

 

While the above is contrived for a simple illustration, the same issue can arise in real corporate systems

 

There are two simple situations where the above may be valid:

• Where users can be assigned only one role (thus only one role can be active)
• Where rights are absolutely exclusive (which is difficult to determine).

There are some more complex situations where validity can be constructed  but they run a real risk of breaking down with business evolution.

 

If we view AccessRights above as equivalent to a permission (the right to do an operation on an object:  operation x object) then we can define the criteria that must be satisfied to be exclusive.

• {Objects in AccessRight A} Intersect {Objects in AccessRightB} = null

That is, there are no objects that are in common. Since RBAC typically includes object hierarchy, the complete hierarchy for A and B must be retrieved and intersected. Is this practical?

 

I suspect not:

• You cannot assign roles with overlap to a user
• You must check before every assignment of a role and deny if there are conflicts,
• On every object add or hierarchy change, every role must be rechecked, if there are any conflicts, deny the addition.

These behaviors are not administration friendly because the denials may appear random.

 

Bottom line is that maintaining a single active role is essential. This actually results in complexities when a UI element could be influenced by multiple roles, but that’s another post… Another key factor is that access rights are always in a role context and cannot be removed from the containing role.

 

Post Script: Above we did Insect of objects, an equivalent condition may be stated in terms of actions but since typical actions are far more complex to do analysis on, objects were used.