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!