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.

Database Row Level Security - Part 1

In this 3 parts series I intend to explain what Row Level Security in database management is and how to implement it in Oracle and SQL Server.

In almost all database management systems the level at which information is controlled extends only to a certain level of granularity. In some scenarios, however, there is a requirement to control access at a more granular level. A list of patients, for example, may be stored in a single table. Any doctor, however, is only be permitted to view his own patients' records. Another example can be those applications that implement feature access control. That is, users' credentials and their access to application's features and modules are stored in database and used by application.
In such cases, merely issuing a GRANT/DENY SELECT command on the table will not meet the requirement. One solution is to implement this requirement at the application level - in Business Logic Layer if application is using 3 tier architecture - to filter rows of data. But what if someone bypasses the code and gain direct access to data? The presentation layer creates the illusion of security. What actually needs to be done is to automatically filter queries at the database level. In the doctors and patients example, all users might have access to the patient table, but SELECT * FROM Patient only returns the rows that a user should see; wherever that query is executed from.

In part 2, I’ll explain how RLS can be implemented in Oracle 10g utilizing auditing features.

SQL Server guys' problem of learning Oracle - Part 2

In the first part, I briefly wrote that there are fundamental differences between Oracle and SQL Server and in this episode we'll look at a few examples. The intention is to make it easier for those who know about one product and would like to know about the other. It's also my intention to conclude that designing applications to be database agnostic is not a good practice.

In my opinion, there are two reasons for facing difficulties when someone (SQL Server DBA or Programmer) wants to transition to Oracle and vice versa.

1- Similar terminology but different implementations.
2- Utilizing the database in applications the same way.

Terminology

The most similar and confusing term between these two DBMSs, I believe, is “Instance”.
What is called instance in SQL Server is a collection of windows services that manages SQL Server Databases. You can have more than one database attached to an instance at any point of time.
In Oracle, however, an instance is a little bit different and so the way Oracle DBAs manage it.
Unlike SQL Server each instance in Oracle can mount and open only one database at any point of time. If you want to have more than one database open and running simultaneously, you’ve got to have another instance. Creating an instance in Linux came easier to me than creating one on Windows. In Windows every instance is represented by a service and so to have a new instance you ought to create a new service using a command-line tool. But in Linux, you need to take care of a few files and folders and then start it up.
Also each Oracle instance needs a "parameter" file to start working called Server Parameter File (SPFile). This file defines the attributes and characteristics of the starting instance.
 Each SQL Server instance comes with a few databases including "System" using which instance manages other databases. But since an Oracle instance can open one database at a time, at the creation time, is nothing more than a few files and folders.

Applications

In applications that utilize SQL Server it's common to open a connection to database for each executing statement. If you are going to do six queries, you might well see six connections. SQL Server was designed that way - much like Windows was designed for multithreading, not multiprocessing. In Oracle, whether you want to do six queries or six hundred queries, the maximum number of connections you want to open is one. Each Oracle connection was designed to handle multiple sessions. So a single Oracle connection requires much more memory than a single SQL Server connection. Essentially, what's common practice in a SQL Server database is simply an incorrect approach against an Oracle database.


To conclude, as an architect or developer, it's important to remember that when porting applications from one database to another, you ought to take into consideration the time that is needed to refine and adjust application's architecture to be coherent with the database's architecture. I notice development teams choose development frameworks specifically because it helps their application to be database neutral without understanding the implications. Even Oracle provides two separate database management applications for Linux and Windows even though Java runs in both. That's not because Oracle can't ship one application. It's solely because Windows and Linux are fundamentally different. One uses processes (Linux) and the other threads.

If you are in the same situation and having problems, please don’t hesitate to contact me and share your problems.

SQL Server guys' problem of learning Oracle - Part 1

In the first episode I’m going to write about the main issue that a SQL Server expert might face as he start learning Oracle.

I’ve always liked Microsoft SQL Server not only because it’s easier to manage and I can be more productive with – which isn't the whole point, of course - but also because it’s one of Microsoft’s products with which I have least amount of trouble (e.g.: bugs, security holes, etc) compare to other products like Visual Studio or Windows itself.
After a while, I realized knowing about other DBMSs is necessary, too. Because working with similar products in the same field gives you a lot of new ideas and, at the same time, options.

Starting with Oracle can be confusing if your mind is set on SQL Server. I, myself, had to start by figuring out differences of terminologies ("instance" and "database" to begin with). Oracle databases I setup were running into numerous number of issues. And it was all merely because I wanted to do things the same way I use to do in SQL Server.
It’s true that they have similarities, lots of lots of similarities. But the way they work are different. If you are a developer that doesn't look at database as a black box, you understand what I am talking about.
In my opinion, you have to learn Oracle from scratch, in the same way you learned SQL Server. This seems very obvious. But many good developers I run into don't seem to have realized this.
You have to understand how things really work in Oracle and solve problems in the Oracle way, not in SQL Server way.
This would be true as well if you were an Oracle DBA or programmer and you wanted to learn SQL Server. That’s why in the next post I am going to write more about this issue and compare quite a few concepts of Oracle and SQL Server that sound similar but are not and show you how they are different. That will hopefully make it easier for those who work with SQL Server to start learning Oracle.

There is a book from an expert that could make the learning process easier; “Expert Oracle Database Architecture” by “Thomas Kyte”. I wish it was available when I was starting with Oracle.