Get the list of profiles having access to a specific object - SOQL Interview Question

Off-late a specific interview question has been asked in Salesforce Interviews a lot. That too especially when SOQL is concerned.

And the question goes like this, how can I get the list of profiles that has specific permission on a specific object?

The query goes like this!

List<PermissionSet> permissions = [SELECT Profile.Name FROM PermissionSet WHERE IsOwnedByProfile = TRUE AND Id IN (SELECT ParentId FROM ObjectPermissions WHERE PermissionsCreate = True AND PermissionsRead = True AND PermissionsEdit = True AND PermissionsDelete = True AND PermissionsRead = True AND SObjectType = 'Account') ORDER BY Profile.Name];
System.debug(' 🚀 ' +permissions);

In case you are interested in understanding how this works, continue reading.

Every profile will be associated with a Permission Set. Here is the visual confirmation for that.

PermissionSet permission = [SELECT Id FROM PermissionSet WHERE PermissionSet.Profile.Name = 'System Administrator'];

The next part is, objects and their associated permissions are provided to us in the object ObjectPermissions.  

ObjectPermissions will have a foreign key called ParentId which references the primary key in the object PermissionSet.

So the below query is going to give me a list of PermissionSet Id's and associated permissions.

List<ObjectPermissions> objectPermissions = [SELECT ParentId FROM ObjectPermissions WHERE PermissionsCreate = True AND PermissionsRead = True AND PermissionsEdit = True AND PermissionsDelete = True AND PermissionsRead = True AND SObjectType = 'Account'];

Now am gonna use this list and fetch all the profile's names from the PermissionSet object by using the Id field (primary key) in the WHERE clause.

I know it can be overwhelming just by the looks of it.

Try to have a look at it a couple of times and then you will get to the bottom of it.

Hope this helps!

I had put together a FREE course on Javascript that helps you master Lightning Web Components.

It just takes 2 minutes to finish the course and you will be able to feel the difference.