SQL Server Row Level Security @ Richmond Code Camp 2009.1

—–

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 thought on “SQL Server Row Level Security @ Richmond Code Camp 2009.1

  1. Bob

    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

Leave a Reply

Your email address will not be published. Required fields are marked *