—–
Update 10 January 2010: Thanks to Gints Plivna for observing that we had not posted the slides to this presentation, here they are: Pretty Good Row Level Security Slides. – Bob
—–
Thanks to those who attended Saturday’s Microsoft Code Camp (see http://richmondcodecamp.org/). Here are materials for the presentation “Pretty Good Row Level Security” which I did with Nic Morel, my fellow CapTech Ventures Lead Consultant.
Summary:
The presentation reviewed a security solution that limits access at the data level, leverages simple database protocols, requires minimal administration resources, applies to both users and application developers, and overcomes some of the vulnerabilities of default approaches like having the application use a “system” user id.
The solution presents a three layer security architecture:
1. Base tables are accessible only to DBAs.
2. A security layer including cross-reference tables relating user ids to key data like department or sales territory, and table-valued functions that accept user id as a parameter and deliver data from base tables with a join to the security cross reference tables.
3. A data access layer exposed to users consisting of views that access the table-valued functions, providing the current user id as a parameter. End users and developers only have access to this data access layer.
Email me if you’d like the powerpoint (a little big to attach here, even without the accent graphics), and I’ve pasted in the SQL scripts as a comment on this post. The second two examples run against the SQL Server AdventureWorks database that ships with 2005 Developer Edition.
One response to “SQL Server Row Level Security @ Richmond Code Camp 2009.1”
Here are the object creates and presentation scripts presented in
Pretty Good Row Level Security
At Richmond Code Camp 2009.1 (http://richmondcodecamp.org/)
Bob Lambert and
Nic Morel
CapTech Ventures, Inc.
blambert@captechventures.com, https://robertlambert.net
nmorel@captechventures.com
See also Protecting Your Data with Row Level Security for SQL Server Databases at Dr Dobbs Portal, http://www.ddj.com/database/215900773
Note: users other than dbo are granted only access to views, that is in example 2 mdavis should only have access to the view ordersummary, in the later examples bobama only to vssalestotals and vsemployee.
—————————————————————————
— Sales user Access table
USE [AdventureWorks]
GO
/****** Object: Table [Security].[SalesAccess] Script Date: 04/27/2009 08:11:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [Security].[SalesAccess](
[UserID] [varchar](20) NOT NULL,
[TerritoryID] [int] NOT NULL
) ON [PRIMARY]
GO
—————————————————————————
— HR User Access Table
SET ANSI_PADDING ON
USE [AdventureWorks]
GO
/****** Object: Table [Security].[HRAccess] Script Date: 04/27/2009 08:11:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [Security].[HRAccess](
[UserID] [varchar](20) NOT NULL,
[DepartmentID] [smallint] NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING ON
—————————————————————————
— select * from [Security].[SalesAccess]
UserID TerritoryID
——————– ———–
sordway 1
sordway 2
sordway 3
bobama 1
jmccain 2
jkerry 6
jkerry 7
jkerry 8
jkerry 9
mromney 7
mromney 8
dbo 1
dbo 2
—————————————————————————
— select * from [Security].[HRAccess]
UserID DepartmentID
——————– ————
bobama 4
bobama 5
bobama 6
dbo 5
dbo 6
dbo 7
dbo 8
dbo 9
dbo 10
dbo 11
dbo 12
dbo 13
dbo 14
dbo 15
dbo 16
(15 row(s) affected)
—————————————————————————
— table valued function accessing sales data joining to the sales security access table
USE [AdventureWorks]
GO
/****** Object: UserDefinedFunction [Security].[ufnGetSalesTotals] Script Date: 04/25/2009 12:25:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [Security].[ufnGetSalesTotals]
(
@UserId VARCHAR(20)
)
RETURNS TABLE
AS
RETURN
(
SELECT
pvt.[SalesPersonID]
,pvt.[FullName]
,pvt.[Title]
,pvt.[SalesTerritory]
,pvt.[2002]
,pvt.[2003]
,pvt.[2004]
FROM (SELECT
soh.[SalesPersonID]
,c.[FirstName] + ‘ ‘ + COALESCE(c.[MiddleName], ”) + ‘ ‘ + c.[LastName] AS [FullName]
,e.[Title]
,st.[Name] AS [SalesTerritory]
,soh.[SubTotal]
,YEAR(DATEADD(m, 6, soh.[OrderDate])) AS [FiscalYear]
FROM [Sales].[SalesPerson] sp
INNER JOIN [Sales].[SalesOrderHeader] soh
ON sp.[SalesPersonID] = soh.[SalesPersonID]
INNER JOIN [Sales].[SalesTerritory] st
ON sp.[TerritoryID] = st.[TerritoryID]
INNER JOIN Security.SalesAccess sa /* this is the added join */
ON sa.TerritoryID = st.[TerritoryID]
AND sa.UserId = @UserID
INNER JOIN [HumanResources].[Employee] e
ON soh.[SalesPersonID] = e.[EmployeeID]
INNER JOIN [Person].[Contact] c
ON e.[ContactID] = c.ContactID
) AS soh
PIVOT
(
SUM([SubTotal])
FOR [FiscalYear]
IN ([2002], [2003], [2004])
) AS pvt
)
—————————————————————————
— View passing userid to the function above
USE [AdventureWorks]
GO
/****** Object: View [Security].[vsSalesTotals] Script Date: 04/25/2009 12:26:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [Security].[vsSalesTotals]
AS
SELECT
[SalesPersonID]
,[FullName]
,[Title]
,[SalesTerritory]
,[2002]
,[2003]
,[2004]
FROM Security.ufnGetSalesTotals(USER)
—————————————————————————
— table valued function accessing employee data joining to the HR security access table
USE [AdventureWorks]
GO
/****** Object: UserDefinedFunction [Security].[ufnGetEmployeeData] Script Date: 04/25/2009 12:26:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [Security].[ufnGetEmployeeData]
(
@UserId VARCHAR(20)
)
RETURNS TABLE
AS
RETURN
(
SELECT
e.[EmployeeID]
,c.[Title]
,c.[FirstName]
,c.[MiddleName]
,c.[LastName]
,c.[Suffix]
,e.[Title] AS [JobTitle]
,edh.DepartmentID
,dpt.Name AS [DepartmentName]
,shr.UserID
,c.[Phone]
,c.[EmailAddress]
,c.[EmailPromotion]
,a.[AddressLine1]
,a.[AddressLine2]
,a.[City]
,sp.[Name] AS [StateProvinceName]
,a.[PostalCode]
,cr.[Name] AS [CountryRegionName]
,c.[AdditionalContactInfo]
FROM [HumanResources].[Employee] e
INNER JOIN [Person].[Contact] c
ON c.[ContactID] = e.[ContactID]
INNER JOIN [HumanResources].[EmployeeAddress] ea
ON e.[EmployeeID] = ea.[EmployeeID]
INNER JOIN [Person].[Address] a
ON ea.[AddressID] = a.[AddressID]
INNER JOIN [Person].[StateProvince] sp
ON sp.[StateProvinceID] = a.[StateProvinceID]
INNER JOIN [Person].[CountryRegion] cr
ON cr.[CountryRegionCode] = sp.[CountryRegionCode]
INNER JOIN HumanResources.EmployeeDepartmentHistory edh
ON edh.EmployeeID = e.EmployeeID
AND edh.EndDate is null
INNER JOIN Security.HRAccess shr /* this is the added join */
ON shr.DepartmentID = edh.DepartmentID
and shr.UserID = @UserID
INNER JOIN HumanResources.Department dpt
ON dpt.DepartmentID = edh.DepartmentID
)
—————————————————————————
— View passing userid to the function above
USE [AdventureWorks]
GO
/****** Object: View [Security].[vsEmployee] Script Date: 04/25/2009 12:27:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [Security].[vsEmployee]
AS
SELECT
[EmployeeID]
,[Title]
,[FirstName]
,[MiddleName]
,[LastName]
,[Suffix]
,[JobTitle]
,DepartmentID
,[DepartmentName]
,UserID
,[Phone]
,[EmailAddress]
,[EmailPromotion]
,[AddressLine1]
,[AddressLine2]
,[City]
,[StateProvinceName]
,[PostalCode]
,[CountryRegionName]
,[AdditionalContactInfo]
FROM Security.ufnGetEmployeeData(USER)
GO
EXEC sys.sp_addextendedproperty @name=N’MS_Description’, @value=N’Employee names and addresses.’ , @level0type=N’SCHEMA’,@level0name=N’Security’, @level1type=N’VIEW’,@level1name=N’vsEmployee’
—————————————————————————
— Presentation scripts
————————————
— Dr Dobbs Example 1: dbo
————————————
— Connect blambert
use rls
–The database includes these orders
select * from orders
where department in (‘East’, ‘Southeast’, ‘Northeast’)
–Here are the contents of the user access table
select * from useraccess;
–and here are the results of a query summing orders accessible by
— a particular user
select * from getordersummary (‘blambert’)
— here’s the view encapsulating the getordersummary function
select * from ordersummary
select user
————————————
— Dr Dobbs Example 2: mdavis
————————————
— Connect mdavis
select user
use rls
–review the orders table
select * from orders
where department in (‘East’, ‘Southeast’, ‘Northeast’)
–review the user access table
select * from useraccess;
–review the getordersummary table-valued function
select * from getordersummary (‘mdavis’)
— review the ordersummary view
select * from ordersummary
————————————
— AdventureWorks Example 1: dbo
————————————
— Connect blambert
use AdventureWorks
–Adventureworks includes this view, providing a pivoted sales report
select * from Sales.vSalesPersonSalesByFiscalYears
–Here are the contents of the user access table
select * from Security.SalesAccess
–Here are territory codes
select * from Sales.SalesTerritory
–and here are the results of a query summing orders accessible by
— a particular user
select * from Security.ufnGetSalesTotals(‘jmccain’)
— here’s the view encapsulating the getordersummary function
select * from Security.vsSalesTotals
select user
————————————
— AdventureWorks Example 1: bobama
————————————
— Connect bobama
use AdventureWorks
–Adventureworks includes this view, providing a pivoted sales report
select * from Sales.vSalesPersonSalesByFiscalYears
–Here are the contents of the user access table
select * from Security.SalesAccess
–Here are territory codes
select * from Sales.SalesTerritory
–and here are the results of a query summing orders accessible by
— a particular user
select * from Security.ufnGetSalesTotals(‘bobama’)
— here’s the view encapsulating the getordersummary function
select * from Security.vsSalesTotals
select user
————————————
— AdventureWorks Example 2: dbo
————————————
— Connect blambert
use AdventureWorks
–Adventureworks includes this view, providing employee data
select * from HumanResources.vEmployee
–Here are the contents of the user access table
select * from Security.HRAccess
–Here are department ids
select * from HumanResources.Department
select * from HumanResources.EmployeeDepartmentHistory where enddate is null
–and here are the results of a query summing using the table valued function to list employees
select * from Security.ufnGetEmployeeData(‘jmccain’)
— here’s the view encapsulating the ufnGetEmployeeData function
select * from Security.vsEmployee
select user
————————————
— AdventureWorks Example 2: bobama
————————————
— Connect bobama
use AdventureWorks
–Adventureworks includes this view, providing employee data
select * from HumanResources.vEmployee
–Here are the contents of the user access table
select * from Security.HRAccess
–Here are department ids
select * from HumanResources.Department
select * from HumanResources.EmployeeDepartmentHistory where enddate is null
–and here are the results of a query summing using the table valued function to list employees
select * from Security.ufnGetEmployeeData(‘jmccain’)
— here’s the view encapsulating the ufnGetEmployeeData function
select * from Security.vsEmployee
select user