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 2 - Oracle

In the first episode we briefly introduced Row Level Security. In this part I’m going to show you how to implement it in an Oracle 10g database.

Oracle 8i introduced a feature called VPD (Virtual Private Database); also known as Fine Grained Access Control that provides powerful row-level security capabilities.
VPD works by modifying the SQL commands to present a partial view of data to the users based on a set of predefined criteria. During runtime, WHERE clauses are appended to all the queries to filter only those rows user is supposed to see.

Important: implementing row level security using VPD requires each user to have an actual corresponding user object in database, not just a record in a table. If that's not feasible, then part 3 might be the way to go.

Here is the list of what we need to implement this:

1. An Application Context.
2. A procedure that sets a variable in the above mentioned context and is called when user login to the database.
3. A secured procedure that makes the WHERE clause using the variable that has been set in the context.
4. A RLS POLICY that put all these together to tell database how to filter queries.

I explain it with the doctors and patients example in part 1.
Let’s assume the tables look something like the following picture (the relationship could be actually many-to-many, but I simplified it).
 Every time a doctor logs in to the system, a procedure is invoked which then sets the value of a variable in Application Context called, say, "logged_in_doc_id" to doctor's doc_id field queried from the Doctors table.

When a doctor queries the list of patients, another procedure filters the data. In this case, we simply add a WHERE clause to the query. Something like this: WHERE f_doc_id = logged_in_doc_id. This WHERE clause will be concatenated to the SELECT command and make it look like this (assuming his id in database is 1): SELECT * FROM PATIENTS WHERE f_doc_id = 1.

Here is the code:

1 & 2 –
As it can be seen, I’ve named the Application Context "NMSPC" and I’ve set the "logged_in_doc_id" variable to the value of "doc_id" field of the record that its "doc_name" field matches with the current logged in user’s name (USER). 

CREATE OR REPLACE PACKAGE CONTEXT_SETUP AS
   PROCEDURE SET_SESSION_VAR;
END;

CREATE OR REPLACE PACKAGE BODY CONTEXT_SETUP AS
   PROCEDURE SET_SESSION_VAR IS
      t_id NUMBER(5);
   BEGIN
      SELECT doc_id INTO t_id
      FROM DOCTORS
      WHERE UPPER(doc_name) = UPPER(USER);
      DBMS_SESSION.SET_CONTEXT
           ('NMSPC', 'logged_in_doc_id', t_id);
   END;
END;

CREATE CONTEXT NMSPC USING CONTEXT_SETUP;

CREATE OR REPLACE TRIGGER EXEC_CONTEXT_SETUP
AFTER LOGON ON JIM.SCHEMABEGIN
   CONTEXT_SETUP.SET_SESSION_VAR();
END;

EXEC_CONTEXT_SETUP trigger is called whenever a user (in this instance "Jim") logs in.

3 – We need a procedure that creates the predicate; the WHERE clause:

CREATE OR REPLCAE PACKAGE CONTEXT_WHERE_MAKER AS
   FUNCTION WHERE_MAKER(obj_schema VARCHAR2,
                        obj_name VARCHAR2)
   RETURN VARCHAR2;
END;

CREATE OR REPLCAE PACKAGE BODY CONTEXT_WHERE_MAKER AS
   FUNCTION WHERE_MAKER(obj_schema VARCHAR2,
                        obj_name VARCHAR2)
   RETURN VARCHAR2 IS
   BEGIN
     IF UPPER(obj_name) = 'PATIENTS'
     BEGIN
      RETURN 'f_doc_id = SYS_CONTEXT("NMSPC",
                              "logged_in_doc_id")';
     END;
   END;
END;

SYS_CONTEXT is a function that helps us to retrieve the value of a variable in a particular context.

4 – The last thing we are going to create is a RLS POLICY that put all these together to make them meaningful.
 

BEGIN
   DBMS_RLS.ADD_POLICY
   ('ALL_TABLES', 'Patients',
    'pat_policy',
    'JIM', 'CONTEXT_WHERE_MAKER.WHERE_MAKER',
    'SELECT');
End;

Please note that "ALL_TABLES" is our schema under which all tables are created and all users have access to.

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.

Microsoft and Linux

If you are an Oracle expert or follower, you’ve probably been following Oracle’s Open World and its news. One of the biggest one was Oracle’s announcement of distribution and support of Red Hat Linux on October 26. This made the Linux Magazine to say “Oracle was predicted to make a big Linux announcement this week and you would be hard pressed to make a bigger one than this”.But I believe the second announcement from Microsoft and Novell that they would be working together on Linux wasn’t something less important than that.Yes, you red it right. Microsoft made a deal to be a distributor of Suse Linux, Novell's version of the operating system.Beyond the patent covenant and financial arrangements between them, it was revealed that, the two companies will work together on at least three projects.The companies will jointly develop a compelling virtualization offering for Linux and Windows. The result of which would enable Windows to run under Linux and Linux to run under Windows.
The two companies will be working together on Web Services. And finally there will be an effort to create interoperability between Microsoft Office and OpenOffice.org file formats.Well, things are changing in the world of software development. I think we have to get ready for the time that .NET applications are fully portable to Linux.

Update (2013):

Mono has simply kept its promise of delivering a cross platform .NET development framework to this day. For example, we ported and recompiled an ASP.NET web application which was built with .NET framework 3.5 (utilizing many of its new features) and deployed onto a server running a CentOS linux.

Iterative Development

I'd like to write about something a little bit different from the other posts, Iterative Development.
Well, it may sounds easy and obvious. That’s what I thought when I heard it for the first time. But usually when I ask this simple question that what Iterative Developments is, I get a vague answer. Thus, I decided to write about it.
I use to think the most important part of development is to get the requirements and understand them intelligibly to start development. Well, understanding requirements is very important. But not to collect and understand all detail requirements at the beginning and to start development all the way to the end and maybe showing a prototype to the end user in one stage. Does this sound familiar to you? Well, that’s not what a proper Iterative Development is.
Yes, you have to have an overall and good enough knowledge of the stakeholders’ requirements at the first day, a vision. But detailing requirements and developing them in stages - that are called Iterations - and also informing stakeholders and getting their confirmation for what you have done at the end of each stage are what Iterative Development is composed of.
Maybe the following picture conveys what does that mean:

As you can see, this picture shows at each stage some of the requirements are detailed and developed and build. What it doesn’t show is the customer involvement in this process that can be seen in the next picture.

At the end of each iteration you will have a tested build that is representable to the customer and so you will be able to show him your progress and get his feedback and assess it. Of course after that ther will a chance that your plans and documents need to be refined. That shows you are progressing. That's the nature of this way of development.Regarding the management, I must say it’s not an easy responsibility to manage an Iterative Development process. Project manager, need to plan for each iteration before it starts and assess the result when it finishes.I think it's time to show you some of the advantages. To me the most important advantage is mitigating risks. Let’s take a closer look at one of the risks that every project may face, change requests.Which one is easier? Receiving a change request (that your customer thinks is quite small and easy change but in fact it can be devastating) at end of the development process or end of an iteration that you have invited him/her in to see that iteration’s output?Another benefit of iterative approach is having test and test plans early in project. It doesn’t seem right to postpone test to the end of project. You don’t want to see bugs and issues – especially big ones - at the end of development that you believe you are done. Do you?
I hope you got the picture.One more thing, Iterative Development approach is considered troublesome if you don’t have experience or consultant. So please don’t get a real project – even a small one – and try to use this approach if you don’t have someone that has experienced this before. You might end-up with what I did once before.
Let me know if you disagree with me or you have a real case that you want to share with the others.

How to pin objects in the Oracle's Shared Pool

In this post I'd like to write about Oracle's Shared Pool and the way it is being used and also introduce a package named "DBMS_SHARED_POOL".

Every Oracle Instance has one big memory structure called SGA (System Global Area). It can vary in size depending on the server's memory capabilities.SGA is divided into various pools and one of its critical pieces is Shared Pool. Critical in term of performance. A wrong sized Shared Pool can easily make the system look dead.There are many pieces of program data that Oracle keeps in Shared Memory.
For example, as you might know, executing a query is not a very simple process as database engine has to parse the query and find the most efficient way to execute it and do IO stuff and retrieve actual data.So to not parse every single query, Oracle keeps the parsed queries in the Shared Pool. The next time that it needs to parse a query, it first looks at the Shared Pool to find out whether that query has been parsed or not. If it has, it reads the data from Shared Pool and continues.Let's say we have 100 users all executing same piece of program. The first execution, caches the parsed query and others just use it. So Shared Pool not only caches data but also shares them.As you could imagine, Shared Pool can not keep programs' data for ever. Sometimes it needs to take some of them out to make space for new data. Under those circumstances Oracle uses a LRU algorithm to erase data. LRU stands for Least Recently Used and it means if Oracle needs to make space, it will take those parts that have been used least out. That's what is called Aging Out. That peace of memory has been aged out.But sometimes it happens that you need to keep a piece of memory in the Shared Pool and make sure that it won't be aged out.
When? Well, if you have a large procedure that will be used frequently you might want to load it up at database startup time and keep it in Shared Pool.DBMS_SHARED_POOL package has a procedure called KEEP. Using KEEP, you can make an object to stick to the Shared Pool. But not all kind of objects. Only Packages, Functions, Procedures, Sequences, Triggers and Types.If you want to undo the KEEP, you need to use UNKEEP procedure from the same package.
More information about the package can be found in Oracle documentation.By the way, if you don't have this package in your database, you can create it by running the
DBMSPOOL.SQL. You can find it at $ORACLE_HOME/rdbms/admin.

Team Development using MS TFS - Part 3

In part 1 and part 2 of this series we introduced TFS and its architecture.

I intended to close this series by writing about other aspects of team development using TFS. But a colleague pointed out that TFS deployment is not as easy as it looks to many. So I dedicate this post to the topic of installation. 
You can deploy TFS in two ways, single server or double server. Among reasons to use double server approach could be performance or distribution. In the double server implementation, application and database tiers will be physically separated and have more resources to utilize.

In this post I only discuss single server deployment.

Installation prerequisites 

1- User accounts

If the server in which you are installing TFS is part of a domain, you need three Active Directory domain user accounts.
If the server is a member of a workgroup, you can use local user accounts.

Sample user logon name	Purpose
TFSSETUP	Used to run Team Foundation Server Setup. This account must be an administrator on Team Foundation Server computers. This account must be a member of the same domain as the following two service accounts. For example, you cannot have the two service accounts on a domain and then use a local account to run setup.
TFSSERVICE	Used as the service account by Team Foundation Server Windows services (Code Coverage Analysis Service and TFSServerScheduler), and the SharePoint Timer Service. Used as the application pool identity by the Team Foundation Server application pool (TFS AppPool), and the Windows SharePoint Services application pools (TFWSS and TFSWSSADMIN). Must have the Allow log on locally permission on Team Foundation Server computers. For optimal security, this service account: - Should not be an administrator on Team Foundation Server computers. - Should have the option Account is sensitive and cannot be delegated selected for Active Directory on the domain.
TFSREPORTS	Used as the service account by SQL Server Reporting Services data sources. This account should not be an administrator on Team Foundation Server computers. This account must have the Allow log on locally permission on Team Foundation Server computers.

2- Login to the server using the Setup Account that you just created, TFSSETUP for example.
3- Install IIS 6.0 with ASP.NET enabled and without FrontPage server Extensions.
4- SQL Server Enterprise or Standard Edition. Please take note of following when installing SQL Server:

• Install SQL Server as the default instance. Don’t name the instance.
• Use the local system account for the service account. This should not be confused with the two service accounts you created prior to installation.
• Install following services from SQL Services:

              - Database Services
              - Analysis Services
              - Reporting Services
              - Integration Services

• Don't need to install Replication.
• On the Report Server Installation Options page in the setup wizard, select "Install the default configuration".

5- Install SQL Server Hotfixes.
6- Install .NET Framework 2.0 Hotfix KB913393. It’s available on the TFS installation CD.
7- Install Windows SharePoint Service Service Pack 2. Install it with "Web Farm" option. You can download SharePoint Service Service Pack 2 from here.
8- Eventually, install Team Foundation Server. During installation, the setup wizard asks about the Service Logon Account and Reporting Logon Account which you got to use the TFS Service Account (TFSSERVICE) and TFS Report Account (TFSREPORT) respectively.
 
Verification

To make sure the installation was successful, after restarting the server, open a browser and open http://localhost:8080/services/v1.0/Registration.asmx and click on GetRegistrationEntries. On the GetRegistrationEntries page, click Invoke without entering a ToolID. GetRegistrationEntries is a web method of a web service that is running on the Application Tier. The SOAP response from web service should look like this:

<?xml version="1.0" encoding="utf-8" ?>
<Type>vstfs</Type>

Installing a double server is not any different except that components are installed in different locations. Please feel free to share the problems you faced during TFS installation or administration.