Getting all Users and their Custom Permissions

Question

tl;dr

Want to make a SOQL to return all Users and there (custom) permissions

---

Hi there

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 PermissionSet with 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 Assignee. 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?

---

edit

With the help of @adrian-larson so far I've manage to get the users, their permission sets, and the SetupEntityAccessItems SetupEntityId and 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

---

edit

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

Answer 1

You can get Object, Field, and SetupEntity permissions with the query outlined below. It gets you a lot of information...

static List<PermissionSet> getPermissions(Id userId)
{
    return [
        SELECT Name,
            (
                SELECT
                    SObjectType,
                    Field,
                    PermissionsRead,
                    PermissionsEdit
                FROM FieldPerms
            ),
            (
                SELECT
                    SObjectType,
                    PermissionsRead,
                    PermissionsCreate,
                    PermissionsEdit,
                    PermissionsDelete,
                    PermissionsViewAllRecords,
                    PermissionsModifyAllRecords
                FROM ObjectPerms
            ),
            (
                SELECT SetupEntityId, SetupEntityType
                FROM SetupEntityAccessItems
            )
        FROM PermissionSet WHERE Id IN (
            SELECT PermissionSetId
            FROM PermissionSetAssignment
            WHERE AssigneeId = :userId
        )
    ];
}

If you just care about CustomPermission, you could do something like:

static List<CustomPermission> getCustomPermissions(Id userId)
{
    Set<Id> assigned = new Map<Id, PermissionSet>([
        SELECT Id FROM PermissionSet
        WHERE Id IN (
            SELECT PermissionSetId
            FROM PermissionSetAssignment
            WHERE AssigneeId = :userId
        )
    ]).keySet();
    return [
        SELECT DeveloperName FROM CustomPermission
        WHERE Id IN (
            SELECT SetupEntityId
            FROM SetupEntityAccess
            WHERE SetupEntityType = 'CustomPermission'
            AND ParentId IN :assigned
        )
    ];
}

Or for all users:

static List<CustomPermission> getCustomPermissions()
{
    return [
        SELECT DeveloperName FROM CustomPermission
        WHERE Id IN (
            SELECT SetupEntityId
            FROM SetupEntityAccess
            WHERE SetupEntityType = 'CustomPermission'
        )
    ];
}

Answer 2

I managed to do it using 1 API request:

/services/data/v46.0/query/?q=SELECT Id, ParentId, SetupEntityId FROM SetupEntityAccess WHERE SetupEntityId IN (SELECT Id FROM CustomPermission WHERE NamespacePrefix = '{{NAMESPACE_PREFIX}}') AND ParentId IN (SELECT PermissionSetId FROM PermissionSetAssignment WHERE AssigneeId = '{{USER_ID}}')