I was recently working on a SharePoint project and needed to figure out if an access control list (ACL) is replicated for a SharePoint object in database which is set to inherit security from its parent object. Functionally this won’t impact anything from an end-user perspective as its completely about SharePoint internals and how it manages data in its content database. However this was important for us for a couple of reasons:
- We needed to setup SharePoint sites with hundreds of users in thousands of document libraries and further thousands of documents in each document library. Each document library had unique security (i.e. not inheriting security from the site), but each document would inherit security from its document library. If the ACL was supposed to be replicated per document which was set to inherit security from its library, it would mean increased storage space would be needed and we needed to factor the same in our calculations.
- The security would be updated frequently on the document libraries via SharePoint web-service calls. If the ACL is replicated per document which was inheriting security, we would need to factor-in the time needed for updating all document ACLs too (internally in SharePoint) while making api calls to update document library security ACL.
So our basic question was whether an ACL is replicated per object that is set to inherit security from its parent. I asked this question on Stack Overflow:
The only answer that was posted as I was researching myself and documenting the same in this blog post did not address the question clearly, was inconclusive and even wasn’t in-line as per my own research.
After spending a considerable amount of time and research in exploring how security is stored underlying in SharePoint’s database, here’s what I have observed:
- A basic introduction to what is stored in what tables in SharePoint (a very basic introduction):
- Document information is stored in
- Document library information is stored in
- Contrary to what assumed earlier, the document content itself (raw document data) is also stored in the database itself in
DocStreamstables. There are ways to offload the same to NAS drive but out of the box, the documents are stored as byte column in database itself. Which means document size would actually add to Content database size’s limitation of 100 GBs.
As for permission storage:
Permstable stores all SharePoint objects that have unique security defined. If any object is set to inherit security, it won’t have an entry in
Permstable. Only those SharePoint objects which have an explicit ACL have an entry in
- Every entry in
Permstable gets a unique guid identified by
Actual ACL for any SharePoint object (only those objects on which ACL is explicitly defined) is stored in
RoleAssignmenttable. It contains 4 columns where:
PrincipalIdidentify the Site and principal this assignment belongs to.
PrincipalIdcould either be
Groupstable (if this role assignment is for a group) or
UserInfotable (if this role assignment is for a user).
UserInfotable are non-overlapping (none of them are Identity columns), SharePoint internally uses a common sequence to assign them values.
ScopeIdidentifies the SharePoint object this
RoleAssignmentbelongs to as described in above points.
RoleIddefines the Principal’s role in this ACL. The same is a lookup to
- Moving on,
Rolestable define a Principal’s permissions via
PermMaskcolumn. The same specifies permissions as bitwise combination of values available here:
- And oh, I also found out
Rolestable has a
PermMaskDenycolumn for exclusion security. Its supported in schema but no UI or logic is built around it:
- As soon as you break security inheritance for any object in SharePoint, it gets a single entry in
Permstable and as many entries in
RoleAssignmenttable as the number of Principals in the object’s ACL (remember here Principal is either a user or a group).
- As soon as you reset a security ACL of a SharePoint object to inherit security from its parent, the entries in both
RoleAssignmenttables are removed.So effectively ACL is NOT replicated for any object that is set to inherit security.
Apart from these, we should be aware of these fallacies about SharePoint. These are not directly related to our topic of this blog post, but I found these as part of my research in the same context:
- When deleting a documents, its security is not deleted if its set to unique permissions:
This has no other effect except perhaps on database size if lots of documents are not set to inherit security and then deleted.
A mitigating factor as per my own research is we have a document which is set to not inherit permissions, news rows are created in database. Then when its reset to again inherit permissions, those rows are deleted from SharePoint’s db. If you then delete the document, there are effectively no ACL related rows in SharePoint’s content db for that document.
- Important queries for a SharePoint db. These can help in your research in some way: