Today's guest blogger is Ross Talbot, a Principal Developer at Sonoma Partners.
Recently we were working on a project and came across a gap in our expectation of data access as a Salesforce admin user.
We were using a query to find updated documents in Salesforce content when we realized some files we expected to see were missing from the results. While the query overlooks these files, the UI shows them to an admin user. So where do you start the investigation?
First you double-check your Salesforce security settings. You have the system administrator profile. You have the view all data permission. So why can’t you access the document via SOQL?
Found on the User Edit screen:
Found on the System Administrator Profile:
Salesforce content security inherits from the parent object, be it a record or Chatter group. However, even as an administrator using SOQL we cannot access a file posted in a private group if we are not a member of that group. The same holds true for a file posted on a record that is not explicitly shared with the user when it comes to SOQL. Salesforce has a helpful table here to help us determine who should be able to access files.
So let’s test this out. We find the file in the web UI and we are able to view it. As expected, I see it and it sees me. As an administrator, we join the private group containing the file and we are able to access it in the Salesforce UI. So next we test whether joining a private group gives us access in SOQL, and we are successful. We then leave the group, and accessing the file still works in the UI, but again our SOQL query does not include the file.
Next our investigation leads us to test changes in our SOQL query. We can access the files in SOQL, but we must specify the ID of the file using an equals or IN condition. Now I know what we are thinking, “Great, now I have access, but this sounds pretty tedious since I have potentially hundreds or thousands of files to track down, and in an integration I can’t keep manually updating the list of IDs. Plus, there are API limits to consider, since I can’t efficiently run tens of thousands of SOQL selects, one for each file. So where does this leave me?”
Which brings us to the KingswaySoft SSIS Integration Toolkit for Salesforce, and for our purposes we are using a SOQL generated query. For our input, we can find the file IDs we need by searching the related data via the related parent or record ids. Our sample query will include more fields than the RelatedRecordId in this case so that we can identify the correct record for troubleshooting and other identification purposes, however for the next steps it is the column we will key in on. Setting a KingswaySoft Salesforce Source to use an Object Query allows us to filter for posts updated or created after a specific date that have content attached to them. We will load these results in an SSIS record set for use in the next step of our processing.
We can use a Script task in SSIS to construct our SOQL query using these RelatedRecordIDs for input to select the correct content records for processing. We take a comma separated list of IDs in the allIds SSIS variable and use a C# String to build the SOQL query we set in the objectQuery SSIS variable. By batching these IDs, we can avoid hitting a limit on the number of IDs we include in our IN condition.
The generated SOQL query is then added to the Kingswaysoft Source adapter by using the SSIS variable objectQuery we constructed and the source type ObjectQuery, and now we are able to bring the content into SSIS or SQL staging tables for processing in our migration or integration. Since we are already batching the query results, the batch size in our source query can remain the default value of 1000.
Now the next time you run into an issue accessing private files through the Salesforce API, you see their every move.