Who all have read access to a given Record

Question

I am trying to replicate salesforce ACL model for a record in an external system by querying Salesforce. What I mean by that is, given a record for an Object(for example: Case), I should be able to tell which all users/groups have access to this record.

• I do not care are field level security, I am fine with ignoring any field level details.
• I only care about who all is able to view/read that record (I do not care about who is the owner or who can edit it)

Given the Salesforce sharing model is quite complex. I came up with a basic approach.

My current approach: I found some sharing tables exist in Salesforce for most of the objects(example: CaseShare) which contains some rows for each record saying which user or group has access to this record. I plan to query this table to get all users and groups who have access. Later, I'll query the group maintainence table to check what all users belong to a particular group.

Queries I plan to use:

Query 1:

SELECT UserOrGroupId FROM AccountShare WHERE AccountId='X'

Query 2:

SELECT Id, Email FROM User WHERE UserRoleId IN (SELECT RelatedId FROM Group WHERE Id IN  ( RESULTS_OF_QUERY_1_HERE))
UNION
SELECT Id, Email FROM User WHERE Id IN (SELECT UserOrGroupId FROM GroupMember WHERE GroupId IN  (RESULTS_OF_QUERY_1_HERE))

My question is:

1. Would my approach work in all cases. Considering the salesforce access model is quite complex, does this sharing table capture all details?
2. Is there a limit on the number of rows in a sharing table per record. This is needed to plan for capacity in my external system.

Answer 1

No, it doesn't. The UserOrGroupId can point to a separate group, which then has more GroupMember records that may refer to other Group records... Salesforce calculates only as many shares as it needs to cover all the access levels using an Oracle SQL Hierarchy Query. Unfortunately, we don't get access to this type of filter, so we have to build this manually. It can take many queries to get to all of this data. If you don't have a lot of Group/Group Member records, you might try loading everything into a Map, then you can recursively discover members. You'll have to choose a strategy of using either more SOQLs or more SOQL rows/CPU/heap, or you can try a hybrid approach of querying up to 6 levels (5 levels of relationships) per query, extracting any other IDs on the way. Here's one answer that demonstrates that algorithm.