Database Row Level Security - Part 3 - SQL Server (and others)

In part 1 of this series Row (Record) Level Security was introduced and part 2 depicted its implementation in Oracle database.

I'd like to start the last part by answering one of the questions I was asked: "what's the point of doing this much configuration in an application that users never see the database? Essentially, application layer's control should suffice." To be pragmatic, I'd say not much most of the time. But if you are dealing with sensitive information (e.g.: medical records, payment cards, social security numbers) you shouldn't assume that all applications connecting to the database to be bug free. Studies show that most attacks exploit a weakness in user interface (a few web development frameworks were created to address that issue). When that happens, your data is at the mercy of your application and the attacker.

Update in 2010:  For an example, please see this [PDF] report.

SQL Server unlike Oracle doesn’t have a built-in mechanism to provide RLS. It has to be done using a technique called Security Labeling. As a matter of fact, this technique can be used with any database (e.g.: using actual users in Oracle to achieve this may not always be an option).
A security label is a piece of information which describes the sensitivity of a data item (an object such as a table). It is a string containing markings from one or more categories. Users (subjects) have permissions described with the same markings. Each subject has a label of their own. The subject’s label is compared against the label on the object to determine access to that object.
For example, the following table fragment (object) has rows annotated with Security Labels. (Classification column)

ID	File Name	Classification
1	Mission in zone 1	SECRET
2	Mission in zone 2	TOP SECRET
3	Mission in zone 3	UNCLASSIFIED

And users have different access level:

Amir: with "SECRET" clearance
Michael: with "UNCLASSIFIED" clearance (no clearance)

Each user's clearance level (expressed as a security label) determines which rows in the table they can access. If Amir issues a SELECT * FROM <tablename> against this table, he should get the following result:

ID	File Name	Classification
1	Mission in zone 1	SECRET
3	Mission in zone 3	UNCLASSIFIED

And Michel with same query should see a different result:

ID	File Name	Classification
3	Mission in zone 3	UNCLASSIFIED

Access controls can get more complex than this. There may be more than one access criterion expressed in a security label. For example, in addition to a classification level, a piece of data may only be visible to members of a certain project team. Assume this group is called PROJECT YUK, and consider the following example.

ID	File Name	Classification
1	Mission in zone 1	SECRET, PROJECT YUK
2	Mission in zone 2	TOP SECRET
3	Mission in zone 3	UNCLASSIFIED

Let’s modify our user permissions as well.

Amir: with "SECRET, PROJECT YUK" clearance
Michael: with "UNCLASSIFIED" clearance (no clearance)
Charlie: with "TOP SECRET" clearance

We've added Charlie, a user with TOP SECRET clearance. We’ve also augmented Amir's label with the PROJECT YUK marking. Now, if Amir issues SELECT * FROM <tablename>, he should see the following results:

ID	File Name	Classification
1	Mission in zone 1	SECRET, PROJECT YUK
3	Mission in zone 3	UNCLASSIFIED

And Charlie will see the following results:

ID	File Name	Classification
2	Mission in zone 2	TOP SECRET
3	Mission in zone 3	UNCLASSIFIED

Although Charlie has a TOP SECRET clearance, he does not have the PROJECT YUK marking, so he can't see row 1. Amir's marking, however, satisfies both SECRET and PROJECT YUK marking, so he can see row 1. Row 2, requiring a TOP SECRET clearance, is visible to Charlie only.
This basic approach can be extended to additional markings. In some real-world scenarios, security labels can include several markings from different categories, and the number of possible label combinations can be quite large.

A subject can access an object if the subject label dominates the object label. Given two labels, A and B, label A is said to dominate label B if every category present in label B is satisfied by markings on label A. Determining whether the markings are satisfied depends on attributes of each category. For our purpose, each category can be characterized by the following attributes:

• Domain: The possible markings in the category. 
• Hierarchical (yes or no): Whether or not the category is hierarchical. Hierarchical categories have an ordering among values. This order determines access. A marking can satisfy any marking at or below its level in the hierarchy. Nonhierarchical categories have no ordering among values. A marking is either present or not present.  
• Cardinality: How many values from the domain can be applied to the object. 
• Comparison Rule: Whether the subject must have any or all of the markings applied to the object from this category (referred to as the Any and All comparison rules, respectively). An alternative rule, InverseAll, can be used. This rule requires that each object must have all the markings held by the subject in order to be accessible.

Let me illustrate this with a few examples. Let's assume we have a security labeling scheme with two categories as shown in the following table:

Category	Domain	Hierarchical	Cardinality	Comparison Rule
Classification	TOP SECRET SECRET CONFIDENTIAL UNCLASSIFIED	Yes	1..1 (exactly one)	Any
Compartment	YUK ALB BC	No	0..* (0, 1, or many)	All

The question to ask is "does label A dominate label B?".

Example 1

Label A	SECRET, YUK
Label B	SECRET, YUK, ALB

To compare these labels, we must compare the markings in each category.

• Classification: The SECRET marking in label A satisfies the SECRET marking in label B. 
• Compartments: The YUK compartment in label A does not satisfy the YUK, ALB compartments in label B (since ALL compartments in B must be present in A).

So, label A does not dominate label B.

Example 2

Label A	TOP SECRET, IRQ, AFG, BN
Label B	CONFIDENTIAL, IRQ, AFG

• Classification: The TOP SECRET marking in label A satisfies the CONFIDENTIAL marking in label B. 
• Compartments: The YUK, ALB, BC compartments in label A satisfy those in label B.

So, label A dominates label B.

Example 3

Label A	SECRET, IRQ, BN
Label B	CONFIDENTIAL

• Classification: The SECRET marking in label A satisfies the CONFIDENTIAL marking in label B. 
• Compartments: Label B has no compartments listed, which means there are no compartment requirements.

So, label A dominates label B.

To implement this, all the necessary logic is built in views. The intent is to simply wrap base tables in views with nearly identical definitions. Users (or applications) will then query or update views.

To achieve this:

1. Create tables to store label categories and markings along with properties of each unique security label combination.
2. Create tables to store roles and their associated marking values. 
3. Create views.