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.

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)
)
RETURNS TABLE 
AS
RETURN 
(
	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
)

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

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]
AS
SELECT		OrderCount,
Receipts
FROM         	dbo.GetOrderSummary(USER)

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.