Figuring out shares in the PrincipalObjectAccess POA table in CRM

Not a long article, this one is to support my recent presentation at the UK CRM User Group about security design and performance. I mentioned that when you are trying to figure out  what is going on in the PrincipalObjectAccess (aka “POA”) table, it can be useful to separate out things like Users and Teams, and furthermore to separate Owner Teams from Access Teams.

I also find it helpful to join to the right tables to get friendly names rather than go blind on GUIDs. Note: in the example code below I have pulled in first names for users to keep to shorter columns for demo purposes, in larger Orgs you might need full names, but it depends on what you are trying to troubleshoot.

I am excluding some object types from the results, because every user has their own User record and User Settings record shared with them. These are inevitable and not useful to most troubleshooting scenarios.

Of course, for some scenarios you will be trying to figure out why some users have access to records of a specific entity, in which case you might want to replace “POA.[ObjectTypeCode] NOT IN (8,150)” with an IN or an equals.

SELECT TOP 1000
CASE WHEN POA.[PrincipalTypeCode] = 8 THEN ‘User’
WHEN POA.[PrincipalTypeCode] = 9 AND TEAM.[TeamType] = 0 THEN ‘Owner Team’
WHEN POA.[PrincipalTypeCode] = 9 AND TEAM.[TeamType] = 1 THEN ‘Access Team’
ELSE ‘Other’ END AS ‘PrincipalType’,

COALESCE(USERID.[FirstName],TEAM.[Name]) AS PrincipalName,
POA.[ObjectTypeCode],ENTITY.[OriginalLocalizedName], POA.[ObjectId],
POA.[AccessRightsMask],POA.[InheritedAccessRightsMask],POA.[ChangedOn],
POA.[PrincipalTypeCode],POA.[PrincipalId]
FROM [MyOrg_MSCRM].[dbo].[PrincipalObjectAccess] AS POA

LEFT OUTER JOIN [MyOrg_MSCRM].[dbo].[SystemUserBase] AS USERID
ON POA.[PrincipalId] = USERID.[SystemUserId]
LEFT OUTER JOIN [MyOrg_MSCRM].[dbo].[TeamBase] AS TEAM
ON POA.[PrincipalId] = TEAM.[TeamId]
LEFT OUTER JOIN [MyOrg_MSCRM].[MetadataSchema].[Entity] AS ENTITY
ON POA.[ObjectTypeCode] = ENTITY.[ObjectTypeCode]

WHERE POA.[PrincipalTypeCode] IN (8,9) AND POA.[ObjectTypeCode] NOT IN (8,150)
/*User and Team (8,9) shares, not including shares to User or User Settings (OTC 8,150)*/

I am sure there are SQL gurus out there who could improve on this code. I also leave it as an exercise for the reader to add a join to the SystemUserPrincipal table to reverse engineer which users belong to which teams, so you can end up with a list of which users have access as themself or as a team member, in a single query.

Decoding the bit masks

In some cases, just being able to find rows in the POA that prove why a user has some kind of shared access to a record is enough. Knowing whether it is explicit or inherited, seeing if it is the user or an owner team or access team is usually enough to point you in the right direction to check or change your configuration.

If you do need to figure out exactly what rights have been shared, you need to decode the bit mask, and the table below is a quick reference to what the bits mean. You might also like to use Scott Sewell’s "POA Decoder Ring" spreadsheet if you don’t speak binary like a native: Unmasking CRM’s PrincipalObjectAccess table.

Bit value Meaning
1 Read
2 Write
4 Append
16 Append To (with shares, this always goes with Append, so treat them together as 20
32 Create. Of course this makes no sense – you can’t have permission to create a record that is already there, so you should not see this bit in use “in the wild”
65,536 Delete
262,144 Share
524,288 Assign
134,217,728 “This access is inherited” – added to the value of the share on the parent record and used for the child records when Share cascading behaviour is turned on.

One value you will see quite a lot in the InheritedRightsMask column is 135,069,719. This is basically all of the bits in the table above added together, except 32 (Create). You will see this for child records when Reparent cascading behaviour is turned on, with the owner of the parent record as the principal. It effectively allows the owner of the parent record to do anything they like to the child record, albeit always filtered by their security privileges (so if they do not have the rights to delete any records of this entity, they do not get to do that to this record either).

This same number will also show up if someone explicitly shares a record and ticks every box, then this share is cascaded down to child records. In real-world use I find that does not show up very often. Users rarely share records and give people access to do anything, it is usually more specific, eg to grant write and assign rights. Note: if a user does not have a privilege for a record, they cannot share that privilege to someone else, the check box would be disabled in the Share dialogue. So if Alice opens a record that she can read, but not delete, and shares that record with Bob, Alice will not be able to select the checkbox to share delete rights with Bob (which is good because it means it is obvious to the user that they cannot do this).

Special privileges in CRM Security Roles

There are several privileges in Dynamics CRM that control access to things like settings and user personalisation features, rather than data records. If users are missing some of these then they might not be able to sign in to CRM at all, or might not be able to use it properly. In particular, there are six privileges that can only be set at User level

Privileges that can be set at “User” Level only

There are a few privileges that you can only set to User level or None in any security role. Five of the entities for which this is true are on the Core Records tab, and you can easily find out which they are by looking at the System Administrator security role (shown below). Even this “super user” does not have global rights to these items so they stand out as the only rows not covered in green circles:

System Administrator Security Role

(Minor note: the above screenshot was taken from CRM 2015. In CRM 2013 and earlier, you will see that UserEntityInstanceData is written as one word with no spaces, and appears below User Entity UI Settings.)
Find out what these 5 settings are for, and how to configure them »

Why Use Access Teams in Dynamics CRM 2013

It seems to be “Access Teams Week”.

Larry Lentz wrote an article CRM 2013 Access Teams in a Nutshell which is a great introduction to what Access Teams are and what they do. Ben Hosking wrote a post in his series about preparing for the CRM Customization exam MB2-703: Access Teams and Access Team Templates how to use them and key facts.

Both of these are really good primers on the mechanics of using Access Teams but I think they miss addressing one really big question – why would you want to use Access Teams in the first place?

I think there are several reasons to use Access Team Templates, some in terms of usability, others from a more technical or performance perspective, and some cases for using manually-created Access Teams.

Read 4 key reasons to consider using Access Teams in Dynamics CRM 2013 »

Security Roles and Teams in CRM – An Inconvenient Half-Truth

Over the course of the last two years or so reading everything I can about Dynamics CRM, as well as teaching many classes of people how to get the most out of their CRM systems, one thing which comes up again and again is how to best structure Business Units, Users and Security Roles, and sometimes Teams as well to get the exact model you want to match your business requirements for who has access to which records and when.

Users inherit Security Roles from Teams – right?

One concept I have seen repeated many times is that “Users inherit security roles from all the Teams they are in”. And generally this seems to be a reasonable way to describe how it works, but occasionally odd behaviours seem to show up which make this appear to be less than 100% accurate.

I also had a gut feeling for a while that this was not the best way to describe the way this works. I prefer to say that “when a User is in a Team, they can act as if they are the Team, with the rights that the Team has through its Security Roles, but only while considering records in the same Business Unit as that Team”.

More on this later, and the one part of the model that this description does not do justice to.

Overall this means Security Roles use a kind of “impersonation” when Teams are involved and that the rights the User has are not only ‘borrowed’ very temporarily from the Team but they are relative to where the Team is – so access levels / depths such as “Business Unit” or “Parent / Child Business Unit” operate from the Business Unit where the Team is.

So how does this really work?

If you really want to read how security roles work in terms of determining access to a whole bunch of records (to display the results of a view) or a single record, then you need to read the white paper Scalable Security Modelling with Microsoft Dynamics CRM 2011.

42 pages later you will probably know exactly how the queries are built to actually enforce the security model, but that may not have made it much clearer from a practical, day-to-day design point of view. To be fair, the point of that white paper is to explain the underlying architecture and query methods properly so you can figure out the performance impact of different security approaches, rather than demonstrating how this informs your design from an end-result “who can see what” point of view. One thing that is never mentioned is any idea of inheritance or merging of privileges from Teams to Users. Every kind of access request is checked against User and Team permissions separately (exactly what is checked depends on things like whether the User has Global access level privileges to that entity at all, and whether the record is owned by the User or any of their Teams. These can help shortcut the otherwise brute force querying that would be necessary, especially to return all records in a view).

“You can’t handle the TRUTH!”

By now, I bet some of you are ready to shout at the screen – “we know Users don’t actually inherit the roles and keep them for themselves, but it works just as if they did, so it’s just a kind of shorthand and we all understand what we really mean, so don’t be pedantic”.

Tom Cruise in A Few Good Men - I Want the TRUTH!I always argue that I am not pedantic, I just like things to be exactly correct – “I want the TRUTH!”

In this case, it is CRM which is pedantic, and does not always behave as expected if you believe that a User can act as if they have all the Roles that their Teams have, all of the time. If you are betting your security model on it working this way then either you will end up with Users who can’t do their job, or possibly a gaping hole in your security. Neither sounds good to me.

Read more of this post

Oops – Microsoft Certificate expired when logging on with Live ID

When signing in to a Microsoft site this evening I received a security warning from Firefox. Strange, I was convinced the site was genuine and I had not followed a spoofed phishing link to get there. How could this be?

I chose to continue using the “Add exception” button to get to the screen where I could see the certificate details. Nothing wrong with the certificate issue, path and so on, except that it expired a few hours ago at 18:26 GMT:

Expired Microsoft Certificate

This certificate is not actually for live.com that runs the logon part of the process, but profile.microsoft.com which looks after the other parts of the page which wrap round this. So, not vital but likely to cause much confusion and FUD until they get a new certificate to fix the problem.

Do you know when your certificates expire? And all your different domain names? What about other vital contracts which would stop you doing business if they expired suddenly? How do you manage all of these; is it a central business policy or does it just come down to one overworked IT Manager’s Outlook calendar?

Microsoft Browser Choice screen rant

I know this is old news, but it still annoys me. Just for those who have not heard, this useful summary of the legal background to Browser Choice (rather than the technical details) describes the decision:

In December, the European Commission and Microsoft arrived at a resolution of a number of long-standing competition law issues. Microsoft made a legally binding commitment that PC manufacturers and users will continue to be able to install any browser on Windows, to make any browser the default browser, and to turn access to Internet Explorer on or off. In addition, Microsoft agreed to use Windows Update to provide a browser choice screen to Windows users in Europe who are running Internet Explorer as their default browser.

So, when I install shiny new Windows 7 machines for my clients with a perfectly serviceable browser (IE8) with some great security features such as protected mode, I make sure the Windows Update has brought everything up to date and BAM! An icon appears on their desktop and prompts them to choose what browser they want.

So I choose IE, delete the icon and everyone is happy.

This is a complete waste of everyone’s time and money. The users who want an alternative still go and download the browser of their choice. Most don’t bother. Making a bad choice from the popup screen and deciding a while later you want to switch, or revert to IE is just a waste of people’s time, and in business this time will cost money. Across Europe this hidden cost will be huge.

Read more of my rant about the Browser Choice screen»

UPS_Invoice email trojan variant claims to be from Customs Service

In the last hour I found in my inbox a variation on the UPS_Invoice trojans of last week. This new email claimed to be from “Customs Service” with the subject “Customs – We have received a parcel for you” and the following text:

Good afternoon,

We have received a parcel for you, sent from France on July 9. Please fill out the customs declaration attached to this message and send it to us by mail or fax. The address and the fax number are at the bottom of the declaration form.

Kind regards,

Rolland Hanna

Your Customs Service

This content was so close to the UPS_Invoice one that it seems obvious it originates from the same source.

Read more of this post

Follow up post about UPS_Invoice trojan

I’ve now had a chance to take a slightly closer look at the four copies of this Trojan Agent HFU that I received in the last 24 hours, as discussed in my previous post here. I’ve posted some details of file names and sizes along with MD5 hashes for people to be able to compare their versions against.

Read more of this post

UPS_Invoice.exe trojan received by email

This lunchtime I received an email as follows:

From: United Parcel Service [someone@not_ups.com]

Subject: UPS Paket N2410170593

Unfortunately we were not able to deliver postal package you sent on July the 1st in time because the recipient’s address is not correct.

Please print out the invoice copy attached and collect the package at our office

Your UPS

Attachment: UPS_Invoice_317.zip

Of course this was extremely suspicious. I had no recent dealings with UPS, the email clearly did not really come from them anyway (it was not even spoofed to appear to be from their domain), and why on earth would they need to send me a file, let alone a zipped one? The misspelling in the subject also smelled of an automated message (although Paket is the correct spelling for the German word for packet). I smelled malware and wanted to find out more.

Read more of this post

Why IT design skills are important, and how to measure them

The comments on my earlier post about the MS Security Design exam 70-298 prompted me to add some more general thoughts.

I agree with the comment made that the design exams do generally seem easier in some respects than the straight technical ones, as you don’t need to know the same level of detail of exactly how to do something in terms of making choices in a dialogue box.

On the other hand, the MS design exams do expect you to be able to take in, digest and interpret a load of business and technical requirements (some of the latter may only be implied from the former, some will be explicitly stated). The breadth of this is where the challenge lies in the real world, although the exam will often lead you in the right direction, rather than a blank sheet of paper on which to write an IT security plan. The nature of a computer-based exam does not lend itself to open questions; it would be very hard to make any kind of meaningful sense out of your answer to “How would you improve the security of the data for this organisation? (answer in no more than 200 words)”.

Read more of this post

Passed 70-298 "Designing Security for a Windows 2003 Network"

This morning I took and passed Microsoft exam 70-298 “Designing Security for a Windows 2003 Network”. Having not taken one of these scenario-style design exams before, I was a little cautious even though I was fairly confident of my knowledge of the material.

The first section had 11 questions which was great as I had made loads of notes from the provided fictional case studies, and I sailed through with loads of time to spare. Unfortunately the format of these exams is that the time for each part is independent, so you don’t get to carry any spare time to the next set of questions and use it there. I had a couple of shorter sections where I maybe spent too long reading the materials and answered the last question with seconds to spare.

Overall I found this style of exam to be right up my street; taking in lots of information in a very short time and then applying my technical knowledge to this to come up with solutions to the business issues. Despite the rushed time on a couple of questions I came away with my best score to date on a Microsoft MCP exam, and won’t need to use my second chance to take this.

How do you find these design exams compare to the ‘normal’ technical ones?

Windows Server 2008 Security Resource Kit coming very soon

book cover - Windows Server 2008 Security Resource KitJesper Johansson has put together a great book for Windows Server 2008 focusing on security and providing a load of resources that go beyond the shipped product.

Produced by a group of world-class contributors including several MVPs and members of Microsoft’s server security team, this is likely to be the definitive reference on the subject for some time.

According to Jesper’s blog it has now gone to press.

This official Microsoft Resource Kit delivers the in-depth, technical information and tools you need to help protect your Windows®–based clients, server roles, networks, and Internet services.

Leading security experts explain how to plan and implement comprehensive security with special emphasis on new Windows security tools, security objects, security services, user authentication and access control, network security, application security, Windows Firewall, Active Directory® security, group policy, auditing, and patch management. The kit also provides best practices based on real-world implementations.

You also get must-have tools, scripts, templates, and other key job aids, including an eBook of the entire Resource Kit on CD.

It’s an MS Press title so it should be pretty widely available, I will be pre-ordering my copy from here at The Register book store, as they have really competitive pricing and free delivery for orders over £25 at the moment.

Hardening Windows Systems – Roberta Bragg

Author: Roberta Bragg. CISSP, MCSE: Security, Security+Publisher: McGraw Hill / Osborne

Suggested Publisher Price: $39.99 US / $57.95 CDN / £24.99 UK

ISBN: 0-07-225354-1 Softcover, 504 pages

Hardening Windows Systems book cover

Bulletproof your systems before you are hacked!

Take a proactive approach to network security by hardening your Windows systems against attacks before they occur. Written by security evangelist Roberta Bragg, this hands-on resource provides concrete steps you can take immediately as well as ongoing actions to ensure long-term security. Whether you have one Windows server or one hundred, you’ll get complete details on how to systematically harden your network from the ground up, as well as strategies for getting company-wide support for your security plan. With coverage of Windows 95/98/NT 4.0/2000/XP and Windows Server 2003, this book is an essential security tool for on-the-job IT professionals.

Read more of this post

Watch those data entries

Thought I would share a cartoon I saw:

From http://imgs.xkcd.com/comics/exploits_of_a_mom.png

Using anti-virus software to keep the elephants away

Steve Riley wrote an interesting article recently about why he chooses the trade-off to not run anti-virus (AV) on his own machines, and a follow-up to that after many people asked if this is his general recommendation. His view is very similar to mine, in that if your overall stance is a cautious one and you are taking other suitable precautions against the risk of getting a virus infection (or spyware or some other nasty malware) then you may be just fine running with no AV software. This is how I run my own workstations (both private and business), but in all cases I run as a non-privileged user and will always be aware of the risks anytime I use admin credentials to install something.

As Aaron Margosis points out, running anti-virus software which requires you to be a local administrator to work properly is fairly pointless. You have the rights required to turn off, disable and uninstall your AV, so any malware that gets past your defences can do this too, rendering the AV potentially useless. The same applies of course to running well-written anti-virus which does not require admin rights, but then running as admin anyway.

»Read on to find out why I recommend using anti-virus to keep elephants away»

Use Bitlocker drive encryption for all your data volumes on Vista

Thanks to a comment by Steve Lamb on his blog, I now find out that you can already use Bitlocker to encrypt volumes other than the operating system partition, you just have to do it from the command line.

I was pleasantly surprised to learn this, and it means I don’t have to wait for sp1. »Read the rest of the article to find out how»

Industry Insiders article – Don’t Secure Your Documents!

An article I wrote for Microsoft’s Industry Insiders blog site has just been published.

This week I was asked by the IT support guy who works for one of my clients about how a user could put a password on a document. Since I am both their external consultant and their MS Office trainer, I was the right person to call.

To me this question is always a red flag as it implies that the user does not understand the places which already exist for them to save documents in such a way as to give access to the correct group of colleagues (or just themselves). My answer was therefore “I’ll show you how to do it for the sake of argument, but you should tell the user that they should not do this”.

Read the whole of this article about a proper approach to document security and avoiding mere security theatre.

The Industry Insiders site looks at various topics affecting corporate IT, with a slight lean towards information security, which is unsurprising since it is maintained by IT Pro Evangelist for Security, Steve Lamb (and evangelist manager Eileen Brown)

Whitelisting applications versus Anti-virus

There was an interesting article in The Register yesterday called “the decline of antivirus and the rise of whitelisting“. It discussed the relative merits of using a whitelist to allow only known good programs to run, versus using traditional anti-virus (AV) to let everything run except things you know are bad. The comments to this article also raised a number of valid points, some academic and some based on real-world experience.

The obvious flaw in the traditional AV approach is the difficulty in keeping up with new malicious software rapidly enough to avoid infection. Whitelisting gives you a little more control but still takes substantial effort in a large environment, and is harder to delegate out to a third party without leaving so many loopholes as to render it pointless.

Read more of this post

Users sharing passwords may breach data protection regulations

The Data Protection Act 1998 (DPA) can be seen as a very straightforward piece of legislation. Properly applied, it protects the rights of individuals to ensure that data about them is processed properly, securely and only for the purposes they originally gave that information.

In a ruling yesterday the Information Commissioner’s Office decided that allowing staff to access data without proper controls (by using each other’s passwords) is not in compliance with the Act. This kind of lax IT management does not ensure that personal information will only be accessed by authorised people who have a good reason to do so. This does not meet the Act’s requirements that a Data Controller should have appropriate “technical and operational measures” to ensure data is processed in line with the Data Protection Principles.

Read more of this post

Windows Vista more secure after six months than XP

Some readers may have seen the report which was published by Jeff Jones three months after Vista was finally released in which he showed that the number and severity of flaws in Vista were far less of a risk than XP after an equivalent period.

He has now updated this report to show the vulnerabilities in Vista after 180 days. What is key is not only the distinctly fewer known vulnerabilities overall, but the number of disclosed holes that remain unpatched at the time of writing.

Note that the blog entry is only a summary and the only graph you get to see relates to high severity vulnerabilities. Also, it only looks at those which affect the core systems, not optional components. So, Vista looks like it is doing better than XP at this point with almost no unpatched holes, and many people will go away with that impression because visuals work well in getting messages into the brain.

The full 14 page report (pdf) is also available, in which the discussion is much more detailed (even patch by patch). It is here that it becomes clearer that while it is faring better than XP did, to me it is not doing so much better given how much hype there has been about trustworthy computing and Vista (and Longhorn / 2008) being secure by design, rewritten from the ground up to be more secure, yadayada more secure.