Windows Sharepoint Services incidentally inside permissions on Lists table with tp_ACL
WSS offers a nice Object Model to navigate its content. Anyway sometime we need something more. In these situations we can think about accessing directly the WSS Content Database as James Sturms showed us in his session (SharePoint Database Schemas).
It's dangerous, and I agree with his conclusion, when he states:
- Direct database access
- Lots of power available
- Lots of danger to SharePoint
- Lots of danger to your solution
- Upgrades will break
- SharePoint upgrades will break your application
- Your changes may break SharePoint’s upgrade
- Test the impact of your changes at scale
- Multiple user performance (how fast)
- Scalability (how many)
I suggest you to carefully think about directly accessing WSS Content DB, but sometimes you need it. For instance this week I had to. I'm not so happy about that, I'd prefer not to do so, but I needed to.
By the way I had the opportunity to discover the inner workings of Lists permissions inside content DB. I'm speaking about the tp_ACL column of each List record. I'd like to write some notes, just in case someone else will need them in the future.
The tp_ACL column it's an image data type column and containes a binary set of data, representing the list of groups/users and their permission masks.
Here is what I discovered:
- The field is divided in groups of 4 bytes, multiple of 2
- The first 3 groups (i.e. 3 x 4 = 12 bytes) are headers. In particular the third one stores the number of different permissions stored inside the tp_ACL
- Starting from the 4th group of 4 bytes, included, you can find sets of 8 bytes where:
- The first group of 4 bytes represents the ID of a User (tp_ID field of a User from a UserInfo record) or of a Group (ID field of a Group from a WebGroups record) in hex format, with less significant byte on the left.
- The second group of 4 bytes represents the permission mask.
- At the end of these groups, to fill to multiples of 2 groups of 4 bytes, there could be a blank filler group (0x00000000).
For instance if you have a group named "Customers" with ID 1073741832 in WebGroups table, it's value in tp_ACL will be its hex (40000008) reverted: 0x08000040.
If you have a user name "Mario Rossi" (a tipical Italian user :-) !) with tp_ID 15 in UserInfo table, it's value in tp_ACL will be its hex (0000000F) reverted: 0x0F000000.
After the User/Group identifier you have the permission mask. When you work with document libraries (that was my situation). The interesting part (I guess) is represented by the first 2 bytes of the mask.
First Byte - Single Item Permissions
| ViewListItems | 1 | 0001 |
| AddListItems | 2 | 0010 |
| EditListItems | 4 | 0100 |
| DeleteListItems | 8 | 1000 |
Second Byte - List Permissions
| CancelCeckout | 1 | 0001 |
| ManagePersonalViews | 2 | 0010 |
| ManageListPermissions | 4 | 0100 |
| ManageLists | 8 | 1000 |
So if Mario Rossi can access an "Invoices" document library with the following permissions:
- ViewListItems
- AddListItems
- EditListItems
- DeleteListItems
- CancelCeckout
His permission mask about the list and its items will be 0x0F01 (0001 | 0010 | 0100 | 1000 + 0001) and his 8 bytes group will be: 0x0F0000000F010108.
I'm still missing the meaning of 0x0108 as the last 2 bytes in the mask (they're equal in every document library I've seen, based on 14K sample lists!), but seems not to be necessary for my case, so at least I decided to skip them. Does anyone know what they mean?
I'd like to know, from someone of WSS team (is there anybody out there?), why they decided to use such a way of storing permissions, rather than a more normalized DB schema, with a Permissions table and a Join table between Lists and Users/Groups. There should be a reason, of course, but I can't see it. I'm really very interested on these kind of choices in large software projects.
It took me about two days of studying to understand all the thinks I needed, included tp_ACL format and many other things, so I'd like to make life simplier to someone else out there. :-).
Anyway I repeat: don't try to directly access WSS DB if you don't really need it!!