1

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. Salesforce UAR Tool

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)
    {
    }
}
  • This is a good suggestion and one I didn't implement at the start because I wasn't familiar with ability to query children of Parents in same query. I'll have to implement this later due to time constraints, thank you. – Nicholas Zozaya Dec 9 '18 at 21:34
  • This is probably the best way to solve this problem going forward, so am marking as answer. – Nicholas Zozaya Dec 9 '18 at 21:37
  • @NicholasZozaya Keep in mind that this may not solve your issue, which is really just that you are querying and processing a lot of records. You might want to consider having the page load using an asynchronous method like Javascript Remoting/RemoteAction, which can be configured with a longer timeout, possibly combined with paginating your result list to reduce the amount of data you need to load at once. – IllusiveBrian Dec 9 '18 at 21:40
  • I'm was timing out via Queueable Apex, so it was running pretty long. I've since batched the permissions aggregation itself so that it only processes 100 ObjectPermissions at a time, which has worked. – Nicholas Zozaya Dec 9 '18 at 22:12
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
        )
    ];
}
  • I can't filter by one PermissionSetId in my situation - I need to triangulate all ObjectPermissions (child) for every PermissionSetId (parent) via the ParentId field. I need all ObjectPermissions for this. I wanted to know if there was a way to retrieve records within just a specific grouping of a SOQL result (maybe by providing that grouping's value to a method). So, instead of looping through all ObjectPermissions, I can simply loop through ObjectPermissions within the given PermissionSetId's grouping. Does that make sense? – Nicholas Zozaya Dec 9 '18 at 5:12
  • @NicholasZozaya You seem very much to be having an XY Ptoblem. Terry explaining your end goal rather than intermediate steps. – Adrian Larson Dec 9 '18 at 5:50
  • Indeed - after some digging it seems like this isn't possible. Sorry. Aggregation Functions Documentation for anyone who stumbles into this: developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/… – Nicholas Zozaya Dec 9 '18 at 5:58
  • No I mean you should better explain your use case. At a higher level. Not just an implementation detail. – Adrian Larson Dec 9 '18 at 6:06
  • Edited question to include more context. – Nicholas Zozaya Dec 9 '18 at 20:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.