SQL Server 2005 — Tools for row and cell level security

As if you needed more reasons to start using SQL Server 2005, we’ve got another big one!  You may have seen links we posted on implementing row level security in SQL Server 2005 in previous posts.  Now we are making available a toolkit for easily applying this design in your database.

The toolkit comes from our Federal MCS practice.  The centerpiece is a tool which allows you to logically define the security labeling scheme you wish to be used in your app’s database.  Based on this, at the click of a button the tool generates an implementation of the supporting framework described in the whitepaper.  All you need to do is create a simple view over the table(s) you wish to protect.  Support for insert/update/delete is added by writing simple instead-of triggers to capture these operations.  The toolkit documentation includes extensive design guidance and examples of implementing different scenarios.  Several working code samples are included as well.

Naturally, everything is based on the .Net Framework 2.0.  However, as can be seen in one of the examples (which uses Visual Basic 6.0), .Net is not required for apps which use a SQL Server 2005 database with row/cell level security.  As long as the client app can connect to SQL through a supported database access stack, it can make use of tables protected with row/cell level security.  This includes shrinkwrapped apps like Excel which can treat SQL 2005 as a data source.

The toolkit is a free download, and is not supported by Microsoft.  Download it here and let us know what you think!

 – Art Rask, MCS Public Sector

11/9: Updating location of file to http://www.federaldeveloper.com/Shared%20Documents/SQL%20Label%20Security%20Toolkit/Label%20Security%20Toolkit%20Install.zip

 11/22: If you have trouble hitting the download server, the file is attached to the blog entry here: http://blogs.msdn.com/federaldev/archive/2006/11/16/sql-server-2005-label-security-toolkit.aspx

Technorati Tags: , , ,

Comments (41)

  1. This morning was a jammed filled session covering off a lot of changes made to Microsoft SQL Server 2005….

  2. WilliamLeary says:

    I’ve playing with the toolkit now. We are trying to implement an enterprise security scheme and are reviewing this toolkit. Our goal is to be able to define rights and then  apply them to records for a user. For example, UserA should be able to delete Record1 in TableX, and UserB should be able to delete Record2 in TableX. Both could have read rights to each row.

    It appears that this row-level security function is designed for visibility, not for retrieving rows based on a perm. i.e. "What rows can user X perform a given action against?" (for a business app, this would be things like create a voucher for, approve expense for, etc)

    I’m just not seeing clearly how to use the toolkit to do this. I’m understanding how to lock rows down so you can’t even read them, but not seeing how you can retrieve rows checking for a certain ‘marking’ in combination with the current user. He may be able to see a whole bunch, but I want those filtered by a certain marking. Any help will be greatly appreciated.



  3. MSDNArchive says:

    You are exactly right when you say "this row-level security function is designed for visibility".  The purpose of the toolkit is to implement label-based access control, which is about controlling row visibility (and write-ability) based on row security labels.  Defining arbitrary permission types (e.g., Approve, Deny, Create Voucher) and applying them to rows is not the original intent of label based access control.  Label based access control (and this toolkit) is also  designed with groups of users in mind, rather than individuals.  For example, "all the people in East Region Accounting" or "all the people with Top Secret security clearances", as opposed to "John Doe’s manager" or "the salesperson assigned to XYZ Corp."

    However, with some creativity it may be possible for the toolkit to help you here.  If the permissions you want to grant, and the people you want to grant them to, can be grouped together into roles, then you can express those permission as markings.  Lets say groups A, B, and C are distinct groups who can approve purchase orders.  You could label the approriate rows with these markings, and manage membership in the A, B, and C roles to associate the correct people with these permissions.  Then, rather than using the labels to restrict visibility on SELECT, you could use the helper functions in the toolkit to check whether the user’s permissions satisfied the label on the row, before executing whatever action was requested.

    Or, if this doesn’t quite match your requirement, you might consider augmenting the toolkit’s framework with some design work of your own, which is focused on mapping individual users to arbitrarily defined permissions (such as Create Voucher).  In this case, the toolkit would give you an easy and powerful way to control visibility to portions of the underlying data set, and your additional custom work would meet your other requirements.

  4. WilliamLeary says:

    Thanks for the reply. I’m glad I came across the toolkit, as I will incorporate some of its ideas into the design. Particularly it’s use of standard, consistent, schema objects. (i.e. the view that gets added for each table, the standard column added to the table) I was planning on trying to build a defined set of schema objects to try to handle all ACL needs, but see the performance and flexibility your model provides, so have changed direction.

    We also generally will be using a trusted subsystem for many of these apps (since we do not yet have Kerb everywhere) so, while we were planning on experimenting with EXECUTE AS with the toolkit, it’s probably not going to meet our current needs as nicely as a custom solution.

    Great work though, and whenever I need to lock down rows, this is it!

  5. Steven says:

    Tried to run the app but received an error.  "Could not load file or assembly ‘LabelInfo, Version=, Culture=neutral, PublicKeyToken=3916adce30aaa243’ or one of its dependencies. An attempt was made to load a program with an incorrect format."

  6. MSDNArchive says:

    Steven: Sounds like you are either missing the .Net Framework 2.0 or you have a damaged or corrupt file.  Make sure you have the .Net Framework 2.0 installed, then try downoading the toolkit installer again.  Uninstall your current copy, and reinstall with the fresh download.

  7. Yong says:

    "Download here" link is broken now. Do you have another link for your tool?

  8. FederalDev says:

    Thanks for the note…we fixed that…sorry for the inconvenience.

  9. Solienko says:

    When will be available new version of toolkit?

  10. Snorro says:

    Is the "Download here" link broken again? Or is there another way to get the install?

  11. PubSec says:

    The file is available for download again. Our apologies for the inconvenience!


  12. Travis says:

    Is the updated link still correct? I can’t access via either link and wouldn’t mind getting my hands on this toolkit as soon as poss.

  13. MSDNArchive says:

    Try again.  It seems the host site was down for a few moments.

  14. K Sparrow says:

    Unfortunately I can’t access the link either it is giving a "An unexpected error has occurred." message, which is a pity as I would like to try out the toolkit

  15. Chris & Co says:

    The download link seems to be completely dead. Is there a way to download the toolkit from another location ?

  16. PubSec says:

    Again we apologize for the challenges with the download location — I’ve attached the zip file to this post — http://blogs.msdn.com/federaldev/archive/2006/11/16/sql-server-2005-label-security-toolkit.aspx

  17. René says:


    Is it possible to use the toolkit with SQL Server 2005 Express version ? I would like to try and reuse some of your ideas but don’t use the full-blown SQL Server 2005 (yet). As a workaround, I would like to test it on the Express edition, if possible.



  18. MSDNArchive says:

    Yes, the tool and its output work with SQL 2005 Express.

  19. Brendon says:


    We need to allow for subscribers to view a subset of data based on the username. Dynamic row filters seem to work well for this purpose. However, another requirement is that subscribers may only modify (insert, update and delete) a smaller subset of the all the data that they may view [at least at the publisher, it seems to be difficult to prevent this completely at the subscriber as the users are running as local admins]. There doesn’t seem to be a way to define different row filters for select, insert, update and delete. Please correct me if I am mistaken.

    Thus the row level security framework described in "Implementing Row- and Cell-Level Security in Classified Databases Using SQL Server 2005" and implemented in the toolkit was of great interest. Unfortunately I cannot see how this framework can be used in a replication scenario, as the underlying tables must also be published for the views on the subscribers to work.

    One possibility being investigated is to use dynamic row level filters to publish the viewable set of data for each subscriber and then to use INSTEAD OF INSERT/UPDATE/DELETE triggers (similar to those mentioned in the white paper) on the publisher that will run during synchronization.

    Is there a better way to achieve this, or are there any thoughts on this approach? Apologies if this is too off-topic, should comment be posted elsewhere?


  20. Brendon says:

    Just to add to the paragraph: "… to use INSTEAD OF INSERT/UPDATE/DELETE triggers …", the intention is to create the triggers on the base tables (not on views) as they would be updated during synchronization.

  21. Francisco Imperio says:

    I am running into a problem with your toolkit.  If you use windows authentication and a user is a domain administrator, he will automatically get mapped to dbo.  This means that the SQL user object I created for this login is not used and thus the database roles I assigned to the user object are not used.  From my viewpoint, the toolkit fails.  

    Am I missing something?

    I would like domain admins to fall under the same "rules" as other users when using the toolkit.  Is this possible?


  22. MSDNArchive says:

    Francisco: The behavior you are seeing is a result of the the way the SQL Server IS_MEMBER function works.  If the current user is a member of the built-in sysadmin server role, calls to IS_MEMBER for user-defined groups will always return 0. Thus any attempt to grant a sysadmin access to data protected by this toolkit will not allow them to see it.  This is not a surprise, and the good news is the behavior results in less access than you might expect rather than more.

    Two comments on this.  First, if you really need to you can circumvent this you can write your own custom is_member function in your database.  You would then modify the framework produced by the toolkit to call your custom function everywhere that it currently calls IS_MEMBER.  This works; I have done it for a customer before.  This would definitely be considered an ‘advanced’ modification, though.  Be careful.

    Second, ask yourself what you are trying to accomplish by granting sysadmins restricted access to application data with this toolkit.  Sysadmins can get to anything they want if they try.  If possible, get your users to use non-Domain Admin accounts when accessing your application.  This is a good administrative security principal anyway.

    Hope this helps.

  23. Jerry Nellis says:

    Francisco, just rewrite your secured views comprising them of two select statements (the original select in the view, plus one that returns all records for administrators) unioned together:

    Select Name

    From Country With (READ-COMMITTED) Join vwVisibleLabels On

      (Country.RLS_ID = vwVisibleLabels.Id)


    Select Name

    From Country

    Where IS_SRVROLEMEMBER(‘sysadmin’) = 1

  24. Jerry Nellis says:

    I guess technically I didn’t answer the question, I just created a solution that allowed administrators full access to all data in the base tables…

  25. Anand says:

    >>>First, if you really need to you can circumvent this you can write your own custom is_member function in your database.  You would then modify the framework produced by the toolkit to call your custom function everywhere that it currently calls IS_MEMBER.

    What will be inside this custome IS_MEMBER function? How can it return 1 for sysadmins being members of user defined roles?

  26. Paul Hester says:

    I’m with MCS US – West Region, currently working with one of our ISV’s on a payroll system. They are currently implementing cell level security via entity web-services, and have engaged us to architect a solution for using SSRS against them. I’m looking at alternative methods of cell level security, such as your framework, to simplify working with SSRS. However, am I correct that because the views essentially wrap protected columns in decrypt functions, that if we join or filter on those columns, SQL will be unable to use any indexes for optimal performance? If possible, please reply to my internal Microsoft email address (lookup Paul Hester).


    Paul Hester, Principal Consultant

    Microsoft Consulting Services, Denver, CO

  27. merill00 says:

    Is source code available since the tool is not supported?

  28. Dan says:

    Its a .NET app. Source can be viewed with Reflector. CLR assemblies can be exported from the database to a file with a command similar to this…

    bcp "select content from DATABASENAME.sys.assembly_files" queryout C:Sample1.Functions.dll -S "MACHINENAMESERVERNAME" -T

  29. Sergio says:

    Is there a example (Front-End, or Web App) for C# anywhere? I was able to get the SQL side to work fine, but in trying to create a User front end or .net app to interface the database I am getting alot of issues when trying to Insert, Update or Delete rows in a Gridview when trying to access the Triggers on the backend.

    Any help would be great.


  30. Frans says:

    Do you have a 64bit version of the toolkit, or a link to the source code so I can compile it for a 64bit OS.