Email	Print Reprint
	add to:
	Del.icio.us Digg Google Furl	Slashdot Y! MyWeb Blink
	TABLE OF CONTENTS Available Row Level Security Solutions Row Level Security Code Example

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

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.

1 Available Row Level Security Solutions | 2 Row Level Security Code Example Next Page

RELATED ARTICLES Logging In C++ : Part 2 OutSystems Updates Agile Platform SDK, .NET Controls Released for Communications Enabling Web Pages Welcome To the World of Cryptographic Voting recls 100% .NET		TOP 5 ARTICLES No Top Articles.