Access SOQL results by grouped field

Question

I'm attempting to aggregate all ObjectPermissions at the user level for a user access review (audit) application. This application's central function is to show an end-user (a reviewer) their reviewee's access to in-scope objects. It should account for both Profile and PermissionSet driven permissions.

To accomplish the actual aggregation of ObjectPermissions I'm doing the following:

1. Get all PermissionSetAssignments.
2. Get all Object Permissions.
3. Since both have a PermissionSetId, use PermissionSetAssignment.PermissionSetId to find the ObjectPermissions associated with each assignment.
4. Do this for each user, for each of their PermissionSetAssignments.
5. The end-result should be a comprehensive picture of their Object-level access.

I'm hitting CPU timeout limit on step 3 - probably because I'm looping through all of my ObjectPermissions for every single one of my PermissionSetIds.

// ObjectPermissions query

List<ObjectPermissions> allObjectPermissions = [SELECT Id, ParentId, PermissionsRead, PermissionsDelete, PermissionsEdit, PermissionsViewAllRecords, PermissionsModifyAllRecords, SObjectType
                                                    FROM ObjectPermissions
                                                    WHERE (ParentId IN (SELECT PermissionSetId
                                                                        FROM PermissionSetAssignment
                                                                        WHERE Assignee.Id IN: revieweeIds))
                                                    AND
                                                    PermissionsRead = true
                                                    AND
                                                   SobjectType IN
                                                   (: Constants.OPPORTUNITY_OBJ,  
                                                    : Constants.ACCOUNT_OBJ,
                                                    : Constants.INSERTION_ORDER_OBJ,
                                                    : Constants.PLACEMENT_OBJ,
                                                    : Constants.APTTUS_AGREEMENT_OBJ,
                                                    : Constants.UPFRONT_OBJ,
                                                    : Constants.SNAPCHATSALESTEAM_OBJ,
                                                    : Constants.ORGANIZATION_OBJ,
                                                    : Constants.ADACCT_OBJ,
                                                    : Constants.CASE_OBJ,
                                                    : Constants.BRAND_OBJ,
                                                    : Constants.UPFRONT_ENTITY_OBJ)];

Here's the problematic looping:

// loop thru list of PermissionSetAssignments

    String loopVar;

    for(PermissionSetAssignment pSa : permSetAssignments){

        // isolate PermissionSetId
        loopVar = pSa.PermissionSetId;

        // Loop thru all object permissions
        for(ObjectPermissions objPermission : allObjectPermissions){

            // find ObjectPermissions where ParentId == PermissionSetAssignment.PermissionSetId
            if(loopVar == objPermission.ParentId){
            /*
              * do work to aggregate this ObjectPermission at Assignee level
             */

My question: how can I reduce the amount of ObjectPermissions i'm looping through? I initially thought to GROUP BY on my ObjectPermissions query and somehow access only the ObjectPermissions under a particular PermissionSetId grouping within the PermissionSetAssignment loop, but that doesn't seem to be possible. I'm currently trying to batch this operation to remediate, but I feel the loop can be improved.

U.I.

Answer 1

Based on the documentation, subqueries work for ObjectPermission and PermissionSet, but for some reason they named the relationship field "ObjectPerms". So, the following should work:

SELECT ID, (select ID, PermissionsRead from ObjectPerms where PermissionsRead = true) 
FROM PermissionSet 
WHERE ID IN (SELECT PermissionSetId FROM PermissionSetAssignment WHERE Assignee.Id IN: revieweeIds)

Unfortunately if I recall correctly you can't do more than one nested where query in a single query, so if you want to limit the permission sets you get back by whether or not they have any of the correct object read permissions, you need to do a separate query:

[SELECT ID, (select ID, PermissionsRead from ObjectPerms where PermissionsRead = true) 
FROM PermissionSet 
WHERE ID IN (SELECT PermissionSetId FROM PermissionSetAssignment WHERE Assignee.Id IN :revieweeIds)
    AND ID IN :[SELECT ParentId FROM ObjectPermissions 
                WHERE sObjectType IN (:Constants.OPPORTUNITY_OBJ)
                      AND PermissionsRead = true]]

You could also test taking the two where nested queries and performing them first, then combining them in a Set<ID> variable to see if that is faster.

Afterward, loop through like:

for(PermissionSet ps : permissionSets)
{
    for(ObjectPermission op : ps.ObjectPerms)
    {
    }
}

Answer 2

If your question is how to make this line more efficient:

if(ObjPermission.ParentId == somePermissionSetId){ 

Then the simple answer is to include this criterion in your SOQL:

SELECT ...
FROM ObjectPermissions
WHERE ParentId = :somePermissionSetId
AND ...

Please note that your query itself will be significantly more efficient if you use an IN clause instead of a massive series of OR clauses:

AND SObjectType IN (
    :Opportunity.sObjectType,
    :Account.sObjectType,
    :Case.sObjectType,
    :Etc__c.sObjectType
)

---

If you want to view all object permissions for a specific user, you can run this query:

public static List<ObjectPermissions> getObjectPermissions(Id userId)
{
    return [
        SELECT
            PermissionsCreate, PermissionsEdit,
            PermissionsDelete, PermissionsRead,
            SObjectType
        FROM ObjectPermissions WHERE ParentId IN (
            SELECT PermissionSetId FROM PermissionSetAssignment
            WHERE AssigneeId = :userId
        )
    ];
}