Salesforce: Using Permission Set to Query User Permission

Permission Set is a powerful feature in Salesforce. If you are using Enterprise and Unlimited edition (included Developer edition). Using permission set, admin can assign additional permissions to users on top of permission given in Profile assign to that user. Permission Set is set per user basis and is only to ADD more permissions, not to reduce it from Profile. Permission Set also may be given to admin users when the permission cannot be enabled in the standard System Administrator profile.

Permission Sets include settings for:
- Assigned Apps
- Assigned Connected Apps
- Object Settings, which include: objects, fields, and tab availability
- App Permissions
- Apex Class Access
- Visualforce Page Access
- External Data Source Access
- Named Credential Access
- Data Category Visibility
- Custom Permissions
- System Permissions
- Service Providers

In this blog, I am not going to explain how to set up the Permission Set, you can find the overview here.

As of now, we still cannot run a report on the Permission Set and Users assignment to Permission Set. But, since Summer '11 release (API version 22), Salesforce introduces 2 new objects related to this: PermissionSet and PermissionSetAssignment. To make this object more powerful, in Spring '12 release (API version 24), FieldPermissions and ObjectPermissions objects are introduced with ParentId which pointing to PermissionSet.

Once you understand the architecture, you can answer all sorts of questions about your users and permission. Here is the diagram (right-click image to see in full size) :

Here are a few samples using SOQL to query permission set:

List all Permission Set
SELECT Id, Name FROM PermissionSet WHERE IsOwnedByProfile = False ORDER BY Name

List all Permission Set that grants extra Permission, such as create Dashboard

SELECT Id, Label FROM PermissionSet WHERE IsOwnedByProfile = False AND PermissionsCreateCustomizeDashboards = True ORDER BY Name

List all Permission Set does not from Managed Package
SELECT Id, Name FROM PermissionSet WHERE IsOwnedByProfile = False AND NamespacePrefix = '' ORDER BY Name

Show all Users with ViewAllData Permission
SELECT Id, AssigneeId, Assignee.Name, PermissionSet.IsOwnedByProfile
FROM PermissionSetAssignment
WHERE PermissionSet.PermissionsViewAllData = True
ORDER BY PermissionSet.IsOwnedByProfile DESC, Assignee.Name

This query will return all Users with Permission to View All Data, either acquired from Profile or from Permission Set.

Compare to the query below, where it just returns the permission from Profile only.

SELECT Id, Name

FROM User

WHERE ProfileId IN  (SELECT Id

                    FROM Profile

                    WHERE PermissionsViewAllData = true)

ORDER BY Name

Show all Active Users without ViewAllData Permission

SELECT Id, Name  

FROM User 

WHERE Id NOT IN 

SELECT AssigneeId FROM PermissionSetAssignment WHERE PermissionSet.PermissionsViewAllData = True) 

AND IsActive = True

ORDER BY Name 

So instead of pulling all users and all users with specific permission, then do a vlookup() in Excel, we can combine the query using anti-joint query NOT IN.

Id in the main query is the same Id with AssigneeId in sub-query, which is User Id.

Show all Active Users with Transfer Record Permission
SELECT Id, AssigneeId, Assignee.Name, Assignee.Profile.Name, PermissionSet.IsOwnedByProfile
FROM PermissionSetAssignment
WHERE PermissionSet.PermissionsTransferAnyEntity = True AND Assignee.IsActive = True
ORDER BY PermissionSet.IsOwnedByProfile DESC, Assignee.Name

Show all Users by specific Profile and return Permission Set assigned to that user
SELECT p.Id, p.Assignee.Name, p.Assignee.Profile.Name, p.PermissionSet.Label
FROM PermissionSetAssignment p
WHERE p.PermissionSet.IsOwnedByProfile = False AND p.Assignee.Profile.Name = 'Sales Reps'
ORDER BY p.PermissionSet.Label, p.Assignee.Name

This query will return all Users with Permission Set assigned to users with Profile = Sales Reps and the additional Permission Set name assigned.

Show Profile and Permission Set have read access to Account for a User
SELECT Assignee.Name, PermissionSet.isOwnedByProfile, PermissionSet.Profile.Name, PermissionSet.Label
FROM PermissionSetAssignment
WHERE PermissionSetId
IN (SELECT ParentId FROM ObjectPermissions WHERE SObjectType = 'Account' AND PermissionsRead = True)
AND Assignee.Name = 'Johan Yu'
ORDER BY PermissionSet.Profile.Name, PermissionSet.Label

This query will return Permission Set (and Profile if exist) that give the user read access to Account object.

Show Permission a User has for Account and which permissions give that access
SELECT Id, SObjectType, Parent.Label, Parent.IsOwnedByProfile, PermissionsRead, PermissionsCreate, PermissionsEdit, PermissionsDelete, PermissionsViewAllRecords, PermissionsModifyAllRecords
FROM ObjectPermissions
WHERE (ParentId IN (SELECT PermissionSetId FROM PermissionSetAssignment WHERE Assignee.Name = 'Johan Yu'))
AND (SobjectType = 'Account')
ORDER BY Parent.IsOwnedByProfile DESC, Parent.Label

This query will return user permission of an object and also tell all Permission Set (and Profile if exist) that give the user that permissions.

Show Object Settings added to all Permission Set
SELECT SobjectType, Parent.Name, PermissionsRead, PermissionsCreate, PermissionsEdit, PermissionsDelete, PermissionsViewAllRecords, PermissionsModifyAllRecords FROM ObjectPermissions WHERE SObjectType IN ('Account') AND Parent.IsOwnedByProfile = false

This query will return only Permission Set with additional permissions on a specific object.

Get all Non-Chatter users with Lightning Experience permission enabled
SELECT Id, Assignee.Username, Assignee.Profile.Name FROM PermissionSetAssignment WHERE Permissionset.PermissionsLightningExperienceUser = True AND Assignee.Isactive = True AND (NOT Assignee.Profile.Name LIKE '%chatter%') ORDER BY Assignee.Username


Mass Assign Permission Sets to Users
Based on PermissionSetAssignment attributes, we can use Data Loader to mass assign (and mass delete) users with a specific Permission Set. All you need to provide just: AssigneeId (which is User Id) and PermissionSetId. But, you cannot update the record in PermissionSetAssignment.



Reference:

• FieldPermissions
• ObjectPermissions
• PermissionSet
• PermissionSetAssignment
• SOQL (Salesforce Object Query Language) Reference
• Using SOQL to Determine Your Force.com User’s Permissions
• Comparing Profiles and Permission Sets
• Permission Sets Best Practice: Mass Assign Permission Sets and other cool things using the API

Last update: 8 Jan 2021