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

This interview question on SOQL is going to make you aware of how can use SOQL and get all the profile names having access to a specific object.

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);
Query that gives us a list of profiles having said permissions on the SObject 

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'];
Query confirming that every Profile will be associated with a Permission Set

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'];
Query giving us a list of Permission Set Id's having the said access

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!