Salesforce: Using Permission Set to Query User Permission

Permission Set is a powerful feature in Salesforce. If you are using Enterprise and Unlimited editions (including Developer edition). Using the permission set, the admin can assign additional permissions to users on top of the permission given in the Profile assigned 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 the Summer '11 release (API version 22), Salesforce has introduced 2 new objects related to this: PermissionSet and PermissionSetAssignment. To make this object more powerful, in the Spring '12 release (API version 24), FieldPermissions and ObjectPermissions objects are introduced with ParentId, which points 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 the image to see it 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 grant extra Permission, such as creating 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.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 the Profile or from the Permission Set.

Compare this 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 doing a vlookup() in Excel, we can combine the query using anti-joint query NOT IN.

Id in the main query is the same ID as AssigneeId in the 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 the 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 exists) that gives the user read access to the 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 for an object and also tell all Permission Sets (and profiles if they exist) that give the user that permission.

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