Unlike some other industrial-strength database servers, SQL Server lacks a built-in mechanism for protecting individual data records, called row-level security. This stairway level explores why you might want to use such a low-level granularity of data access security and how you can implement row-level security.

Row-Level Security

Between the releases of SQL Server 2000 and SQL Server 2005, Microsoft got serious about security. Bill Gates wrote his “trustworthy computing” memo to the company early in 2002, and as a result SQL Server 2005 received a massive security overhaul. One of the new security strategies was to make the authorization scheme far more granular than earlier versions, giving you fine-grained control over who could do what with which objects.

One glaring omission to the increased security granularity was that it didn't extend down to the row level. You have lots of options for controlling access at the table level, but SQL Server has no built-in access control mechanism within the table, commonly called row-level security. This is a problem in many scenarios, particularly when there are regulatory requirements for protecting data. For example, a patient table in a medical database might have the data for all of a clinic's patients, but only medical administrators should have full access to all patients while doctors and nurses should only have access to the patients they are directly caring for. There are many other scenarios that require allowing access to a subset of rows in a table, or even down to the cell level. But SQL Server has not had a built-in means of implementing row-level security.

Row-level security provides fine-grained access control over the individual records in a database table. The desired result is that the records are filtered by the execution context of a query, so that the user only has access to those that she has permissions for. This filtering uses the same security features in SQL Server that control access to other database and server objects and operations on those objects.

With SQL Server's lack of built-in row-level security, developers and administrators have had to devise a variety of clever and creative techniques to work around this shortcoming. Although these techniques often require a fair bit of extra work to set up, it can be worthwhile in order to exert fine-grained control not just over all the records in a table, but grant access to subsets of records in a table. The techniques can be relatively simple or can be as complex as you need, with security labeling and hierarchical categories of access control. Security labeling describes the sensitivity of a data item; you'll learn about the technique later in this level.

Our assumption here is that row-level security is best implemented at the database level. You can control access to database objects at the application level, but then you have to implement the security layer in every application that uses the database. Row-level security in the database means that the security is transparent to the application, and application developers don't need to even be aware that access control is in place.

Microsoft is finally beginning to address the lack of row-level security in SQL Server, driven by the need to host databases in the cloud. They first added row-level security to SQL Azure databases, and it will be a part of SQL Server 2016 as well.

Implementing Row-Level Security with Views

As you'll learn a bit later, SQL Server 2016 implements built-in row-level security. But until it's released and you move your databases to it, we'll need to implement it ourselves. Let's take a look at a simple implementation of row-level security using a common technique. Even though it is simple, it can be the basis for a more sophisticated approach. Say that a company has customer records that contain sensitive information, such as credit limits and felony convictions. (Yes, I know, it's a bit contrived, but let's run with it.) Only system admins (or top management) and the company representative assigned to the customer should be able to see a customer's data, all of which is simplistically stored in a single Customer table.

Listing 10.1 shows the T-SQL code to create the sample database and the single Customer table, as well as to insert some sample data. Everything is fairly straightforward, other than the inclusion of the UserAccess field in the table. This is the field that database code will use to restrict access. Keep in mind that this is a simple example; there are many other ways to do this.

IF DB_ID('RowLevelSecurityDB') IS NOT NULL DROP DATABASE RowLevelSecurityDB;
CREATE DATABASE RowLevelSecurityDB;
GO
USE RowLevelSecurityDB;
GO
-- Create the sample table that we want to protect with row-level security
CREATE TABLE Customer (
    CustId INT, 
    Name NVARCHAR(30), 
    City NVARCHAR(20), 
    CreditLimit MONEY,
    SocialSecurityNumber NCHAR(11), 
    FelonyConvictions INT,
    UserAccess NVARCHAR(50)
);
GO
-- Add some data to the Customer table
INSERT INTO dbo.Customer
       (CustId, Name, City, CreditLimit, SocialSecurityNumber, FelonyConvictions, UserAccess)
VALUES (1, N'Don Kiely', N'Fairbanks', 5.00, N'123-45-6789', 17, N'UserOne'),
       (2, N'Kalen Delaney', N'Seattle', 500000.00, N'987-65-4321', 0, N'UserOne'),
       (3, N'Tony Davis', N'Cambridge', 5000.00, N'', 0, N'UserTwo'),
       (4, N'E.T. Barnette', N'Fairbanks', 0.00, N'555-66-7777', 47, N'UserOne'),
       (5, N'Soapy Smith', N'Sitka', 0.00, N'222-33-4444', 32, N'UserTwo');

Listing 10.1: Code to create the sample database and table, as well as to add sample data.

All data access to the table will be through a view; the code in Listing 10.2 creates the MyCustomersView view that filters the data based on the user execution context. The interesting part of the CREATE VIEW code is the WHERE clause. It uses the USER_NAME function to get the user name from the execution context. The SELECT statement then returns all rows with the matching name in the UserAccess field. Alternatively, if the user is a member of the db_owner database role or a member of the server sysadmin role, the view returns all rows in the table.

IF object_id(N'dbo.MyCustomers', 'V') IS NOT NULL
    DROP VIEW dbo.MyCustomers;
GO
CREATE VIEW dbo.MyCustomersView AS
    SELECT CustId, Name, City, CreditLimit, SocialSecurityNumber, FelonyConvictions, UserAccess FROM dbo.Customer 
    WHERE UserAccess = USER_NAME() OR
        IS_ROLEMEMBER('db_owner') = 1 OR
        IS_SRVROLEMEMBER('sysadmin') = 1;
GO

Listing 10.2: Code to create the MyCustomersView view, which filters the data in the table based on the execution context.

That is all the code needed to set up the hold the data and provide access to it. The next block of code, shown in Listing 10.3, creates two database users. In this case the users don't need to be mapped to a server-level login, but they certainly could be, and would be in most situations. Then the code denies those users all access to the Customer table and grants select permissions on the view. That prevents the users from being able to directly access the table but allows them to access the data through the view. If you want to allow the user to make changes to the data via the view, you could grant those permissions as well.

CREATE USER UserOne WITHOUT LOGIN;
CREATE USER UserTwo WITHOUT LOGIN;
GO
-- Set permissions
DENY SELECT, INSERT, UPDATE, DELETE ON dbo.Customer TO UserOne, UserTwo;
GRANT SELECT ON dbo.MyCustomersView TO UserOne, UserTwo;
GO

Listing 10.3: Code to create two database users, restrict their access to the Customer table, and grant select permission on the view.

Now you can test the row-level security scheme, using the code in Listing 10.4. The first SELECT statement tests the view as a sysadmin. As you can see in the first set of results in Figure 10.1, this statement returns all the rows in the table.

-- Test as admin
SELECT * FROM dbo.MyCustomersView;    -- Should succeed and return all rows
-- Test as regular users
EXECUTE AS USER = 'UserOne';
SELECT * FROM dbo.Customer;            -- Should fail
SELECT * FROM dbo.MyCustomersView;    -- Should succeed and return 3 rows
REVERT;
GO
EXECUTE AS USER = 'UserTwo';
SELECT * FROM dbo.Customer;            -- Should fail
SELECT * FROM dbo.MyCustomersView;    -- Should succeed and return 2 rows
REVERT;
GO

Listing 10.4: Code to test the simple row-level security, first as sysadmin then as each of the database users.

Figure 10.1: Results of selecting data using the view as a sysadmin: all rows returned.

The second block of code in Listing 10.4 tests the row-level security view in the execution context of UserOne. In this case, there are two SELECT statements. The first attempts to read the data directly from the table, which tests the DENY permissions. And the second tests the use of the view. As expected, the first statement fails (Figure 10.2) while the second statement returns three rows of data (Figure 10.3), the rows with UserAccess equal to UserOne. We have row-level security!

Figure 10.2: Failure and success messages when testing the table and view as UserOne.

Figure 10.3: Rows returned from the MyCustomersView view when executing as UserOne.

The final block of code in Listing 10.4 again fails to retrieve any data directly from the table, but this time the view returns the two rows of data in the Customer table that UserTwo has access to, as shown in Figure 10.4.

Figure 10.4: Rows returned from the MyCustomersView view when executing as UserTwo.

There you have a very simple scheme for implementing row-level security. It's imple, but useful for understanding the basic concepts. But this way of doing things has a few problems:

• If you need to protect multiple tables, you may have to implement multiple views with varying degrees of complex logic to allow different users and roles access to protected data. And those views need to be kept in sync with the table when the schema changes over time.
• This example is based on user names, and assumes that each row is accessible by only one user. You'd probably need a way to filter by roles as well, and you also have to handle the situation where there is no unique user name.
• When a user name changes, you have to change data in one or more tables.

And there are likely to be other problems, depending on your unique scenarios.

But you can easily build on this scenario, and add whatever level of complexity that you need or want. A great source of ideas is a row-level security whitepaper from Microsoft, covered in the next section, which describes an approach to row-level security that is complex as the previous sample was simple.

Best Practice Row-Level Security from Microsoft

A more comprehensive, real-world-enabled example of row-level security would be well beyond the scope of one or a few levels in this SQL Server security stairway. But Microsoft has produced just such a sample as part of a whitepaper, Implementing Row- and Cell-Level Security in Classified Databases, primarily by Art Rask.

The approach described in the whitepaper was designed for the security needs of public-sector, classified databases with the highest level of security needs. It uses security labels and views to provide fine-grained access control, and even goes beyond the level of rows to protect access to individual cells, if you implement the full scheme. The whitepaper describes a security label like this:

A security label is a piece of information which describes the sensitivity of a data item (an object). It is a string containing markings from one or more categories. Users (subjects) have permissions described with the same markings. Each subject's permissions can be thought of as a label of their own. The subject's label is compared against the label on the object to determine access to that object.

The example used is that of a hierarchy of protection levels, using the familiar SECRET, TOP SECRET, and UNCLASSIFIED categories favored by the U.S. government. The scheme allows for multiple access criterions, such as the case where those security categories are further restricted by association with various projects or departments. For example, people in the NSA with a top secret classification shouldn't necessarily be able to access the FBI's top secret documents.

Implementation of this kind of cross-cutting, hierarchical security scheme for row- and cell-level security requires a fair bit of code, both to build the database objects to hold and give access to the data—mainly tables and views—as well as the code to maintain the system. And the whitepaper includes plenty of code! In addition to the code in whitepaper, Microsoft created a SQL Server Label Security Toolkit, , hosted on CodePlex, which accompanies the above referenced whitepaper. The toolkit includes a Label Policy Designer application, documentation, and examples of using the described implementation in various ways. An example of the SQL Server Label Policy Designer is shown in Figure 10.5. This application provides a nice GUI that lets you define your categories. Then the tool will generate the SQL code to build all the necessary objects and either run the code directly against your selected database or save the script file for optional modification and later execution.

Figure 10.5: The user interface of the SQL Server Label Policy Designer, with the categories of the sample scheme used in the whitepaper.

Even if you don't need the kind of flexible and extremely granular security scheme described in the whitepaper, you can learn a lot by reading the whitepaper and exploring the content of the toolkit. The options for implementing row-level security in your own database and applications are limited only by your creativity with the numerous tools available in SQL Server.

Row-Level Security in SQL Server 2016

The need for creative, custom row-level security schemes will be made largely obsolete by SQL Server 2016. Microsoft has announced that version will include built-in row-level security, a long-overdue feature that other database engines like Oracle have long had.

The feature is still under development, so this level will not cover it in any depth. But the way it will probably work is that you will define a security predicate filter as an inline, table-valued function, which becomes part of a security policy invoked when a user or application accesses protected data. Listing 10.5 shows a bit of the row-level security code used with CTP2 of SQL Server 2016, taken from the Row-Level Security preview topic in Book Online. The resulting security policy contains a single filter predicate that returns 1 when the SalesRep column in the table is the same as the user executing the query or is the Manager user. This is similar to the previous code in this level that used a UserAccess column and a view to filter the data.

CREATE SCHEMA Security;
GO
CREATE FUNCTION Security.fn_securitypredicate(@SalesRep AS sysname)
    RETURNS TABLE
WITH SCHEMABINDING
AS
    RETURN SELECT 1 AS fn_securitypredicate_result 
        WHERE @SalesRep = USER_NAME() OR USER_NAME() = 'Manager';
GO
CREATE SECURITY POLICY SalesFilter
    ADD FILTER PREDICATE Security.fn_securitypredicate(SalesRep) ON dbo.Sales
    WITH (STATE = ON);
GO

Listing 10.5: SQL Server 2016 code to create a security policy to implement built-in row-level security.

The ability to create a security predicate filter as an inline-table-valued function provides enormous flexibility when implementing the business logic for row-level security without requiring extensive support objects like the approach described in the classified database whitepaper. And the resulting transparency to the application accessing the data is just as good: the application doesn't need to know anything about row-level security in the database.

Microsoft's design goal with row-level security in SQL Server 2016 is for the performance to be on par with custom implementations. It remains to be seen if they'll achieve that in the final release, but I'm sure there is some very poorly performing custom code out in the wild that lowers the bar for them! In any event, this new feature of SQL Server 2016 will massively reduce the work needed to implement row-level security.

Summary

The lack of support for row-level security in SQL Server 2014 and earlier is a glaring omission in the otherwise granular security scheme built into SQL Server 2005 and later. But creative developers over the years have devised a number of clever schemes to let you secure data down to the row and cell level in a table, with varying degrees of complexity and flexibility. Nevertheless, it can be well-worth the effort to build a custom scheme, based on other published schemes or something of your own creation, so that you can more granularly protect your data. SQL Server 2016 will make it all easier by building support for row-level security into the database engine.