Who has what permissions?! 3

Do you ever find it hard to get a list of who has what permissions inside of Salesforce?  Don’t worry, I’ve been there.   There are several objects on the backend that can help you determine this information and, with a little excel magic, you’ll have great documentation or a means to interrogate, “Is this really a permission someone should have?”

TL;DR;

The important objects are:

1. PermissionSet
2. ObjectPermissions
3. FieldPermissions
4. SetupEntityAccess

I will be going over primarily permission sets/profiles and Object Permissions.  The concept utilized below applies to the other objects.

PermissionSet

This is the bread and butter of reporting on Salesforce permissions.  The most important attribute that I think that I include in my SOQL queries is “IsOwnedByProfile”.  True means that it is a profile, whereas false means it is a permissionset.  For example:

SELECT Id, Name
FROM PermissionSet
WHERE IsOwnedByProfile = false

This will return a list of all permissionSets in the organization.

If you wanted to return profiles, you would query something like this:

SELECT Id, Profile.Name
FROM PermissionSet
WHERE IsOwnedByProfile = true

An overview of this wonderful object is available here.  A few good things that you can do is query who has elevated permissions such as:

1. View All Data
2. Modify All Data
3. Customize Application
4. Manage Internal Users

Object Permissions

Now, the most common thing that people wonder is who has CRUD access on any given set of objects.  Here’s the query that I use to pull this information:

SELECT ParentId, Parent.Name, Parent.Profile.Name, PermissionsRead, PermissionsCreate, PermissionsEdit, PermissionsDelete, PermissionsViewAllRecords, PermissionsModifyAllRecords, SobjectType
FROM ObjectPermissions

This results in an output like this:

You will want to use this query in dataloader as tools such as workbench receive a foreign key error when attempting to export as a CSV file.  Once in excel, replace all of the “true” values with 1 and the “false” values with 0.  You can hit control + f on your keyboard to replace all.   Your spreadsheet should look like this.

Then create a pivot table of the values, with the permission set or profile as the column and the row label as the sObjectType. Right click on the pivot table and remove the grand total columns.  In excel 2013, this is under Pivot Table Options > Totals & Filters > Show grant totals for (rows/columns).  In the Pivot Table options, you will also want to remove the checkbox that says “For empty values show” on the “Layout & Format” tab.  Click okay.  Now, highlight all of the rows and columns.  Right click and choose “format cells.”  Choose “Custom” and input [=0]”False”;[=1]”True”.  Your spreadsheet should now look like this:

Be sure the freeze the first column by going to View > Freeze Panes > Freeze First Column.  This makes it easier to scroll through the spreadsheet and see which object is being referenced.  This is a good view for seeting what permissions a profile or permission set has at a glance.  If you want to compare permissions, I would suggest changing the Profile/permissionset name to be the row and the column to be the object.  You can then use simple formulas to determine if the permissionsets match.

Before:

You’ll notice that if the value matches across all four permission sets that we are comparing, we can say that it is the same.  If it is less than four, it should be false. This can be expressed as [=4]”True”;[<4]”False”.  Conditional highlighting can be added to call attention to differences. The final result looks like this:

That’s it!  You now can tell differences at a glance using excel skills and basic SOQL!