Column-level security of SharePoint

Windows SharePoint Services 3.0 and Microsoft Office SharePoint Server 2007 support a wide range of security models, allowing administrators to put specific security objects (such as sites, lists, libraries, folders, and even independent documents and entries) permissions are assigned to users and user groups to control access to sites and content.

In some cases, you need to perform security control on Individual columns in the list and document library. Currently, SharePoint does not provide built-in support for security control of columns or views. A typical scenario that requires such a feature is to include certain columns (salary, salary payment date, promotion opportunity, etc.) in a list containing a large amount of employee or customer information) it is best to view the information only by a specific user group in this portal.

To meet the requirements of these scenarios, this article describes a method that uses SharePoint scalability and built-in entry-level security to allow basic column permission control on Custom field types. This is achieved by using a query field as a column. In this way, another list is closely associated, this includes security values and methods for returning valid values only for users with valid permissions.

The result is that in view mode, authorized users will see the content of the security column, just as it is a normal column. On the contrary, unauthorized users cannot see the content of this column. Different behaviors are shown in Figure 1. Similarly, only authorized users can access the security column content in the new and edit modes.

Figure 1: Security column view by authorized and unauthorized users

Column-level security: solution architecture

To design a Custom column-level security solution, you need to solve the following problems:

• Store data securely without exposing security data to unauthorized users.
• Only presents security data to authorized users
• Only authorized users are allowed to edit security data.

To solve these problems, we decided to use the Custom Field Type for data presentation and processing, and use the Item Level Security function for data storage.

Custom field Types are the same as Content Types and List Forms, which are the main extensions of Windows SharePoint Services and allow access, rendering, and processing of custom data. You can read about Custom Field Types in Windows SharePoint Services 3.0 SDK.

The primary role of our custom field type is to ensure that only authorized users can view or edit data. We have implemented a Custom Field Type named Secure Column, which can be used in any SharePoint list.

For data storage, we have considered using a separate data table in a separate database. This is a good choice for using a separate database for other purposes. However, considering that most SharePoint solutions do not use non-SharePoint databases, we decided to use SharePoint List as the storage mechanism for this article and example, instead of independent database dependencies.

Using the SharePoint List as the backend storage method of secure data, you can create a List Item for each piece of data that requires security control ). To provide an appropriate Level of Security assurance, the Item-Level Security feature must also be used. We hereby promise that column-level security should be as secure as built-in SharePoint entry-level security.

For decision-making, it is important to note that using the SharePoint list and Lookup columns feature also has some inherent limitations. These restrictions are: searching in a very large list will affect the performance, and the query Column cannot directly access the list in different Site collections.

Implementation Details

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

• Data Storage List)
• Custom field types, including custom fields and editor controls
• Deployment Solution Package)

As described in component 2, these components work together to allow users to create, view, and edit data stored in the security column.

Figure 2 Implementation Overview

For this example, a separate Data Storage List is used to save all the Data in this site set. This makes the solution simpler and easier to maintain. However, all security fields depend on a single storage point. In a very large solution, this may cause performance problems. In this case, the sample solution can be expanded to create a new data storage list for each security field.

Data storage list

Although the use of SharePoint list as a secure data storage method brings many benefits, such as entry-level security and complete API support. However, you also need to face several challenges:

• Visibility-a common SharePoint list is visible to all users with access permissions.
• Scalability-if a single list is used to store data from all security columns in the system, the number of entries in the list may be huge and exceed the restrictions recommended by SharePoint, performance may be significantly reduced.
• Permission maintenance-if entry-level security is used to ensure the security of each entry, updating column permissions may become very difficult.
• Create -- when a user adds a new column, it does not necessarily mean that the list already exists.

Each of these challenges needs to be addressed using the standard SharePoint list feature or by adding custom code in the Custom field type.

To solve the visibility problem, we created a "directory" list. This is similar to the list of systems in the standard SharePoint implementation. The directory list includes the Web Part Library, site template library, and master page library. In addition, the following attributes of the SPList object are also set to further hide the list:

• Hidden -- setting this value to False will delete the list from all standard UI elements.
• NoCrawl -- setting this value to True ensures that the SharePoint crawl engine does not include data in the list.
• OnQuickLaunch -- setting this value to False will ensure that the list will not be added to the Quick Start navigation bar.

To solve scalability and permission maintenance problems, we decided not to use a flat table, but to create a two-level folder structure to group these entries. The first level of the folder structure corresponds to the list of the given security columns. Level 2 and Security columns correspond to each other. In the end, the number of entries in a single Folder does not exceed the number of entries in the list of given security columns, even for the list containing multiple security columns. This method can solve the scalability problem. With this folder structure, we can use the second-level folder permission to indicate the permissions associated with the given security column. This also removes the need to maintain permissions for each independent entry.

Figure 3: folder structure

The last problem is that the created list can be easily processed as part of the Custom field type. To achieve this purpose, if this list does not exist when a new field is created, we will use code to create it.

Custom Field Type

As mentioned in the solution architecture section above, data is not actually stored in this list, but logically expressed by the security column added to the SharePoint list. Actually, the data is stored securely in an independent and dedicated SharePoint list, which is the data storage list we mentioned (This list has an internal name called _ SecureFieldStorage, and has a site relative URL called _ SecureFieldStorage ). Retrieving data stored in this independent data storage list and displaying it in the context of the host list is the main use of Custom field types.

When studying how to implement this function, we hope that our custom field types will be inherited from the basic SPField class to avoid starting from scratch. We should make full use of the existing features of SharePoint. If we consider using some available SharePoint field types as the basis for Custom field types, the query field type (SPFieldLookup) will be the best choice.

The main feature we intend to use to query a field is to point to another field in the SharePoint list and obtain the value of this field based on the specific list entry number. This is what we need. In addition, the use of the query field function allows the full use of some internal advantages-they benefit from the SQL Join function, while providing good scalability, but also good security.

Although the core functions of the Field Type query provide a good start for the functions we need, there are still many inconsistencies between the standard query field features and our requirements. This is the usage of our custom code -- used to expand the SPLookupField:

• You cannot change the list of columns associated with the query.
• You must be able to set permissions on the data associated with this column.
• When creating a new column, it should be able to automatically set related lists and columns to our custom data storage list.
• If the Custom Data Storage list does not exist, it should be automatically created.
• When the value of this field is displayed, the value should not be displayed as a hyperlink pointing to the related list entries.
• When in edit or new mode, this field should be displayed as a standard text list, and any changes should be permanently saved to the target list.
• When you are in edit or new mode, if you do not have the appropriate permissions, the data value of this field should be hidden.

Custom type XML

An important component for the implementation of any custom field type is the custom fldtypes. xml file. In our example, this file is 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 mode. This display mode is recognized in the following way, and it is not rendered as a hyperlink. It is used for Lookup columns by default.

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

The other one we define is to set a custom field editor control.

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

Custom Field Type

The Custom field type class contains the main code for customizing the core query field function. These functions include:

• Create a data storage list
• Result of creating a data storage list
• Set permissions on the data storage list
• Associate our custom field controls
• When a field is deleted, the data storage list is cleared.

This class inherits from SPFieldLookup and overwrites the Update method so that the data storage list on the back can be created and set with appropriate permissions at any time when the security column is created or edited.

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 these actions are executed at the strict permission level. This ensures that standard users do not create a new security column with errors.

In addition, to achieve this custom effect, we also need to rewrite the FieldRenderingControl attribute so that our custom field control can be used.

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

Finally, we need to customize the OnDeleting method to ensure that we can understand any related data when the column is deleted.

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

     SPSecurity.RunWithElevatedPrivileges(removeFieldFolder);
 }

Custom field editor controls

In addition to the core field type class, you also need a custom field editor control to allow users to set the permissions on the security column. Permissions Can be set when a column is added to the List, and can be updated at any time. This function is implemented using a user control that contains the SharePoint PeopleEditor control. This control allows users to search for and select security subjects.

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

To set and obtain information about the security field type, the post-code class of the user control implements the IFieldEditor interface. In particular, the InitializeWithField method is used to obtain 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 last part of the Custom field type is the custom field control, which implements the logic for creating and maintaining data stored in the data storage list. To achieve this goal, and retain the existing query field function, we let the custom class inherit from the LookupField class. When the field is in display mode, this base class handles all the functions. However, in the new and edit modes, we need to modify some places to make this control meet our needs. Currently, the ControlMode attribute of the base class is used to determine the mode in which the field control is located.

When the field control is in the new or edit mode, you need to make several changes to the default function to achieve the following behavior:

• If you do not have permission for this column, hide the control.
• Display the text box and fill it with any current value stored in the background list entries.
• When the value is changed, create and update the value of the background List entry.

To control the visibility of controls, the best way is to override the Visible attribute. In the implementation of the getter attribute, we need to check whether the user has the right to access the data and return False if the user cannot access the data.

To display text boxes for users who can enter or edit data, we can use an existing SharePoint template with the id TextField. This template is also used by standard text fields. To implement this function, we only need to simply override the Get method of the DefaultTemplateName attribute. The implementation code is as follows:

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

To create or update the remaining functions of the data storage list, we need to override the Get and Set methods of the Value attribute. The Get method is used by the SharePoint framework to update the field value. We define this logic to create or edit background entries in the data storage list based on user input values. To prevent unauthorized users from editing the list, this function runs under a strict security level when necessary. We also implement the logic to ensure that users do not edit fields that cannot be edited. In the code, we reference multiple auxiliary methods. The code for these methods can be found at the end of this section.

// If the mode is Display default to Lookup Field functionalityif (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 attribute is used to Set the field Value for the current list entry. We need to obtain the current value from the data storage list and display it in the text box. It also provides the ability to ensure data security.

// 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 some of the auxiliary methods we 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

SharePoint Solution package is the best deployment tool for most custom development that extends core SharePoint features. This framework allows us to create deployment packages and deploy them to all servers in the server cluster in a central, consistent, and verifiable manner. The custom security feature we implement includes an assembly, an XML configuration file, a control template file, and a moderate-size Solution package.

Final Solution

By combining these components, you can use the standard SharePoint interface shown in 4 to add security columns.

Figure 4: create a security Column

As part of adding a column, you can select which users and user groups can access this column. This feature uses the standard SharePoint "People Picker" control, as shown in Figure 5.

Figure 5: Configure permissions for security Columns

Once the column is added and users with corresponding access permissions are configured, the column can use standard SharePoint to create and edit a form to add data, as shown in figure 6.

Figure 6: edit the Security column Value

Users who do not have permission for this column cannot edit or view data in this column. As shown in figure 7.

Figure 7: Display Effect of the editing mode when the user does not have the permission to edit the Security Column

When this column is added to a view in SharePoint, only users with this permission can access it. You can see from Figure 8 and Figure 9.

Figure 8: users with security column permissions view the list

Figure 9: view the list by a user who does not have the security column permission

Conclusion

This article shows how to extend SharePoint to include column-level security and save all data in SharePoint. The Countermeasure verified in this article allows this feature to be seamlessly attached to any SharePoint environment. In addition, we have released a complete source Code and deployment file on the MSDN Code Gallery.

Although we believe that the methods demonstrated in this article can fully ensure scalability and security, we have not conducted extensive tests. We hope that, if Microsoft can include the built-in basic column security feature in the future SharePoint version, it will have a better UI, better performance and scalability than our example, in addition, it can also have after-sales support, and we obviously cannot do this in our example.

Other issues that have not been resolved in our example but have been verified are:

• When the parent object is deleted, you need to clear the security record.
• Allows users to use data types other than text
• Separates the edit permission of a column from the view permission.

You should consider how to improve the column function in the data table view.