Using Permission Set to Query User Permission

Permission Set is 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 given to admin users when the permission cannot be enabled in 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 setup Permission Set, you can find the overview here.

As of now, we still cannot run report on Permission Set and Users assignment to Permission Set. But, since Summer '11 release (API version 22), Salesforce introduce 2 new objects related to this: PermissionSet andPermissionSetAssignment. To make this object more powerful, in Spring '12 release (API version 24),FieldPermissions and ObjectPermissions object 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 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 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 query below, where it just return 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 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 all User have read access to Account and which permission give the access:
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 users 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.

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