Want to make a SOQL to return all Users and there (custom) permissions
I've just started using SF, so bare with me if this is very basic.
One of the usages of salesforce we're trying to do is to manage the permissions and policies of what some departments can do in our internal BO (backoffice).
So I manage to find that we can do that by making the
CustomPermissions, and it is going forward kindly of ok. But now from our BO we need to query SF for users and their permissions.
So, for those who know SQL, this was kind of what I wanted to do:
SELECT User.Id, User.Email, User.Username, PermissionSet.Id, CustomPermission.* FROM User JOIN PermissionSetAssignment ON PermissionSetAssignment.AssigneeId = User.Id JOIN PermissionSet ON PermissionSet.Id = PermissionSetAssignment.PermissionSetId JOIN CustomPermission ON CustomPermission ON CustomPermission.??? = PermissionSet.Id
But on SF they use SOQL, which already understood that it takes an object way of querying.
Already did a couple of tests, and Docs#StandardObjets helped to understand the properties of each object, but failed of explaining how do the objects interact and which alias they have when interacting. I mean from the POV of
PermissionSetAssignments, users are called
Where can I find these alias?
Where can I find the object relations map?
But going back to the topic, I've manage to go till here:
SELECT Id, Email, (SELECT PermissionSet.Id PermissionSet.Label FROM PermissionSetAssignments) FROM User
Which I already have Users, PermissionSetAssignments and PermissionSet, I was able to find this by some examples and the PermissionSet diagram (end of the page)
But I'm completely lost in going from there to CustomPermissions. Any idea?
With the help of @adrian-larson so far I've manage to get the users, their permission sets, and the SetupEntityAccessItems
ParentId, however I still miss the
DeveloperName from CustomPermissions that would need to be attached to SetupEntityAccess.
The @adrian-larson answer would be correct if I wanted to do a query (in that case 3) per each user.
The idea is to map the CustomPermissions DeveloperName into our application specific policy, so I intend to from time to time to fetch that Users > PermissionSet > CustomPermissions and update all users permissions on our application.
what I have so far:
get all users and their permission sets
SELECT Id, Email, ( SELECT PermissionSet.Id, PermissionSet.Label FROM PermissionSetAssignments ) FROM User
get all setup entity access items that are custom and the parent id (permission set)
SELECT Label, Name, ( SELECT Parent.Name, SetupEntityId, SetupEntityType FROM SetupEntityAccessItems WHERE SetupEntityType = 'CustomPermission' ) FROM PermissionSet
[STILL MISSING] a way to map setup entity access to the custom permission name
Solution found. On the CustomPermission docs doesn't mention an ID, but noticed that @adrian-larson made a match through the ID, so with the simple:
SELECT Id, DeveloperName FROM CustomPermission
It was possible to get the missing data part. Ideally 1 query would be the best, but this will work also. Thanks @adrian-larson