Back at work, I had a requirement to set item level permission in a SharePoint Document Library. What does this mean? Below is a ‘diagram’
Diagram 1
It is very simple to explain in simple words – I want to set unique permissions on each file in my document library.
And the same in SharePoint words – I want to set unique permissions on a ListItem (SPListItem) which resides in a List (SPList) which again resides in a SharePoint Web Application (SPWeb).
The file resides inside a ListItem and thus you need to access the file via its ListItem. Using site settings, you can configure the default permissions for the document library. But there is no option to manage the default permission level for a ListItem. Whenever a new item is added to the document library, SharePoint adds the parent’s permission levels down to the child – i.e. document library’s permission levels to the list item – This is called Role Inheritance (Blue Line in the Diagram 1). So, if you want to add unique permissions to a list item, you need to first break this role inheritance and then add unique permissions.
The only way to achieve this is to write code. I had two options :
1) Handle adding unique permissions in any of the document library event handlers (of your choice)
2) If you have separate application dealing with adding items to the document library, then you can directly handle permissions in that application
Well, I needed both because I had to set permissions on the item when it was manually added to the library using the SharePoint website and also via the application :(
I started to investigate which event handler to use. Since I had to access item’s properties(fields), I had to choose ItemUpdated event handler. Why? Below is the ‘event sequence’ that happens when you upload a document in the document library:
Diagram 2
When a document is uploaded, first the item is added and then checked in. Once the item is checked in, SharePoint allows you to provide values to the custom fields that you may have for your item. Even for the default fields SharePoint adds them once the item is checked in. I am not sure how useful the ItemAdding event is as I was not able to even retrieve the item that was being added in that event. But once the item is added, I got the freedom to set permissions but still I was not able to grab the field values that user may enter, as the item is not yet checked in and the user is not presented with the input screen. Once the item is checked in, now the user gets the screen to input the field values and when the user clicks OK, the ItemUpdating and ItemUpdated events fire up!
That was when we use the SharePoint web UI.
If you use code to add an item to the document library, things happen when you add and then perform an update on the item.
Now remember that since I am using an event handler to set permissions, even If I perform an update via the code, my event handler will fire up, which is great!
This was easy : Wrote my ItemUpdated event handler, set the permissions. As I said earlier I had to break the role inheritance and only then set permissions on the item. Great! I was ready to test this. I had a separate application (web service) which deals with uploading files to the document library. I had a count of 600 files in the initial run to upload.
I hit the button and the application started uploading files..
first 50 – super fast, excellent
next 100 – fast, Excellent :D
next 100 – fast, Excellent :D
next 100 – slow, OK :-/
next 10 – dead slow, NOT OK :-/
next …. – dead slow as it was still dealing with the above 10 items :( :(
And as this upload was going on, our DBA guys came running to me (as they know am dealing with SharePoint database) saying that the CPU usage in the SharePoint database machine is 100% and the database transaction log is growing exponentially high! I was really confused and told them to wait until my 600 files were uploaded. After fifteen minutes I had to stop my application as the disk space where the database logs + database resides became full :(
My team was very furious now. My small application brought the database server down! DBA guys had set up only 5GB of space to the whole SharePoint database (That’s more than enough for our SharePoint Web Application) and they had enabled full transaction logging on the server. The logs were getting bigger and bigger that it went up to 2GB!
What was happening?
It took time but we eventually found out what was happening! The DBA guys were helpful in telling us about a stored procedure that was being called most of the time and also those calls being ‘threaded’ calls (multiple threads). Sample calls below:
WSS_Content_Workspace.dbo.proc_GetTpWebMetaDataAndListMetaData
WSS_Content_Workspace.dbo.proc_SecGetRoleDefskdc-dmz-consume-SharePoint-Config.dbo.proc_CompleteTimerRunningJob
WSS_Content_Workspace.dbo.proc_SecRemovePrincipalFromScope
WSS_Content_Workspace.dbo.proc_GetTpWebMetaDataAndListMetaData
WSS_Content_Workspace.dbo.proc_GetTpWebMetaDataAndListMetaData
That helped to us to start investigating with setting the permissions. So, there were two places:
1) Breaking the role inheritance
2) Setting manual permissions
We have two options when we break the role inheritance which is show below
You can choose to either copy the document library permissions’ to the item or not. Does this make a big difference? Well, YES!
If you choose to copy the permissions down to the item, then SharePoint copies the permissions to the item and does an item update! This makes our event handler to get invoked and that again sets permissions and then again my code has an item update after setting the field values, again, my event handler gets invoked and sets permissions. Unfortunately my code had set to copy the permissions down to the item :( And remember, the event handlers are asynchronous and thus those ‘threaded’ calls! Just think this for 600 files!
Now, you might have another question – how come copying down the permissions slowed things down ?
When we set unique permission to an item, SharePoint copies that permission back to the document library with ‘Limited Access’ permission level. SharePoint does this because it is not smart enough to find whether that unique permission already has access to the document library. SharePoint even goes beyond in adding this unique permission to the site as again SharePoint is not smart enough to find whether that unique permission already has access to the site.(Red Line in the Diagram 1) Why? Unless you have access to site, you cant access the document library and unless you have access to the library, you cant access the item! This goes on for each item, so at the 201st item we would have 200 unique permissions added to the document library and site. So, when we break the role inheritance with copy permissions set true – SharePoint will copy all those 200 permissions to the newly created item! We are now forced to remove those permissions and set our new permissions! That’s bad and that is what this blog post uses ! Don’t even try removing that permission level added to the document library because SharePoint is smart enough to remove the permission on any item inside that document library, thinking that that permission does not need to have access to the items!
So, the proper code to use when you set item level permission and break role inheritance would be :
if (!item.HasUniqueRoleAssignments){ item.BreakRoleInheritance(false);}
This does not copy down the permissions to the items :D
One small flag caused us a lot of trouble and you wouldn’t even notice this CPU hike, logs usage etc., unless you have a very restricted environment where everything is limited! I don’t want to get into various other problems that we faced as a consequence of this, but do not forget to really analyze your code step by step before you completely blame SharePoint ;)
Any links for good practices?
1) Common coding issues when using the SharePoint object model
2) Using Disposable Windows SharePoint Services objects
3) Roger Lamb’s cool posts about SharePoint 2007 and WSS 3.0 Dispose Patterns with examples