Paolo Pialorsi - Bridge The Gap!

Living in a Service Oriented World

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!!

Posted: Aug 25 2005, 01:23 AM by paolopia | with 4 comment(s)
Filed under:

Comments

Marco Bellinaso said:

Thanks for the info Paolo, nice and interesting discovery! :-)
We did something similar in our own portal solution, with regards to ACL storage, for performance and security reasons.

BTW, accessing SharePoint's DB is a bad thing in general, accessing the security info stored in those DB is even worse :-) I understand that there are situations where it might be necessary, but I'd try to find some alternative anyway, even a less elegant/simple/performant way...that's because if you tell your client you're using some undocumented and absolutely unsupported (by MS) hack, they are not going to like it at all, and may even force you to change the implementation. It depends on the specific project and client of course. For the finance project we've worked on in the past months, that surely coulnd't get accepted :-(
I'm sure you know this kind of situations :-

Ciao
# August 25, 2005 6:01 AM

Paolo Pialorsi said:

Marco,
you're absolutely right, as you can see also from my last red caveat! :-)
Anyway in my situation I've got the customer explicit request to make it run "faster" even if in the future may be we'll have to rewrite/review that little piece of implementation.
Consider that I'm using the ACL field just to read and not to write any kind of information.
Thanks for your comment and see you at WPC :-).

Paolo
# August 25, 2005 6:24 AM

Syed Ali Abbas said:

Dear Paolo,

Thanks for bridging gap between newer and experienced peoples...definitely you are doing great job!!!.

I want to know some thing about SharePoint customization, that if i customize SharePoint List web part and create my own web part connected with external database.  In this case, i think we compromise some basic features of SharePoint like Searching and access control over content saved in external database. i need your opinion as well as others who want to share their experience with me.

Thanks & Regards,

Ali Abbas

# May 20, 2008 7:45 AM

Queen said:

Good blog post. I definitely love this site. Keep it up!

# April 15, 2013 4:36 PM