Item Level permissions

Item Level permissions

SharePoint has a robust object model supporting security at each level of the farm.  Let’s take a quick tour of some relevant methods and properties around item level reporting.

All securable objects have a method named GetUserEffectivePermissionInfo which is defined in the base class SPSecurableObject. This method returns back an SPPermissionInfo object which we can use to inspect the role definition bindings and corresponding permission levels. SPSecurableObject is imple,eented at the SPWeb, SPList, and SPLIstItem class level, hence how we assign permissions if needed at the site level.

 We can loop through the SPRoleAssignments objects via the RoleAssignments property. This will give us information about how the user is given access to the resource. This returns the Member (the account or group), the RoleDefinitionBindings (permission level). This is an excellent place to start if you are looping through each item.

 Next can look at the RoleDefinitionBindings property which returns back a collection of SPRoleDefinition objects that tell us about the type of access granted.

 Other important properties for reporting security include:

  • HasUniqueRoleAssignments, or the method returing the same thing: get_HasUniqueRoleAssignments()
  • RoleDefinitionBindings: collection of SPRole Definition objects returned.
  • IsSiteAdmin : a property of the user, indicates if a user is a Site Collection Admin ,which includes explicit permissions to everything
  • SPListItem.FirstUniqueAncestorSecurableObject: Retrieves the first unique ancestor if it has unique role assignments otherwise returns the first parent object (folder, list, or Web site) that has unique role assignments.
  • SPItem.AllRolesForCurrentUser

For a more general view of Security permissions in SharePoint, please see this TechNet article.

Solving asynchronous Event Handler update conflicts

Titus makes truly great and unique software for securing SharePoint documents based on metadata.   However I ran into a huge problem.  Some document uploads produced an error seemingly at random.  Reducing the number of documents, metadata fields, and content types had no effect.  This problem occurred in three totally separate farms and took way too long to solve, but a solution was indeed found by Titus, and was infuriatingly simple.  First some basics.

SharePoint supports both “Before” events and “After” events. Before events are raised when a specified action occurs before SharePoint writes to the content database, these are “ing” events, such as ItemAdding, these are always “Synchronous” as these are commonly used for custom validation where an event handler can cancel the “Adding” of an item.

First a definition on synchronous vs asynchronous.  A synchronous call is one in which the caller pauses and waits for a response.  An asynchronous call is one in which the caller continues without pausing for a response.  This allows both the caller and the method being called to execute at the same time.

To put a fine point on it, Before events such as ItemAdding are always supposed to be Synchronous, and this Titus event handler is apparently by default Asynchronous, hence our random problem.

The solution is to just make sure one of the Titus Event Handlers is set to process as “Synchronous”.   I took the Titus folk’s advice, and wrote the script below which loops through all libraries, and notes their “synchronicity” (I couldn’t resist that) and fixes any that are out of whack.

$url = "http ://sharepoint/sites/yourSite;$contextSite" = New-Object Microsoft.SharePoint.SPSite($url);
$contextWeb = $contextSite.OpenWeb();
 for ($LibIndex=0; $LibIndex -lt $contextweb.Lists.Count; $LibIndex++)
    $list = $contextWeb.Lists[$LibIndex];
    foreach ($ER in $list.EventReceivers)
         #write-host "Event $($i): $($"
         if ( $ -eq "TITUS Security Suite for Sharepoint ItemAdded")
             if ($ERtoUpdate -ne $null)
             write-host "DUPLICATE TITUS EVENT RECEIVER FOUND: Event $($i): $($ changes to Synchronous for $($list.title)"
    if ($ERtoUpdate -ne $null)
         if ($ERtoUpdate.Synchronization -eq 1)
             write-host "TITUS event handler was already set to Synchronous for Event $($eventNum): $($  $($list.title)" -ForegroundColor DarkGreen
             write-host "Event $($eventNum): $($ changes to Synchronous for $($list.title)"
         write-host "TITUS event handler not found for Event $($eventNum): $($ changes to Synchronous for $($list.title)" -ForegroundColor DarkGreen

Clarifying the Security Scope limits in SharePoint lists

Historically, Microsoft has defined it along the more conservative definition as “A scope is the security boundary for a securable object and any of its children that do not have a separate security boundary defined.”.  This implies that 10,000 uniquely assigned documents amongst five people (5*4*3*2*1 = 5! = 120) would be 10,000 security permissions, and not some number <=120.

After the number of unique security scopes exceeds the value of the List Query Size Threshold, SharePoint uses a code path that requires additional SQL round trips to analyze the scopes before rendering a view.  So the impact is on viewing the list of documents (and providing the security trimmed view), hence my suggestion in such situations to consider retaining or even reducing the SharePoint default View limit of 30 items per page .

To test this out, I did an extract from a Content DB that houses a Document Library I have handy with many custom permissions divided amongst eight users.  Probing the underlying data, it has a 18 distinct ACLs among 7K+ “Scopes” each with a unique ScopeURL and ScopeID (the internal Microsoft  field names).  Even though the ACLs are identical (SharePoint uses an ACL ID that I can pivot on to group the Scopes) each folder and often document has a unique scope, because of the way I had broken inheritance thousands of times, even though the ACL assignment was only one of a couple dozen permutations.  Here’s the SQL you can use if you wish to probe:

SELECT [SiteId], [ScopeId], [RoleDefWebId], [WebId], [ScopeUrl], [Acl]   
FROM [your Content DB].[dbo].[Perms]   order by scopeurl

An even better query exposes the web URL

SELECT [fullurl],perms.[SiteId] , perms.[ScopeId], [RoleDefWebId], [WebId], [ScopeUrl], [Acl]   
FROM [dbo].[Perms],[dbo].allwebs   
where perms.WebId =
order by scopeurl

As an aside, you can see the number of security principals per scope, using this SQL:

select COUNT(ra.PrincipalId) as [Count],p.ScopeUrl from RoleAssignment ra with(nolock) 
join Perms p with(nolock) 
on p.SiteId = ra.SiteId and p.ScopeId = ra.ScopeId 
group by p.ScopeUrl 
order by p.ScopeUrl desc

So that closes the issue.  The limit is not the unique combinations of ACLs, but instead the number of times permissions has been broken from a parent in the Document Library. So the guidelines might be:

  • Use folders whenever possible to encapsulate security
  • Break up huge Document Libraries along security boundaries
  • Never go past 50,000 unique permissions per list
  • You don’t want to go beyond 5,000 unique permissions per list without suffering severe performance impact
  • Don’t breech 1,000 unique permissions per list, otherwise you will see a performance impact of 20% or more
  • Never, ever feed a SharePoint Administrator caffeine after midnight…lol

Microsoft writes:
The maximum number of unique security scopes set for a list should not exceed 1,000.
A scope is the security boundary for a securable object and any of its children that do not have a separate security boundary defined. A scope contains an Access Control List (ACL), but unlike NTFS ACLs, a scope can include security principals that are specific to SharePoint Server. The members of an ACL for a scope can include Windows users, user accounts other than Windows users (such as forms-based accounts), Active Directory groups, or SharePoint groups.

Microsoft writes:

SharePoint 2010:
When a greater number of unique security scopes than the value of the List Query Size Threshold (default is 5,000) set to the web application are created for folders or documents in a list, there’s a significant performance degradation in SharePoint operations that badly affects end users operations(rendering content) as well as SharePoint activities like indexing SharePoint Content. After the number of unique security scopes exceeds the value of the List Query Size Threshold, SharePoint uses a code path that requires additional SQL round trips to analyze the scopes before rendering a view.

MS-Office Integration with SharePoint

I always assumed Microsoft’s strength was integration between its products, so I assumed that there would be good integration between SharePoint and Microsoft Outlook and Microsoft Office generally.  It was only after watching end-users struggle to navigate basic document operations in MS-Office and Outlook that I came to realize there’s a huge gap in that integration and the resulting user experience.  SharePoint at best appears as flat list of Recent Places shortcuts, leaving MS-Office and Outlook users flummoxed as they try to find or file a document within SharePoint.  Worse, metadata support is very limited; Office 2007 products don’t support new metadata fields such as Hierarchical Taxonomy (Managed Metadata Service) fields or External Content Types.  Even in Office 2010, the DIP (Document Information Panel) by default provides a random dump of the metadata fields and is far from user-friendly.  The DIP consumes valuable real-estate by default, with an unintuitive user interface for hiding/exposing it, and an unclear relationship between Document properties and SharePoint metadata fields.

As I explored add-on software to address these issues, I first came across and ultimately deployed a nifty product produced by Colligo.  It is highly focused on the Outlook end-user experience.  It provides drag-and-drop, full rich metadata support, and the ability to set up favorite SharePoint sites.  It works great at first, but there are some real issues to be aware of before committing to it:

  • Stability
    In my experience with this add-on, new releases often create problems as they fix other issues.  Before deploying, make sure the version intended for deployment is well-tested before deploying.  Once corrupted, we had to uninstall and then reinstall on the client desktop too often.
  • Caching
    Watch out for caching.  Changes to sites such as metadata don’t get reflected until a full synchronization.  When caching, a huge volume could get stored on the client desktop.  It blew up several of our thin clients, causing us to have to uninstall and reinstall the product, on more than one occasion, a user ran out of disk space and corrupted Colligo.
  • No site hierarchy

    Favorites need to be hand-entered in the 4.x version, and appear as a list of sites.  There’s no way to see the sites and libraries as a hierarchy, which is what users expect.

  • Colligo adds extra folders to the Mail Folders to represent the structure of Favorite sites. This can cause issues with the MAPI tree if the Favorite site has a lot of sub-sites and libraries.

Colligo has a 5.x version in the wings that offers two much-needed capabilities:

  • Central Administration of favorites
    You can now “push” favorites out to end users centrally.
  • Eliminating of the Site Caching
    Going to real-time is a smart move that goes to the heart of some of the problems in the prior version.

However, in testing, this version is not yet stable enough to recommend.  There are also real issues in the scrolling and display of metadata fields, preventing users from reaching some fields.

A far more comprehensive product line is offered by MacroView.  Like Colligo Contributor,  MacroView DMF (stands of Document Management Framework apparently) also provides drag-and-drop, full rich metadata support and the ability to set up favorite SharePoint sites.  In contrast to Colligo, MacroView shows the full site hierarchy in Outlook, and allows search views that can be customized to reflect the company metadata.  The MacroView menu and configuration options are very comprehensive. It already has the capability to push out Favorites and does not do any caching of file or metadata. MacroView offer an optional add-on that allows users to override and set security at the individual document level (which is another thing that is surprisingly difficult with the out-of-the-box SharePoint UI).  The SharePoint navigation and metadata experience with MacroView is consistent across Outlook, Word, Excel PowerPoint and the desktop client – even in Adobe Reader.  Lastly, the MacroView software is rock-solid reliable in tests and demos.

Customizing item-level security

Individual document security permission overrides carry its own challenges.  Each time permission is “broken” or customized in a document library, a new security scope is created.  Industry estimation is a performance hit of 20% is experienced at 1,000 security scopes in a library.  Microsoft defaults to a configurable maximum of 50,000 security scopes per library, with a recommendation to actually lower this to 5,000.

A product from a company called Titus offers a well thought out rules based security system that sets the security within the SharePoint model based on the metadata, using simple rules you defined.  While it has been the perfect product for some problems, we’ve had some reliability issues with it as well that we are working with Titus to resolve, where some document uploads fail the first time.

Library and Folder Security Gotchas

When setting up SharePoint security on sites, libraries and folders, there are quite a few options available, however not all approaches work as expected.  This article outlines some pitfalls to avoid, and best practices to keep your documents safe and sound, and lastly ensure an optimal end-user experience.

Top-down security

By far the best approach is to have the top level sites as open as possible, and gradually restrict access as needed on subsites, then libraries and finally if absolutely necessary folders.  If a library has broader access than its parent site, end-users will not be able to navigate to it.   There are two subtle problems to be aware of when violating this principle:

  • Granting broader access to Document Libraries
    When a user who does not have explicit Read access to a site accesses a document library within it, the browser and MS-Office Client will try to access Site-level information (such as the Document Information Panel, Content Types, Site columns etc) generating unnecessary end-user logon prompts.  Entering credentials will not succeed, although users can “escape” past these logons.  The better approach is to grant broader access to the site, and then lock down all other libraries.  A simpler approach I’ve used is to define a site-collection permission-level called “Site Reader” with access to pages but not documents.  This simplifies granting access and enables end users to navigate to their document libraries when fine-grained permissions are truly required.  Once you start customizing security for more than one document library it is worth asking yourself whether dedicated sites with custom permissions might be more appropriate.
  • Granting broader permissions to a folder
    Newbie administrators often try to grant broader permissions to a folder in a document library.  While the configuration seems straightforward, end-users cannot access the folder to which they have been granted read access.  The only approach that will work here is to grant the users broader access to the document library, then lock down specific folders to keep them out.

Avoid breaking inheritance

Everything within a Site Collection inherits security by default.  There are of course times that inheritance needs to be broken in order to lock down security.  Be aware that every time inheritance is broken it creates administrative overhead going forward, and is an opportunity for end-user confusion.

If you really need to give broader access to a folder, here’s how:

  1. Create a new Permission Level (details below)
  2. Assign the broader set of users to this permission level at the site level.
  3. Find the library where the site page(s) are located.  This is often called “Pages”.  Break inheritance, and add everyone as “Read” to this library.  That way users can view the landing page.

Here’s how to create the “Site Reader” permission level:
At the Site Collection level, go into “Permission Levels” under site security, and create a Permission Level called “Site Reader”, with the following permissions:

  • Use Remote Interfaces
    Use SOAP, Web DAV, the Client Object Model or SharePoint Designer interfaces to access the Web site.
  • Use Client Integration Features
    Use features which launch client applications. Without this permission, users will have to work on documents locally and upload their changes.
  • Open
    Allows users to open a Web site, list, or folder in order to access items inside that container.

Set your folder permissions.  Groups can be helpful, where a group is given the broader folder level access, and also Read access to “Pages” and Site Reader access to the site.

Not pretty, but it works…