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 https://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: https://blogs.msdn.com/federaldev/archive/2006/11/16/sql-server-2005-label-security-toolkit.aspx
Technorati Tags: SQL Server, SQL, Security, Microsoft
Comments
Anonymous
March 22, 2006
This morning was a jammed filled session covering off a lot of changes made to Microsoft SQL Server 2005....Anonymous
April 28, 2006
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.
Thanks,
WilliamAnonymous
April 30, 2006
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.Anonymous
May 02, 2006
The comment has been removedAnonymous
July 06, 2006
Tried to run the app but received an error. "Could not load file or assembly 'LabelInfo, Version=1.0.0.0, Culture=neutral, PublicKeyToken=3916adce30aaa243' or one of its dependencies. An attempt was made to load a program with an incorrect format."Anonymous
July 08, 2006
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.Anonymous
August 03, 2006
"Download here" link is broken now. Do you have another link for your tool?Anonymous
August 05, 2006
Thanks for the note...we fixed that...sorry for the inconvenience.Anonymous
October 05, 2006
When will be available new version of toolkit?Anonymous
November 02, 2006
Is the "Download here" link broken again? Or is there another way to get the install?Anonymous
November 06, 2006
The file is available for download again. Our apologies for the inconvenience! -DarrylAnonymous
November 15, 2006
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.Anonymous
November 15, 2006
Try again. It seems the host site was down for a few moments.Anonymous
November 16, 2006
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 toolkitAnonymous
November 21, 2006
The download link seems to be completely dead. Is there a way to download the toolkit from another location ?Anonymous
November 22, 2006
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.aspxAnonymous
January 16, 2007
Hi, 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. Regards, RenéAnonymous
January 16, 2007
Yes, the tool and its output work with SQL 2005 Express.Anonymous
September 04, 2007
Hi 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? Thanks.Anonymous
September 04, 2007
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.Anonymous
September 19, 2007
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? Thanks.Anonymous
September 20, 2007
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.Anonymous
November 08, 2007
The comment has been removedAnonymous
November 08, 2007
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...Anonymous
November 16, 2007
>>>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?Anonymous
July 08, 2008
The comment has been removedAnonymous
July 09, 2008
Is source code available since the tool is not supported?Anonymous
August 06, 2008
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" -TAnonymous
November 07, 2008
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. ThanksAnonymous
June 20, 2011
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.