How to determine fields for which a given profile does not have read permission

Question

Full disclosure, my expertise is not on the Salesforce side, but in .NET and SQL; however, I've been tasked with assisting my company with a data migration from our original org to a new one. The in-house Salesforce team and our SI have determined that neither Informatica, Apex Data Loader, nor any other COTS solution is suitable to our needs, so we are building a custom application to handle the migration. That decision wasn't my call, so I can't really argue whether or not it was the right one. The problem we're running into is that the profile of the service account that the migration will run under does not have read access (FLS) to all fields within the source org, and the team says there is no easy way to grant that profile access to all of them.

As a first step towards solving this problem, I'm trying to find a way to determine, for a given profile, which fields within the org (both standard and custom) are not accessible to it because of FLS. The approach I've been working on is first to find all the fields that are accessible to it by querying (SOQL) the FieldPermissions object:

SELECT Field 
FROM FieldPermissions 
WHERE ParentId = '{PermissionSetIdForTheProfile}'

then get a list of all fields within the org and exclude any from that list that are in the first list.

The issue is with getting that list of all fields for the org. SOQL queries of the FieldDefinition object don't return any field to which the profile does not already have read permission. Likewise calls to the describe API. It's obvious why a profile would be blocked from reading the data of fields where FLS read permission is absent, but it's harder to grasp why it's so difficult to just get a list of the names of all the fields so that we can determine which ones it still needs and give that list to the admins to address.

Does anyone know of a way (SOQL, API, Apex, other?) to compile a list of (API) names for all fields in all objects, regardless of FLS? Alternatively, can anyone suggest another approach to ensuring that a given profile has read access to all fields in the org?

Edit:

Per the comment from identigral, maybe it's better to approach this as ensuring that the permission set to which the profile belongs has access to all fields. I'm just not experienced enough with Salesforce to necessarily know what to ask, but I'm open to suggestions.

Answer 1

The general algorithm for this would be as follows. You'll want a way to read metadata, I'd recommend sfdx. First, call listMetadata on CustomObject to get a full list of objects. Next, call describeMetadata using the list of custom objects (which actually includes standard objects), and you'll get back a list of objects with their fields. Then, you just need to extract that data and compare it to the data you pulled out from the FieldPermissions object, and you'll have the list of differences.

However, note that some fields that are described as read only are really read-only. Formula fields, some name and auto-number fields, compound fields, some computed fields, etc, may all not have a way to set their permission any higher than read-only. In addition, globally required fields, like master-detail relationships values and required fields (at the field level, not the page layout), will also not allow their permissions to be set--even if you set them to view/edit.

I just tried it, and it should be able to set FieldPermissions in Apex, so you could write a Batchable class to iterate through every object with a scope size of 1, then every field, then bulk add those entries to the permission set or profile. Something like this:

public void execute(Database.BatchableContext context, sObjectType[] scope) {
  FieldPermissions[] perms = new FieldPermissions[0];
  PermissionSet permSet = [SELECT Id FROM PermissionSet WHERE ... ];
  sObjectType objType = scope[0];
  // Make sure read-only permissions are removed
  Database.delete([SELECT Id FROM FieldPermissions WHERE ParentId = :permSet.Id AND sObjectType = :''+objType], false);
  // First, make everything read-only
  for(sObjectField field: objType.getDescribe().fields.getMap().values()) {
   if(!field.getDescribe().isRequired()) {
     perms.add(
       new FieldPermissions(
         ParentId = permSet.Id,
         Field = ''+objType+'.'+field,
         SObjectType = ''+objType,
         PermissionsEdit = false,
         PermissionsRead = true
       )
     );
    }
  }
  Database.insert(perms, false);
  // Now we're going to add edit permission
  for(FieldPermissions perm: perms) {
    perm.PermissionsEdit = true;
  }
  Database.update(perms, false);
}

That should be efficient enough to guarantee that all fields are set to their maximum value.