Permission storage in SharePoint’s database

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:

1. 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.
2. 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:
https://stackoverflow.com/questions/48096487/sharepoint-security-inheritance-underlying-implementation
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:

1. A basic introduction to what is stored in what tables in SharePoint (a very basic introduction):
   https://sharepoint.stackexchange.com/questions/131049/what-data-is-stored-in-the-sharepoint-2010-content-database
   https://msdn.microsoft.com/en-us/library/dd587562(v=office.11).aspx
2. Document information is stored in AllDocs table.
   https://msdn.microsoft.com/en-us/library/jj594462(v=office.12).aspx
3. Document library information is stored in AllLists and AllWebParts tables.
4. Contrary to what assumed earlier, the document content itself (raw document data) is also stored in the database itself in DocStreams tables. 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.
   https://sharepoint.stackexchange.com/questions/100143/sharepoint-content-database-docstreams-table-very-large
5. As for permission storage:
   1. The Perms table stores all SharePoint objects that have unique security defined. If any object is set to inherit security, it won’t have an entry in Perms table. Only those SharePoint objects which have an explicit ACL have an entry in Perms table.
   2. Every entry in Perms table gets a unique guid identified by ScopeId column.
   3. Actual ACL for any SharePoint object (only those objects on which ACL is explicitly defined) is stored in RoleAssignment table. It contains 4 columns where:
      1. SiteId + PrincipalId identify the Site and principal this assignment belongs to. PrincipalId could either be ID in Groups table (if this role assignment is for a group) or tp_ID in UserInfo table (if this role assignment is for a user). ID in Groups table and tp_ID in UserInfo table are non-overlapping (none of them are Identity columns), SharePoint internally uses a common sequence to assign them values.
      2. ScopeId identifies the SharePoint object this RoleAssignment belongs to as described in above points.
      3. RoleId defines the Principal’s role in this ACL. The same is a lookup to RoleId column in Roles table.
      4. Moving on, Roles table define a Principal’s permissions via PermMask column. The same specifies permissions as bitwise combination of values available here:
         https://msdn.microsoft.com/library/Microsoft.SharePoint.SPBasePermissions.aspx
      5. And oh, I also found out Roles table has a PermMaskDeny column for exclusion security. Its supported in schema but no UI or logic is built around it:
         http://blogs.architectingconnectedsystems.com/tag/tools/
   4. As soon as you break security inheritance for any object in SharePoint, it gets a single entry in Perms table and as many entries in RoleAssignment table as the number of Principals in the object’s ACL (remember here Principal is either a user or a group).
   5. As soon as you reset a security ACL of a SharePoint object to inherit security from its parent, the entries in both Perms and RoleAssignment tables 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:

1. When deleting a documents, its security is not deleted if its set to unique permissions:
   https://social.msdn.microsoft.com/Forums/office/en-US/1e854f73-270a-44d1-a6cd-969682435164/roleassignment-table-only-increase-but-do-not-decrease-in-sharepoint-database-schema?forum=sharepointdevelopmentlegacy
   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.
2. Important queries for a SharePoint db. These can help in your research in some way:
   https://social.technet.microsoft.com/wiki/contents/articles/36432.sharepoint-2013-important-sql-queries-on-sharepoint-content-databases-part-i.aspx
   http://bkemmler.blogspot.in/2010/03/list-of-all-usergroup-rights-for.html