1

i'm trying to run an SOQL query on salesforce to get all users and their permission set id. it is important the list return will be by user not by permission sets, meaning if i have 1000 users i will get back 1000 records and for each record the user attributes like email etc + permission sets list of Id's assign to him

SELECT+id,PermissionSet.id+FROM+User i tried finding the relationship field name but i'm not so familiar wtih salesforce, please assist

Yaniv Rozenzweig
  • 11
  • 1
  • 2

1 Answers1

0

https://developer.salesforce.com/docs/atlas.en-us.238.0.object_reference.meta/object_reference/sforce_api_erd_profile_permissions.htm

The table you're looking for is PermissionSetAssignment

Top-down:

select id, email,
    (select permissionsetid
    from permissionsetassignments
    where permissionset.isownedbyprofile = false)
from user

or bottom-up

select assigneeid, assignee.email, permissionsetid 
from permissionsetassignment 
where permissionset.isownedbyprofile = false
order by assigneeid
eyescream
  • 18,088
  • 2
  • 34
  • 46
  • thanks this is exactly what i was looking for and it works perfect. i will use the Top-Down. – Yaniv Rozenzweig Nov 01 '22 at 03:57
  • what about permission set groups and public groups, how can i add those to the Top-Down – Yaniv Rozenzweig Nov 01 '22 at 03:57
  • perm sets assigned as part of the group will be something like `select id, (select permissionsetid, permissionsetgroupid from permissionsetassignments) from user`. Groups are pain to query, you can't get group assignments top-down, the "relationship name" is not present. You'll need some recursive mess on Group and GroupMember table. Easy when user is added directly as human, painful when he's added indirectly, based on his user role or the fact he's a member of another group... https://salesforce.stackexchange.com/a/13511/799 may be a start but you won't do it in 1 query – eyescream Nov 01 '22 at 08:51
  • what if i need only the direct assignments not indirect, will that help making the query possible for public groups adding to same query? – Yaniv Rozenzweig Nov 07 '22 at 07:54
  • Nope, sorry. If you run "describe" (for example in https://workbench.developerforce.com/ go to Standard & Custom Objects -> User and view "Child relationships" you'll see that data for GroupMember is incomplete. If the "relationshipName" field isn't present we can't do it as top-down query. compare with what same screen returns for PermissionSetAssignment.AssigneeId child relationship. You'll need multiple requests or ask SF dev to make for you a piece of Apex that would generate simple flat view. Have it exposed as REST service with "@httpget" annotation for example.... – eyescream Nov 07 '22 at 11:57