Channels ▼


Protecting Your Data with Row Level Security for SQL Server Databases

Row Level Security Code Example

I start with these assumptions:

  • SQL Server database (MS SQL Server 2000, 2005, or 2008; I'll talk about how to apply in other common DBMS's)
  • An attribute exists in common to all tables to be secured that makes sense as a determinant of who sees what data (in the example, department id)
  • Application calls passed to the database are secured by individual user id, not by a single admin user id.

For the example, the data structure in Figure 1 consists of (1) an Orders table including customer name, total receipts for the order, and selling department, (2) a department lookup table that contains a pointer to the parent department, and a "User Access" table linking users with departments they are allowed to see. The user access table includes rows for each valid user/department combination. If a user has access to a department to which many sub-departments report, then the parent department and each child department have a row that associates it with that user id.

Figure 1: This database model diagram shows the tables used in the code examples.

The key to making the order data secure is to protect it via SQL Server table-valued functions that require the user id of the current user as a parameter. Table-valued functions accept parameters like any other function, but return a table rather than a variable. Within the function, join secured tables to the user access table by the security attribute, limiting results to those where the security attribute is related to the given user id.

Example 1 defines a table-valued function that returns order count and total receipts for the orders, given the content of the UserAccess table limiting what a user is allowed to see.

CREATE FUNCTION [dbo].[GetOrderSummary] 
	@User_Id VARCHAR(20)
	SELECT 	count(*)  	as OrderCount, 
			sum(OrderTotal) as Receipts
		FROM 	dbo.Orders 	o
		Join 	dbo.UserAccess	ua
		On 	o.Department = ua.Department
		WHERE ua.UserID = @User_Id

Example 1: SQL that creates the table-valued function protecting Orders data.

And Figure 2 shows sample executions of the function, with tables populated as in Tables 1, 2, and 3.

Figure 2: Sample Table-valued Function Executions.

Table 1: Contents of the UserAccess example table.

Table 2: Contents of the Orders example table OrderID.

Table 3: Contents of the Departments table.

Exposing the Secured Tables

After protecting the secured tables with table-valued functions, you can then expose them to users in a view like the one listed in Example 2. The view calls the table-valued function defined above with the parameter "user", which in SQL Server is a built-in function that returns the current active user id.

CREATE VIEW 	[dbo].[OrderSummary]
SELECT		OrderCount,
FROM         	dbo.GetOrderSummary(USER)

Example 2: SQL that creates a view enabling secure user access to a protected table.

This view should be the only object to which the user is granted access, and will return permitted data to the user even if that user does not have rights to access the underlying table-valued function and the tables it in turn accesses.

Implementation Considerations

A real-world application of this approach may require more than one attribute to determine user row-level permissions, and would certainly apply security to more than one table. In addition, different data may have different security considerations. For example, a user may have access to sales results for a region but not summary salary data. The key to successfully applying this strategy in real world complexity is to collect and verify requirements from a business perspective, and then based on those requirements select the right attributes with which to enforce row level security.

Organizations using Active Directory (AD) can use this approach to secure by AD groups rather than userids simply by replacing user id with AD group in the UserAccess table.

For those working in databases other than SQL Server, DB2 supports table-valued functions with its CREATE FUNCTION (External Table) statement (see DB2 Version 9 for Linux, UNIX, and Windows), and on the mainframe supports row level security with its "multi-level security" capability (see Securing DB2 and Implementing MLS on z/OS.) Oracle supports row level security, as explained in Oracle Row Level Security: Part 1 by Pete Finnigan, and supports table-valued functions by allowing functions to return user-defined types where the user-defined type is a table.

Finally, the UserAccess table should be supported by a data maintenance application, as well as business ownership to manage security by use of that application. The application and the business processes around it must be designed to meet internal requirements and controls prescribed by audit and legal authorities like Sarbanes-Oxley and the Patriot Act.

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.