Column Level Security in SharePoint

Introduction

Windows SharePoint Services 3.0 and Microsoft Office SharePoint Server 2007 support a rich security model that allows administrators to control access to sites and content by assigning permissions to users and groups for a specific securable object (such as site, list, library, folder and even an individual document or item).

However, in certain scenarios there is a need to secure access to individual columns in lists or document libraries. Currently, SharePoint does not provide out of the box support for securing columns or views. A typical scenario that would require this might be a list that contains a broad spectrum of information about an employee or a client, where certain columns (salary, revenue to date, potential to promote, etc.) might ideally only be viewable by certain groups within the portal.

To address those scenarios, this paper describes a method to leverage SharePoint extensibility and built-in item to level security to allow applying column-level permissions to a custom field type. This is accomplished through the use of a lookup field as the column, with behind the scenes ties to another list that contains the secure values and a method to provision those values back to the lookup only for users with valid permissions.

The result is that in the view mode authorized users will see the content of a secure column as if it was a normal column, while unauthorized users will not see the content of the column at all. This different behavior is shown in Figure 1. Similarly, only authorized users will be able to access the content of a secure column in new and edit modes.

Figure 1 Secure Column as viewed by an authorized and unauthorized users

Column-Level Security: Solution Architecture

To design a custom column-level security solution, the following aspects need to be addressed:

• Storing data in a secure manner, without exposing secured data to unauthorized users
• Rendering secured data only to authorized users
• Allowing modification of secured data only by authorized users

In order to resolve these issues, we decided to use a Custom Field Type for data rendering and processing and to use Item Level Security for data storage.

Custom Field Types together with Content Types and List Forms are primary extensibility mechanisms in Windows SharePoint Services that allow customizing data access, rendering and processing. You can read more about Custom Field Types in Windows SharePoint Services 3.0 SDK here.

The primary role of our custom field type is to ensure that only users with proper authorization can view or modify the data. We implemented a custom field type called Secure Column that can be used in any SharePoint list.

For storing data, one of the options that we considered was to use a separate table in a separate database. This could be a good option for SharePoint implementations that already use a separate database for other purposes. However, considering that many SharePoint solutions do not use any non-SharePoint databases, for the purpose of this article and sample, we decided to use a SharePoint List as the storage mechanism and to not introduce any dependencies on separate databases.

Using a SharePoint List for backend storage of secured data provides the ability to create a List Item for each piece of data to secure. To provide the appropriate level of security guarantee, the Item-Level Security feature will be used. The promise here is that our column-level security should be as secure as the built-in SharePoint Item Level Security.

It is important to note the implementation decisions to use a SharePoint List and Lookup column functionality also has some inherent limitations. These limitations are: lookups on very large lists can hinder performance and Lookup columns are not able to be directed at lists which are part of a different Site Collection.

Implementation Details

To implement column-level security several custom components are required:

• Data Storage List
• Custom field type, including custom field and editor controls
• Deployment Solution Package

These components work together as depicted in Figure 2 to allow the user to create, view, and edit data stored in the secured column.

Figure 2 Implementation Overview

For the sample implementation a single Data Storage List is used for all data in a Site Collection. This allows for easy maintenance and is a simpler solution. One drawback to this solution is that there is a single point where all secure fields will depend. This could cause performance issues in a very large solution. If this occurs the example solution could be extended to create a new Data Storage List per secure field.

Data Storage List

While using a SharePoint list for storage of secure data provides many advantages like Item-Level security and full API support. It also poses several challenges:

• Visibility - A normal SharePoint list is visible to all users that have permission to it.
• Scalability - If a single list is used to store data from all secure columns in the system the number of items in this list could be very large and exceed recommended SharePoint limits and performance could be greatly reduced
• Permission Maintenance - If Item Level Security is used to secure each item, updating a column's permission could become very difficult.
• Creation - When a user adds a new column it does not necessarily mean the list exists.

Each of these challenges can be addressed using standard SharePoint list functionality or custom code added to our Custom Field Type.

To address the issue of visibility we create the list as a "catalog" list. This is similar to the way other system lists are created in the standard SharePoint implementation. Examples include: web part gallery, site template gallery, and master page library. In addition, the following properties of the SPList object are set to further hide the list:

• Hidden - Setting this value to true will remove the list from all standard UI elements.
• NoCrawl - Setting this value to true will ensure the SharePoint crawl engine will not include data from this list.
• OnQuickLaunch - Setting this value to false will ensure this list is not added to the quick launch navigation.

To address the scalability and permission maintenance issues we decided not to use a flat list, but instead to create a two-level folder structure to house groups of items. The first level of the folder structure corresponds to the list to which a given Secure Column belongs to. The second level of the folder structure corresponds to the given Secure Column itself. As the result, the number of items in a single folder never exceeds the number of items in a list to which given secure column belongs to, even if the lists contains multiple secure columns. This approach addresses the scalability issues and having this folder structure, we use the permissions of a second-level folder to represent the permissions associated with a given Secure Column. This eliminates the need to maintain permissions for each individual item.

Figure 3 Folder Structure

The final issue of creating this list is something that can be easily handled as part of the Custom Field Type. To do this we programmatically create the list if it does not exist when a new field is created.

Custom Field Type

As mentioned in the Solution Architecture section above, data that is logically represented by a Secure Column added to a SharePoint list, is not stored in that list. Instead it is securely stored in a separate, dedicated SharePoint list that we refer to as the Data Storage List (this list will have an internal name of _SecureFieldStorage and a site relative URL of _catalogs/_SecureFieldStorage). Retrieving data stored in this separate Data Storage List and displaying it in the context of a host list (i.e., the list that contains a Secure Column) is the primary purpose of our custom field type.

When looking to implement this functionality, we wanted to avoid starting from ground up by deriving our custom field type from the base SPField class. Instead, we wanted to leverage existing SharePoint functionality as much as possible. When considering the available out of the box SharePoint field types to use as a base for our custom field type, the lookup field type (SPFieldLookup) stands out.

The primary feature of a lookup field we want to use is its ability to be pointed at a field in another SharePoint list and retrieve this field's value based on a specific list item id, which is well aligned with what we needed. What more, using the lookup field functionality allows taking advantage of its internal implementation which leverages SQL joins and is very scalable while still being secure.

While the core functionality of the lookup field type is a great starting point for the functionality we needed, there are a number of mismatches between the standard lookup field features and our needs. These are where our custom code is used to extend the SPLookupField:

• The user must not be able to change what list the lookup column is related to.
• The user must be able to specify what permissions should be set on the data related to the column.
• When a new column is created it should automatically set the related list and column to our custom Data Storage List.
• If the custom Data Storage List does not exist it should be created automatically.
• When displaying the value of this field, the value should not be rendered as a hyperlink to the related list item.
• When in edit or new mode this field should render as if it was a standard text column and any changes should be persisted to the targeted list.
• When in edit or new mode the data value of this field should be hidden if the user does not have appropriate permissions.

Custom Field Type Xml

An important component of any custom field type implementation is the custom fldtypes.xml file. In our case this file has been customized to point to our custom field type class:

<FieldName="FieldTypeClass"> SecureField.SecureField, SecureField, Version=1.0.0.0, Culture=neutral, PublicKeyToken=48a15d1316dd0f7d Field>

We also include a custom display pattern. The display pattern is identified below and will ensure this is not rendered as a hyperlink, which is the default for a Lookup column.

<LookupColumn HTMLEncode ="TRUE" "AutoHyperLink="FALSE""/>

The only other customization we have made was to specify a custom field editor control.

<FieldName="FieldEditorUserControl">/_controltemplates/SecureFieldEditor.ascx Field>

Custom Field Type Class

The custom field type class is the piece where most of the work for customizing the core lookup field functionality is located. This functionality includes:

• Creating the Data Storage List
• Create Data Storage List structure
• Set permissions on the Data Storage List
• Associate our custom Field Control
• Clean up the Data Storage List when a field is deleted

The class is inheriting from SPFieldLookup and it overrides the Update method so that whenever a secure column is created or edited, the backing Data Storage List is created and set with appropriate permissions.

public override void Update()
 {
     SPSecurity.RunWithElevatedPrivileges(EnsureSecureFieldStorageListExists);

     SPWeb web = SPContext.Current.Site.RootWeb;

     this.LookupWebId = web.ID;
     this.LookupField = secureFieldStorageFieldName;
     this.LookupList = web.Lists[secureFieldStorageListName].ID.ToString();

     RetrieveCustomProperties();

     SPSecurity.RunWithElevatedPrivileges(ApplyPermissions);

     base.Update();

 }

All of these actions are performed in an elevated permission level. This is to ensure even standard users can create new secure columns without error.

In addition, to this customization, we also need to override the FieldRenderingControl property to enable our custom field control to be used.

 public override BaseFieldControl FieldRenderingControl
 {
     get
     {
 	BaseFieldControl control = new SecuredFieldControl();
 	control.FieldName = this.InternalName;
 	return control;
     }
 }

The final customization is to override the OnDeleting method to ensure we clean up any related data when a column is deleted.

 public override void OnDeleting()
 {
     base.OnDeleting();

     SPSecurity.RunWithElevatedPrivileges(removeFieldFolder);
 }

Custom Field Editor Control

In addition to the core field type class, a custom field editor control is required to allow users to set the permission on a secure column. Permission can be set not only when the column is added to a list, but also can be updated anytime later. This functionality is implemented using a new user control which will include a SharePoint PeopleEditor control. This control enables the user to search and select principals.

<sharepoint:PeopleEditor ID="AllowedPrincipalsPeoplePicker" runat="server"
 AutoPostBack="false" PlaceButtonsUnderEntityEditor="true" SelectionSet="SPGroup"
 MultiSelect="true" />

To set and retrieve the security settings from the secure field type the code-behind class for the user control implements the IFieldEditor interface. Specifically, the InitializeWithField method is used to retrieve any existing security settings from the field.

if (Page.IsPostBack)
     return;

 // Initialize the people picker control using comma separated account list from the secure field

 SecureField secureField = (SecureField)field;

 if (secureField != null && secureField.AllowedPrincipals != null)
 {
     StringBuilder accounts = new StringBuilder();
     foreach (object entity in secureField.AllowedPrincipals)
     {
         accounts.Append((entity as PickerEntity).Key);
         accounts.Append(',');
     }

     this.AllowedPrincipalsPeoplePicker.CommaSeparatedAccounts = accounts.ToString();
     this.AllowedPrincipalsPeoplePicker.Validate();
 }

In addition, the OnSaveChange method is used to update the field security settings.

AllowedPrincipalsPeoplePicker.Validate();

 SecureField secureField = (SecureField)field;

 secureField.AllowedPrincipals = AllowedPrincipalsPeoplePicker.ResolvedEntities;
 secureField.SaveCustomProperties();

Custom Field Control

The final piece of the custom field type is the custom field control which implements the logic to create and maintain the data stored in the Data Storage List. To do this and still keep the existing lookup field functionality we inherit our custom class from the LookupField class. This base class handles all of the functionality when the field is in display mode. However, in new and edit modes we modified how the control works to meet our needs. The way to determine what mode the field control is currently in, we use the ControlMode property of the base class.

When the field control is in new or edit mode there are several changes to the default functionality to implement the following behavior:

• If the user does not have permission to this column, hide the control.
• Display a text box and populate it with any existing value stored in the backing list item.
• Create or update the value of the backing list item when the value is updated.

To control the visibility of the control the best method is to override the Visible property. In the implementation of the properties getter we check if the user has access to the data and return false if they do not.

In order to display a text box for the user to allow entering or editing data we can use an existing SharePoint template with the id TextField. This is the same template used by the standard text field. To implement this we simply need to override the DefaultTemplateName property's get method to implement the following.

// If the mode is Display default to Lookup Field functionality
 if (ControlMode == SPControlMode.Display || ControlMode == SPControlMode.Invalid)
 {
     return base.DefaultTemplateName;
 }
 return @"TextField";

To implement the remaining functionality to create or update the Data Storage List, we need to override the Value property's get and set methods. The get method will be used by the SharePoint framework to update the field value. Our Customizations to this logic are to create or edit the backing item located in the Data Storage List based on the values entered by the user. To ensure users without permission to modify lists, this functionality runs in at an elevated level where necessary. We also implement logic to ensure a user who should not be able to edit this field cannot. In this code we reference several helper methods. The code for these methods can be found at the end of this section.

// If the mode is Display default to Lookup Field functionality
if (ControlMode == SPControlMode.Display || ControlMode == SPControlMode.Invalid)
 {
     return base.Value;
 }

 this.EnsureChildControls();

 // Validate the current users permissions.
 if (!DoesUserHavePermissions())
 {
     return lookupListItemId;
 }

 // Check for an existing value to determine if we create new or edit.
 if (lookupListItemId == null)
 {
    SPSecurity.RunWithElevatedPrivileges(createLookupListItem);
 }
 else
 {
     SPSecurity.RunWithElevatedPrivileges(updateLookupListItem);
 }

 return lookupListItemId;

The set method of the Value property is used to set the current value of the field for the current List Item. Our customizations to this functionality need to retrieve the current value from the Data Storage List and populate our text box. This also implements functionality to ensure the security of the data.

// If the mode is Display default to Lookup Field functionality
 if (ControlMode == SPControlMode.Display || ControlMode == SPControlMode.Invalid)
 {
   base.Value = value;
   return;
 }

 this.EnsureChildControls();

 // Validate the current users permissions.
 if (!DoesUserHavePermissions())
 {
   return;
 }

 if( value != null)
 {
   if (value is SPFieldLookupValue)
   {
     SPFieldLookupValue fullValue = value as SPFieldLookupValue;
     lookupListItemId = fullValue.LookupId;
     this.TextBoxValue.Text = fullValue.LookupValue;
   }
   else
   {
   if (!(value is string))
   {
     throw new ArgumentException();
   }
   try
   {
     SPFieldLookupValue fullValue = new SPFieldLookupValue(value as string);
     lookupListItemId = fullValue.LookupId;
     this.TextBoxValue.Text = fullValue.LookupValue;
   }
   catch (ArgumentException ex)
   {
     this.TextBoxValue.Text = string.Empty;
   }
 }

The following methods are the helper methods which were used earlier.

private bool DoesUserHavePermissions()
 {
     bool doesUserHavePermissions = false;

     SPSecurity.RunWithElevatedPrivileges(delegate()
     {
         SPFieldLookup lookupField = this.Field as SPFieldLookup;
         using (SPSite site = new SPSite(SPContext.Current.Site.ID))
         {
             using (SPWeb web = site.OpenWeb(lookupField.LookupWebId))
             {
                 SPList list = web.Lists[new Guid(lookupField.LookupList)];

                 SPListItem subFolderItem = SecureField.GetOrCreateSubFolderItem(web, list, ListId, Field, false);

                 if (subFolderItem == null)
                 {
                     throw new Exception("Cannot find the List folder or Field folder.");
                 }

                 doesUserHavePermissions = subFolderItem.DoesUserHavePermissions(SPContext.Current.Web.CurrentUser, SPBasePermissions.ViewListItems);
             }
         }
     });

     return doesUserHavePermissions;
 }

 private void createLookupListItem()
 {
     SPFieldLookup lookupField = this.Field as SPFieldLookup;
     using (SPSite site = new SPSite(SPContext.Current.Site.ID))
     {
         using (SPWeb web = site.OpenWeb(lookupField.LookupWebId))
         {
             SPList list = web.Lists[new Guid(lookupField.LookupList)];

             SPListItem subFolderItem = SecureField.GetOrCreateSubFolderItem(web, list, ListId, Field, false);

             if (subFolderItem == null)
             {
                 throw new Exception("Cannot find the List folder or Field folder.");
             }

             // Create the list item.
             SPListItem listItem = list.Items.Add(subFolderItem.Folder.ServerRelativeUrl, SPFileSystemObjectType.File, this.TextBoxValue.Text);
             listItem[SecureField.secureFieldStorageFieldName] = this.TextBoxValue.Text;

             web.AllowUnsafeUpdates = true;

             listItem.Update();

             lookupListItemId = listItem.ID;
         }
     }
 }

 private void updateLookupListItem()
 {
     if (lookupListItemId == null)
     {
         return;
     }

     SPFieldLookup lookupField = this.Field as SPFieldLookup;
     using (SPSite site = new SPSite(SPContext.Current.Site.ID))
     {
         using (SPWeb web = site.OpenWeb(lookupField.LookupWebId))
         {
             SPList list = web.Lists[new Guid(lookupField.LookupList)];
             SPListItem listItem = list.GetItemById((int)lookupListItemId);
             listItem[SecureField.secureFieldStorageFieldName] = this.TextBoxValue.Text;

             web.AllowUnsafeUpdates = true;
             listItem.Update();
         }
     }
 }

Deployment

As with most custom development which extends SharePoint's core functionality, SharePoint Solution packages are the ideal deployment tool. This framework allows us to create a package which will be deployed to all servers in the farm in a central, consistent, and monitored way. Our implementation of custom-level security consists of one assembly, one Xml configuration file, and one control template file and is a perfect fit for a Solution package.

Final Solution

By using these components in conjunction with one another the user can add a secure column using the standard SharePoint interface as shown in Figure 4.

Figure 4 Creating a Secure Column

As part of adding the column the user can select the users and groups who should have access to the column. This functionality use the standard SharePoint "People Picker" control as is shown in Figure 5.

Figure 5 Configuring Permissions for a Secure Column

Once the column is added and configured a user with rights to the column can add data using the standard SharePoint new and edit forms as shown in Figure 6.

Figure 6 Editing a value of a Secure Column

A user who does not have permission to this column would not be allowed to edit or see the data within this column. This can be seen in Figure 7.

Figure 7 Edit mode when user doesn't have permission to edit a Secure Column

When this column is added to a view within SharePoint, only users with permission to this column would have access. This can be seen in Figures 8 and 9.

Figure 8 Viewing a list by a user with permission to a Secure Column

Figure 9 Viewing a list by a user without permissions to a Secure Column

Conclusion

This article has shown how to extend SharePoint to include column-level security and still keep all of the data within SharePoint. The strategies identified in this article enable the seamless addition of this functionality into any SharePoint environment. In addition, we have published full source code and deployment files of a working example at MSDN Code Gallery here.

Although we believe the approach presented in the article can sufficiently scale and is secure, we did not run any extensive tests and we fully expect that if Microsoft delivers a built-in column-level security in SharePoint in the future, it would offer better UI and better performance and scalability than our sample and it would be supported, while obviously our sample is not.

Some other things which were not addressed as part of the example are identified below:

• Cleanup of secured records when a parent is removed.
• Enabling the use of column types other than text.
• Separating the edit permission from the view permission for the column.

Look into improving column functionality when in datasheet view.

About the Authors

Matthew Dressel has over 8 years experience designing and implementing solutions for organizations intent on using the latest Microsoft technologies. His current work is focused on solution architecture and project leadership, for clients in the Education space looking to implement Microsoft's SharePoint technologies. Matthew has been working with the Microsoft SharePoint platform for over 3 years. His work experience includes implementations of the earliest releases of the product and the subsequent migrations to the current version. In addition to numerous successful implementations of 'standard' SharePoint, Matthew has been recently involved in several projects where the SharePoint platform is used as the basis for the creation of a new product or service. These projects utilize highly custom code and processes requiring careful architecture and a deep knowledge of the intricacies and the power of the platform.

Grzegorz Gogolowicz is a Senior Solutions Architect on the Global Partner Architecture Team at Microsoft with 15+ years of software development experience. Previously he was Technical Lead on Visual Studio Team System product group at Microsoft focusing on Team Foundation Server. In his current role, Grzegorz specializes in application platform architecture, including .NET, SharePoint and Azure Services Platform.

Additional Resources

For more information, see the following resources:

• Custom Field Types
• Users, Groups, and Authorization
• Plan site and content security (Windows SharePoint Services)
• Plan site and content security (Office SharePoint Server)
• Microsoft Office Developer Center