Channels ▼
RSS

Database

Protecting Your Data with Row Level Security for SQL Server Databases


Bob Lambert is a Lead Consultant/Architect with CapTech Ventures, specializing in data modeling and database applications. He can be contacted at blambert@captechventures.com


Data security is not optional in today's business environment. High-visibility hacking and fraud, Sarbanes-Oxley, HIPAA regulations, and the Patriot Act all reinforce the need to present the right data to the right users and prevent the wrong ones from gaining access. Typically, "row level security" is one requirement: to allow or permit access to particular users based on data in the row. SQL Server does not provide built-in row level security.

In this article I present a code example (see Listing One) showing a method for providing row level security that restricts user access to data based on data in the row, without changing content of business tables, and without affecting application or presentation developers, and regardless of how users access the data. I use this as an example application: How to can I add security to my existing orders database that will limit managers to departments they manage -- and departments that report to those they manage -- regardless of how users get to the tables and what queries and reports are developed against the database?

--create table script
CREATE TABLE dbo.UserAccess
	(
	UserID varchar(20) NOT NULL,
	Department varchar(50) NOT NULL
	)
CREATE TABLE [dbo].[Orders](
	[OrderID] [int] NOT NULL,
	[CustomerName] [varchar](20) NOT NULL,
	[OrderTotal] [money] NOT NULL,
	[Department] [varchar](50) NOT NULL
	) 
CREATE TABLE dbo.Departments
	(
	Department varchar(50) NOT NULL,
	ParentDepartment varchar(50)
	)
--end create table script

--script to clear then populate example tables

	--clear tables
	Delete from departments
	Delete from orders
	Delete from useraccess

	--insert departments table
	INSERT 	INTO [dbo].[Departments] ([Department],[ParentDepartment]) VALUES ('North America','')
	INSERT INTO [dbo].[Departments] ([Department],[ParentDepartment]) VALUES ('East','North America')
	INSERT INTO [dbo].[Departments] ([Department],[ParentDepartment]) VALUES ('Southeast','East')
	INSERT INTO [dbo].[Departments] ([Department],[ParentDepartment]) VALUES ('Northeast','East')
	INSERT INTO [dbo].[Departments] ([Department],[ParentDepartment]) VALUES ('West','North America')
	INSERT INTO [dbo].[Departments] ([Department],[ParentDepartment]) VALUES ('Southwest','West')
	INSERT INTO [dbo].[Departments] ([Department],[ParentDepartment]) VALUES ('Northwest','West')

	--insert orders table
	INSERT INTO [dbo].[Orders] ([OrderID],[CustomerName],[OrderTotal],[Department]) VALUES (1,'Harris','11.00','East')
	INSERT INTO [dbo].[Orders] ([OrderID],[CustomerName],[OrderTotal],[Department]) VALUES (2,'Corrigan','22.00','Southeast')
	INSERT INTO [dbo].[Orders] ([OrderID],[CustomerName],[OrderTotal],[Department]) VALUES (2,'Corrigan','22.00','Southeast')
	INSERT INTO [dbo].[Orders] ([OrderID],[CustomerName],[OrderTotal],[Department]) VALUES (3,'Baldwin','33.00','Southeast')
	INSERT INTO [dbo].[Orders] ([OrderID],[CustomerName],[OrderTotal],[Department]) VALUES (4,'Pillow','44.00','Northeast')
	INSERT INTO [dbo].[Orders] ([OrderID],[CustomerName],[OrderTotal],[Department]) VALUES (5,'Carpenter','55.00','Northeast')
	INSERT INTO [dbo].[Orders] ([OrderID],[CustomerName],[OrderTotal],[Department]) VALUES (6,'Meyer','66.00','West')
	INSERT INTO [dbo].[Orders] ([OrderID],[CustomerName],[OrderTotal],[Department]) VALUES (7,'Gonzalez','77.00','Southwest')
	INSERT INTO [dbo].[Orders] ([OrderID],[CustomerName],[OrderTotal],[Department]) VALUES (8,'Hall','88.00','Northwest')
	INSERT INTO [dbo].[Orders] ([OrderID],[CustomerName],[OrderTotal],[Department]) VALUES (9,'Patrick','99.00','Southwest')

	--insert user access table
	INSERT INTO [dbo].[UserAccess] ([UserID],[Department]) VALUES ('BLambert','Southwest')
	INSERT INTO [dbo].[UserAccess] ([UserID],[Department]) VALUES ('MDavis','East')
	INSERT INTO [dbo].[UserAccess] ([UserID],[Department]) VALUES ('MDavis','Southeast')
	INSERT INTO [dbo].[UserAccess] ([UserID],[Department]) VALUES ('MDavis','Northeast')
	INSERT INTO [dbo].[UserAccess] ([UserID],[Department]) VALUES ('WSimmons','Northeast')

--end script to clear then populate example tables

Listing One: This listing provides SQL Server Scripts to create and load Example Tables.

Available Row Level Security Solutions

Many generally available database security approaches require addition of non-business-related columns to tables and therefore aren't consistent with the requirement to leave application tables untouched. For example, Implementing Row Level Security in SQL Server Databases by Narayana Vyas Kondreddii recommends addition of user id as a column on secure tables. In Implementing Row- and Cell-Level Security in Classified Databases Using SQL Server 2005, Rask, Rubin, and Neumann offer on the Microsoft Technet site a solution based on defining views that again requires base table modifications. And in A Fairly Capable Authorization Sub-System with Row-Level Security Capabilities (AFCAS) Kemal Erdogan presents a more promising solution, based on a lookup table, similar to the approach I present here. His solution meets one of the requirements, that is not requiring base table changes, but still leaves the tables unsecured if users access tables directly rather than through the given application.


Related Reading


More Insights






Currently we allow the following HTML tags in comments:

Single tags

These tags can be used alone and don't need an ending tag.

<br> Defines a single line break

<hr> Defines a horizontal line

Matching tags

These require an ending tag - e.g. <i>italic text</i>

<a> Defines an anchor

<b> Defines bold text

<big> Defines big text

<blockquote> Defines a long quotation

<caption> Defines a table caption

<cite> Defines a citation

<code> Defines computer code text

<em> Defines emphasized text

<fieldset> Defines a border around elements in a form

<h1> This is heading 1

<h2> This is heading 2

<h3> This is heading 3

<h4> This is heading 4

<h5> This is heading 5

<h6> This is heading 6

<i> Defines italic text

<p> Defines a paragraph

<pre> Defines preformatted text

<q> Defines a short quotation

<samp> Defines sample computer code text

<small> Defines small text

<span> Defines a section in a document

<s> Defines strikethrough text

<strike> Defines strikethrough text

<strong> Defines strong text

<sub> Defines subscripted text

<sup> Defines superscripted text

<u> Defines underlined text

Dr. Dobb's encourages readers to engage in spirited, healthy debate, including taking us to task. However, Dr. Dobb's moderates all comments posted to our site, and reserves the right to modify or remove any content that it determines to be derogatory, offensive, inflammatory, vulgar, irrelevant/off-topic, racist or obvious marketing or spam. Dr. Dobb's further reserves the right to disable the profile of any commenter participating in said activities.

 
Disqus Tips To upload an avatar photo, first complete your Disqus profile. | View the list of supported HTML tags you can use to style comments. | Please read our commenting policy.
 

Video