Database Row Level Security - Part 3 - SQL Server

Good day,

Before finishing this series, I would like to point out a very important matter. A few days ago, a colleague of mine, asked a question about the current series and the point of doing that much of configurations to implement RLS for an application that users always face the application itself; not its database. So basically developers can implement the logic in the application.
I believe I have answered this issue already in the first installment of this series. Of course, these days it’s not realistic to think of using databases in that way. But the matter of fact is the security. What if someone can crack the application’s frontline out? Does not the database is open enough then?
Anyway, let’s turn our attention back here to the implementation of RLS in SQL Server. I’m not going to explain the details here. I’ll give you the clues and you got to do the rest DIY.
SQL Server unlike Oracle doesn’t have a built-in library to provide RLS. It has to be done using a technique called Security Labeling.
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

A system containing this data might have user accounts as shown bellow:

Amir: With clearance of “SECRET”
Michel: With clearance of “UNCLASSIFIED” (no clearance)

Each user’s clearance (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 results.

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

And if Michel issues SELECT * FROM <tablename>, he should see different results.

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 IRQ, and consider the following example.

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

Let’s modify our user permissions as well.

Amir: with clearance of “SECRET, PROJECT IRQ”
Michel: with clearance of “UNCLASSIFIED (no clearance)”
Charlie: with clearance of “TOP SECRET”

We’ve added Charlie, a user with TOP SECRET clearance. We’ve also augmented Amir’s label with the PROJECT IRQ 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 IRQ
3	Mission in zone 3	UNCLASSIFIED

If Charlie issues SELECT * FROM <tablename>, he 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 IRQ marking, so he cannot see row 1. Amir, however, satisfies both the SECRET marking and the PROJECT IRQ 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.
Here are a few examples to illustrate this. Assume we have a security labeling scheme with two categories as in the following table.

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

Now let’s look at some example labels. In each case, the question is: does label A dominate label B?

Example 1

Label A	SECRET, IRQ
Label B	SECRET, IRQ, AFG

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 IRQ compartment on label A does not satisfy the IRQ, AFG compartments on label B, since ALL compartments on 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 IRQ, AFG, BN compartments on label A do satisfy the IRQ, AFG compartments on label B, since ALL compartments on B are present on A.

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.

Well, that’s the whole Idea. How this is implemented in Sql Server is what you have to find out. I just give you a few clues.

The mechanism is used to enforce RLS is views. It’s a specially constructed view which applies all the necessary logic to enforce row-level security based on labels. The intent is to simply wrap the base table in a view with a nearly identical definition. Users (or applications) will then query or update the view.

Creating this view will require us to do the following four things.

1. Create some tables that define the label categories and markings, and which assign properties to each unique security label combination. This only needs to be done once for each database.
2. Create roles for the marking values. Membership in these roles will be used to express the label that is assigned to a user. This also only needs to be done once for each database.
3. Make some modifications to the base table.
4. Define the view.

That's it for now. See you next time.