3 Approaches to Restricting Access to SharePoint Columns and Metadata

Greetings from sunny Maui, where I’m thrilled to be working from the home office for a few days before heading out to the east coast and to London for the International SharePoint Conference from April 23-25. For those of you who have not heard yet, the ISC (#iscolondon) is doing some truly innovative stuff with their event content, building a solution from beginning to end: from board room to whiteboard to dev to deployment to end user. They’ve got a spectacular lineup of speakers and for once I am actually going to get to attend some of the sessions myself. I highly recommend checking the event out if you have the chance of joining the crowd there!

Last week, I was in Las Vegas for SharePoint Connections, the SharePoint event within the DevConnections mega-event. More than 2,000 people attended, many to enjoy the release of SQL Server 2012 and the beta launch of Visual Studio 11. Just prior to that, I was honored to speak at the SharePoint Users Conference in Tokyo, Japan—my first time to that corner of the planet.

At those events, I was asked several times about a common SharePoint scenario: the desire to restrict users’ access to certain columns of metadata. That’s the focus of this week’s technical discussion.

Restricting Access to Certain Columns of Metadata

Out of the box, SharePoint supports access control to the item (or document) level. Access policies can be scoped at the folder, list (library) and site levels. Related security policies are configured at the site collection level (Site Collection Administrators membership and permission levels) and at the Web application level (User Access Policy).  Find more information at "What You Want to Know About the Managed Metadata Service."

But once a user has access to an item or document, it is not possible to restrict their access at a column level. The permission the user has to the item (view, edit, delete, create) is the permission the user has to all columns in the item.

Microsoft product group members have said, repeatedly and in all kinds of forums, that column-level security is not supported and, when asked about future versions of SharePoint, have said (in effect) “over our dead bodies.”

The issue seems to be performance. Column-level security would put such a burden on every activity that SharePoint and (more specifically) SQL would not be able to scale in the near-infinite manner that Microsoft requires in order to support a feature.

That doesn’t mean it’s not possible to achieve—just not out of the box. And before we talk about some approaches, always keep in mind the underlying issue is performance.

If Microsoft hasn’t been able to architect it in a way that’s scalable (near infinite-ly), it’s unlikely that anyone else can, either.

So whatever approach you take or solution you buy, it’s critical that you test the solution against anticipated projected workloads to ensure that the impact on performance is understood and acceptable.

Three Approaches to Securing SharePoint at the Column Level

One approach to securing column data is a security-by-obscurity approach. In other words, you make it more difficult to access the column in unwanted ways, without actually enforcing column level security.

This can be achieved using conditional formatting (created with SharePoint Designer or InfoPath, for example), that “hides” columns you don’t want a user to see, or control properties (created with InfoPath, for example) that specify a control is read-only, or hidden entirely. Building this logic into views and forms can be tricky, to say the least, but people have found some success pursuing this path.

A second approach involves leveraging the new “related lists” feature of SharePoint 2010, which allows you to “project” a field from a child list into the parent list. For example, an “orders” list, in which you select a customer, can display address and telephone information from the customer list. Those fields are, by nature, read-only in the order list.

Taking that approach to the next level brings us to my favored “easy” approach: connected web parts.

Imagine this scenario: You want to track Employee contact information, but you want social security numbers, salary, and other important private data to be available only to the Human Resources department.

Instead of putting all the data in a single list—and then having to try to “hide” columns—put the data in two separate lists, linked by a common field (e.g. Employee ID).

In views, display both lists, in two separate web parts. Visualize a web part on top where you select an employee and see their publicly-visible properties.

The web part below shows the data from the “Employee HR Data” list, which contains the more sensitive information. The web part on top is connected to the web part below, based on the Employee ID field.

When an employee is selected on top, the view below filters to show only the selected employee’s sensitive information.

Now here’s the cool part. If you aren’t in the HR department, and therefore don’t have access to the “Employee HR Data” list, you simply won’t see anything in the web part.

By separating data into lists based on common security requirements, then “stitching together” information in views and forms from the related lists, SharePoint can continue to enforce security at the list level but the effect is that you’ve secured specific metadata about a single item (employee).

The third option is to write custom code that enforces custom business logic for data access.

There are third-party solutions out there that address column-level security scenarios. Most (or all) of them use one of these approaches: conditional formatting, custom content controls or forms, related lists, or “middle man” injection into the content access processes of SharePoint.

Why Microsoft Doesn't Provide Column-Level Security in SharePoint

Again, none of these approaches are “evil” per se, but remember that Microsoft isn’t providing column-level security for a reason: performance. Make sure that you test any approaches against your workloads and content, to be sure that the impact on performance is understood and acceptable. Learn more at "Top 10 SharePoint 2010 Nuggets for Admins."

And if you find any great solutions—that you build, buy, or “work around,” please let me know (@danholme on twitter). This is a really common scenario and I’ll be interested to learn from YOUR experiences!

Discuss this Blog Entry 5

on Dec 7, 2012
Hi Dan, I just came across this exact same issue with a Leave Register that contained some sensitive info e.g. reason for leave, leave entitlements, etc. I hadn't thought of splitting the data across 2 lists. It seems so simple yet it never occured to me. Unfortunately it only solves part of the problem though as we are trying to create a self service Leave Application form so the user creating the entry requires access to both data sources, at least initially which defeats the purpose of splitting the lists up. In circumstances where HR enter some of the data your solution would work fine, however, I can't think of a way of doing the form that doesn't involve some form of temporary datastore before workflow moves it to the correct place (and even that may not work without a elevating privileges). Can you think of an alternative way of dealing with this? cheers Dave
on Jul 27, 2013

There are ways to make Read only fields in SharePoint:

1. Using PowerShell to set ShharePoint column read only.

2. Using jQuery/Javascript to make read only fields

3. Using SharePoint Designer to make SharePoint list field read only.

http://www.sharepointdiary.com/2013/07/how-to-make-sharepoint-list-column-form-field-read-only.html#ixzz2aGyDVMQS

on Nov 26, 2014

Your "second approach" sounds like it might be just what we need. Can you provide more detail or an example of how it would actually be done? We are using Sharepoint Foundation 2013.

on May 20, 2015

I often like to use multiple lists and workflows between them to update one list with the master record. List 1 could update fields a, b, and c while list 2 could update fields d, e, and f. A third list could be the basis to create the record on the master list. Using the lookups, you can see the other columns but not update them. People only get permission to update the columns they need to by restricting them on the list they are responsible to update. It works well for HR functions.

on Jul 22, 2016

I developed a farm solution that restricts edit access to the entire record based on a column value. Not really column level security, but it does help in some scenarios where you want a document locked depending upon its metadata values.

Search for:
rrfreeman blogspot restricted-edit-event-receiver

Please or Register to post comments.

What's Dan Holme's Viewpoint on SharePoint Blog?

SharePoint expert Dan Holme shares tips, how-to's, ideas, and news about all things SharePoint, and more.

Contributors

Dan Holme

Dan Holme's 18 years of experience and his impact on hundreds of thousands of IT professionals and business decision makers have earned him a reputation as one of the world's most respected...
IdeaXchange

Come join the IdeaXchange conversation!

Read the latest from our Xperts, and make your voice heard.

Want to learn more? Check out the FAQs.

eBooks For You
Join the Conversation
Blog Archive

Sponsored Introduction Continue on to (or wait seconds) ×