Boost Security by Auditing Your Privileged Users with SOQL | Salesforce Developers Blog

Part of secure application development is completing regular audits of user permissions. Keeping in mind the principle of least privilege, developers and admins should consistently monitor key permissions in each Salesforce org to ensure that they haven’t over-provisioned special privileges to their end users. Not doing so can have severe consequences, and while it may seem extreme, overly generous permissions can lead to:

  • A user accidentally making a change that impacts critical business processes
  • The loss or theft of vital business data
  • Exponential growth in risk for your company

In this post, I’d like to go through the reasons why we should conduct audits of our users permissions, how we can set guidelines to ensure that only the right number of users are given privileged access, and how to identify admins and privileged users in your org.

Why should we audit?

How long has your Salesforce org been around: one year, five years, ten years? How many admins has the org had during that time? How many times has the System Administrator profile been cloned, updated and assigned to a user? How many users can create new users, and how many can Modify All Data? I think the most common answer is going to be:

“Ummm, I don’t know… a lot?” 🤷‍♂️

I can’t think of a single Information Security Officer that would accept that answer, including Roger Grimes of CSO Online:

“Every additional administrator causes linear-to-exponential growth in risk. Every additional admin doesn’t just increase his or her own risk; if they’re compromised, they add to the takedown risk of all the others. Each admin may belong to groups others do not. If a hacker compromises A and gets to B, B may more easily lead to C, and so on.”

Roger A. Grimes, Columnist for CSO, in Too many admins spoil your security.

At Salesforce, we recommend that regular audits be conducted on the permissions that have been assigned to your users. Specifically, it’s important to look at the total number of admins and privileged users to make sure that the numbers are reasonable based on your total user base, and that there aren’t any users who have been given admin-like privileges that don’t need them (i.e. they have been over-provisioned).

Setting guidelines for admins and privileged users

At Salesforce, an admin is defined as a user with both the Customize Application and Modify All Data permissions.

So, what is a reasonable number of admins in an org?

This can vary greatly based on the size of your org and your industry. Smaller orgs tend to have a larger percentage of users with admin privileges. For example, a 100-user org having five admins, or 5% of the total user base, may seem a little high, but it isn’t totally unreasonable. However, a 5,000-user org having 250 admins, again that same 5% of the total user base, certainly sounds unreasonable and frankly unwieldy from a management perspective. My personal guideline is that somewhere between 1% to 4% of your total user base should have administrator privileges. If you have a higher percentage than that, it may be time to review your list of admins and make some cuts. Customers with larger orgs, or who are in highly regulated industries like healthcare and financial services, should find themselves on the lower end of that range.

What about privileged users? What are privileged users anyway?

Here at Salesforce, we refer to privileged users as those users with one or more of the following permissions:

  • Customize Application
  • Manage Users
  • Modify All Data
  • View All Data

Defining a reasonable number of privileged users is even harder than with admins. Because of the way companies are structured and operate, there could be large differences in the number of privileged users from one org to another. For example, take a company that has hundreds of office branches all across the globe. Each branch may need a designated privileged user (or users) who can add and remove other users for their branch due to high turnover. That company’s structure may dictate a higher number of privileged users. Now, think about an organization that is centrally managed and only needs a very small team of people who add and remove users because turnover is very low. They would have a much lower percentage of privileged users than in the first example. Because of this, it’s difficult to give an absolute guideline, however, what’s most important is to ensure that your percentage fits your organization’s needs. In most cases 2% to 5% is reasonable, but if this percentage starts to creep up towards 10%, you may want to rethink your company’s polices on privileged users. Keep in mind that it should always be the vast minority of users in your org that are privileged users.

Identifying admins and privileged users

Ahhh, at last we have reached the meat of the article. To conduct your audit, you will need to build lists of your admins and privileged users. There are a few ways to do this. The one I want to focus on is the use of SOQL to query for all users who are assigned a specific permission. While admins can leverage the Salesforce Optimizer to provide a basic readout of the admins in your org, or install the Profile and Permission Set Helper from Salesforce AppExchange to search your org’s profiles and permission sets, SOQL is the quick and easy option for developers.

Running a SOQL Query on an org is easy and there are a number of different tools you can use. Personally, I like Salesforce’s built-in Developer Console and VisualStudio Code, since that is where I do most of my development work. For this post, I’ll be using the Salesforce CLI’s force:data:soql:query command (see more info) in VisualStudio Code’s Integrated Terminal.

Here is a list of key objects that we will be using in our queries:

  • PermissionSet — represents a set of permissions that’s used to grant access to one or more users. Note that the the associated documentation does not list all the permissions available to be queried. To get a full list of permissions, you will need to run a describeSObjects() via workbench or one of the APIs like the SOAP API.
  • PermissionSetAssignment — represents the association between a User and a PermissionSet. This is how a user gets the permissions in a Permission Set or Profile.
  • ObjectPermissions — represents the enabled object permissions for the parent PermissionSet.

A couple things to note before we get started running SOQL Queries:

  1. We do NOT recommend running these queries directly in production — instead, run them in a recently refreshed sandbox. It wouldn’t have a major impact if you did run them in production, but you can get the exact same results by running the queries in a fresh developer sandbox — and that’s a whole lot safer!
  2. You will need to have the View Setup and Configuration permission to access the objects mentioned above.

Let’s start by finding our admins — those users with both Customize Application and Modify All Data permissions. Here is the SOQL we will use:

SELECT Assignee.Id,Assignee.Username,Assignee.Name
FROM PermissionSetAssignment
WHERE PermissionSet.PermissionsCustomizeApplication = true AND PermissionSet.PermissionsModifyAllData = true AND Assignee.IsActive=true

We will use the PermissionSetAssignment and PermissionSet object to find the users with both PermissionsCustomizeApplication and PermissionsModifyAllData. What’s nice about this query is that it will return any users with the two permissions, whether they have the permissions on their profile or were assigned them through a permission set. Here is what the command will look like using the Salesforce CLI:

sfdx force:data:soql:query -q "SELECT Assignee.Id,Assignee.Username,Assignee.Name FROM PermissionSetAssignment WHERE PermissionSet.PermissionsCustomizeApplication = true AND PermissionSet.PermissionsModifyAllData = true AND Assignee.IsActive=true"

And here’s the CLI’s output:

You can see that in my org, I have two admins. Wait a second — two admins?! I thought I was the only admin in my org. Jimmy is our Sales Operations Manager (great guy, reads a lot comic books) but he shouldn’t have full admin rights! Ok, I’ll need to fix that.

Alright, let’s move on to our privileged users. Here is the SOQL we will use, this time checking for more permissions by using the OR command in the WHERE clause and also including the four permissions in the SELECT clause, so that we can see who has what permissions:

SELECT Assignee.Id, Assignee.Username, Assignee.Name, PermissionSet.PermissionsModifyAllData, PermissionSet.PermissionsCustomizeApplication, PermissionSet.PermissionsManageUsers, PermissionSet.PermissionsViewAllData
FROM PermissionSetAssignment
WHERE (PermissionSet.PermissionsModifyAllData = true OR PermissionSet.PermissionsCustomizeApplication = true OR PermissionSet.PermissionsManageUsers = true OR
PermissionSet.PermissionsViewAllData = true) AND Assignee.IsActive=true

Running this query again through the Salesforce CLI using force:data:soql:query, here are my results:

OK, this time we have three results: me, Jimmy (still got to fix that), and wait…what…Susan? She is the SVP of Marketing, but she has the Manage Users permission. Susan puts together some of the best marketing campaigns I have ever seen, however she should not be adding and removing users from our Salesforce org. Great, another item to add to my To-Do list!

Well there you have it, two simple queries to help you find admins and privileged users in your org. But what if you want to go deeper and explore other permissions? Let’s cover a few other common scenarios in the next section.

Finding other highly privileged users in your org

Let’s face it, Salesforce is an incredibly powerful tool, and with all that power comes…lots of permissions (your spider sense thought I was going somewhere else with that, right? Ha, got you!). Let’s cover a few other common scenarios where you may want to see how many users have a specific permission.

How about users who can run a data export or report export?

SELECT Assignee.Id,Assignee.Username,Assignee.Name
FROM PermissionSetAssignment
WHERE (PermissionSet.PermissionsExportReport = true OR PermissionSet.PermissionsDataExport = true) AND Assignee.IsActive=true

Need to know which users in your org have API Access? Check this out:

SELECT Assignee.Id,Assignee.Username,Assignee.Name
FROM PermissionSetAssignment
WHERE PermissionSet.PermissionsApiEnabled = true AND Assignee.IsActive=true

Do you have users in your org that have the Password Never Expires permission? I hope not! Run this SOQL to double check:

SELECT Assignee.Id,Assignee.Username,Assignee.Name
FROM PermissionSetAssignment
WHERE PermissionSet.PermissionsPasswordNeverExpires = true AND Assignee.IsActive=true

If you have a private sharing model for an object that is highly sensitive, it may be worth it to check who has View All and Modify All permissions on that object. This one gets a little tricky, as we have to use nested SOQL and the ObjectPermissions object. In this case, I’ll search for users with View All OR Modify All on the Opportunity object (you can replace Opportunity with any standard or custom object API name):

SELECT Assignee.Id,Assignee.Username,Assignee.Name
FROM PermissionSetAssignment
WHERE PermissionSetId in (
SELECT ParentId
FROM ObjectPermissions where (PermissionsViewAllRecords = true or PermissionsModifyAllRecords = true) And SObjectType = 'Opportunity')
AND Assignee.IsActive=true

Note that in some cases, you may see what appears to be duplicate users in the lists that get returned by these queries. This will happen when a user is assigned a profile AND a permission set, or assigned to multiple permission sets with the same permissions. Essentially, the user was granted the same permissions multiple times. This scenario is not uncommon, so be sure to account for that when formulating counts and percentages. It may be helpful to use GROUP BY clauses in the SOQL to aggregate the duplicates.

Conclusion

Salesforce recommends the best practice of auditing and reviewing user permissions as part of your release readiness checklist. With each Salesforce release, as you test in your pre-release sandbox, audit your user permissions to ensure that your users have the proper permissions. Companies with higher turnover, that are in highly regulated industries, or that store highly sensitive data in their org, may want to conduct audits even more frequently (e.g. quarterly). As a final reminder, anything more than 2% to 5% of privileged users in an org is probably a cause for concern. Ensuring that users are not over-provisioned will help boost your org’s overall security posture, protect your data, and ensure that you are not opening your company up to additional unnecessary risk. So let’s schedule your org’s next audit now. Looks like you’re free on Friday…

Additional Resources:

About the author

Christopher Marzilli the Director, Platform Success at Salesforce that spends his days driving customer success on the Salesforce Platform and nights playing video games with his son.

More blog posts by Christopher Marzilli

Stay up to date with the latest news from the Salesforce Developers Blog

Subscribe