Query Business Rules vs. ACL - comparison

[Tom Sienkiewicz]

Hi everyone,

I recently had to explain to someone the difference between Query BR and ACLs and realized that this is an ongoing debate topic for many people. Opinions on which method of restricting access is preferred are quite divided.

Below I would like to show a comparison of several areas and how both methods impact the specific area.

Area	Query BR	ACL	Comment
Access restriction	Row-level only (e.g. entire Incident record)	Global, table or field level	Use ACL if you need "conditional" access to some fields or if you want to set some fields read-only in a safe way.
User Experience	Will only show the records the User can see, with no message at the bottom about "some records removed due to security". E.g. if there are 500 Incidents but the User can only see 10, it will show one page with only 10 records.	Will show all pages with the restricted records being "invisible" and a message at the bottom "some records removed due to security". E.g. it will show 10 pages of records, but the user will only see 1 record on each page - the rest will be empty rows.	The ACL way of showing records can be annoying for users who sometimes have to click through a lot of empty pages to get to the records they want to see. It also makes bulk editing from list difficult. On the other hand, some prefer to show this to users as it makes them aware restrictions are in place.
Performance	Query BR are only evaluated once per each table query. They just return the resulting records from DB to the application.	ACLs have to be evaluated for every record/field individually.	Query BR can provide a performance boost in some cases, compared to ACL. Note: for tables with a lot of logic/scripts (like Task table) they will impact ALL calculations, which can be disaterous to the performance! See comments below the article.
Debugging	You cannot debug Query BR using the "Debug Security" module.	ACLs can be easily debugged with "debug security"	Query BR will be shown in the "Debug Business Rules" module, but you can only see that a particular BR has run, no info on whether it actually restricted any records.
Impact on Scripts	Query BR will impact all your scripts running on the table where the BR is applied. E.g. if you have a script doing  a GlideRecord on Incident table, it will be affected by Query BR according to the restrictions put in it.	ACLs are not impacting the scripts. Exceptions being: using GlideRecordSecure or adding "canRead/canWrite" etc. to your GlideRecord in scripts.	Query BR can make script debugging really hard sometimes.
Scoped Applications	You cannot create Query BR on tables from a different scope within a scoped app.	You cannot add roles to ACLs from a different scope. You cannot use script evaluation in ACL wihch is for a table in a different scope. You cannot create wildcard (*) rules for tables in a different scope.
User Impersonation	In some cases, it was impossible to properly test or debug Query BR by impersonating a user. We actually had to log in as that user to see the real effect*	No known issues related to impersonation.	*I have not been able to reproduce this now, but our testers reported this issue on several occasions. Keep this in mind just in case.
Update Sets	Query BR can impact the proper inserting or updating of records via Update Sets - e.g. if on target instance, you don't have access to a specific record due to Query BR and try to update it via an Update Set, it won't happen.	No adverse impact on Update Sets.	Under normal circumstances, you will probably not be affected by this behaviour. Things get interesting when you start restricting configuration files.

Edit (based on fredjean's comment below): You should avoid using ^NQ (master OR) operators in Query BR queries. This can cause issues mentioned in the below article: https://support.servicenow.com/kb?id=kb_article_view&sysparm_article=KB0564887

hopefully this will help someone make an informed decision.

I use both of these methods depending on the specific case. For sure, if you decide to go with Query Business Rules, it helps to have them properly documented somewhere, as it is a much less natural and obious place to look for any access restrictions.

Let me know if you have any comments on this. Thanks!

[Stef R]

A great recap and an access strategy tool ! Thanks.

[Mark Roethof]

Interesting overview!

Kind regards,
Mark

[Tom Sienkiewicz]

Let me add one more thing - Query BRs can be put to good use to restrict the visibility of Modules/Applications to some users, if advanced/scripted conditions are required. Still, make sure to document this and make all admins aware where to look.

[Mwatkins]

Hi there,

Very interesting topic and a helpful breakdown. I am a senior engineer in ServiceNow's performance support team. Regarding the topic of Performance impact, I would add that, while there is a potential for a Before Query Business Rule to improve performance, there is also a great possibility for them to cause performance issues.

Every year I am involved in multiple account level performance escalations where custom business rule logic from query business rules is the primary cause of the performance issues. Here's some general ways that I've seen this unfold:

• Many types of ACL's can be cached and their answers stored in-memory for instant retrieval. On the other hand, many Before Query Business Rules involve some query that now has to be executed thousands of times per minute in situations where the answer is always the same, causing unnecessary load on the database. Consider that the solution you are designing might work fine when one user is accessing the data with only a few thousand records in the related tables, but that same solution will need to scale to millions of records and perhaps hundreds of simultaneous data access requests.
• Often a Before Query Business Rule will use GlideRecord methods to add conditions to the "current" object. This, in turn, appends new conditional operations to the WHERE clause of the SQL statement that executes, making that query more complex and potentially less performant. In particular, 'contains' filters that search GlideList fields, like the watch_list field, are bad for performance.
• As you mentioned, unlike ACL's, Before Query Business Rules get applied to queries originating from other scripts. This means that the added complexity and execution cost is being applied in places where it was never needed or intended. It also creates the potential for infinite recursion (A calls B calls C calls A calls...). This also makes it difficult to troubleshoot in the future since the developers of other scripts might wonder why they are getting unexpected results (NOTE: GlideRecord.setWorkflow(false) will allow the query to bypass Before Query Business Rules - use with caution since it will also bypass all other script engines and workflows, but not ACLs or security constraints).
• Finally, the added complexity of using a customized script to manage security causes performance issues in the sense that it makes future changes to code or usage more fraught with unknown risk. This is hard for ServiceNow to troubleshoot since we must try to understand a complex new customization and it makes it hard for customers to manage going forward as well since teams that manage it are usually not the teams who implemented it. Excellent in-line documentation is a must. And, ideally, a link to up-to-date comprehensive design documentation should be readily available as well.

To summarize; Roles, ACL's, Domain Separation and other out-of-box security features are the preferred and recommended method of applying data security and/or segregation to ServiceNow data. They are optimized for performance and cause less technical debt than a custom scripted Query Business Rule. Ideally, if you are attempting to achieve data security/segregation in a way that will result in some logical evaluation before every single call to a frequently accessed table, you should try very hard to use something other than a Before Query Business Rule. It should probably be a last resort when features that are actually designed to accomplish the same goal have been exhausted.

NOTE: If you feel that you must use a Before Query Business Rule (and I recognize that sometimes there just isn't another option) make sure you design it to be very efficient:

Official product documentation site: About Before Query business rules [for data segregation]

Performance Best Practice for Efficient Queries - Top 10 Practices

Performance Best Practices for Server-side Coding in ServiceNow

Performance Best Practices for Before Query Business Rules 

Please 👍 if Helpful

[Tom Sienkiewicz]

Thank you for this great further inisght! Indeed the complexity of Query BR is something that can contribute to performance drops. Something everyone should take into account.

Tt is really important to have those properly documented and being aware where/why those are used so in case of issues you can fall back on ACLs or other means of filtering the records.

[Mwatkins]

Sorry to beat a dead horse, but just yesterday I was involved in another severe performance degradation due to a custom Before Query Business Rule. A new rule was added to restrict task visibility to only those tasks that are from the same location as the user. The customer had done their due diligence, testing in lower environments, but wasn't able to predict the impact when run at scale on production.

To sum things up:

• BR runs on a relatively large table given the amount of times that users access that table in a day. Table has over 10 million records.
• Users access this table as the main table to perform their jobs, the table is queried thousands of times per minute.
• When the BR was activated, every query pattern permutation against this table was changed and became more complex, adding multiple JOIN operations - this is a very important point. In order to ensure the database is running in optimal fashion it is often necessary to tune the database by creating indexes and creating "hints" at the app layer to assist MySQL/MariaDB in choosing the best index. In an ideal world we could assume that the right index already exists and the database always chooses the right index. However, in the real world, the greater the complexity of the query, the less likely it will be that the database chooses the most efficient query plan.
• There are things that can be done by ServiceNow to improve query execution based on query pattern (indexes and index hints) but as soon as the patterns change, all that work to optimize things is nullified. It becomes an increasingly brittle solution.

<sarcasm>Thanks for all the good new, Matthew!</sarcasm> What is the solution? The solution is to keep your database query patterns as predictable and simple as possible.

• Favor solutions that will result in the fewest query pattern variations. Avoid creating dynamic code conditions that will result in hundreds of query permutations for a frequent data access operation. User A's query looks like one thing, user B's query looks like something else and so on.
• Create centralized Script Includes for manageability. Keep the code that actually calls current.addQuery or current.addOrCondition in a central place. All Before Query Business Rules will call those Script Includes.
• Instead of making one monolithic query, use two or more queries and feed the results of each query into another. *NOTE: If you do this via code, it shifts the complexity to the application layer and may create more problems than it solves*
  • See Database Performance: Improving Slow OR and JOIN Queries
  • Make use of GlideRecord.addJoinQuery(table, field, field) where applicable. Counter-intuitively named, this method does a SQL sub-query not a JOIN. Sub-queries can help MySQL choose the optimum execution plan by essentially breaking a complex query into two simpler queries.
• Leverage some type of caching strategy to avoid frequent lookups of static data. For example, a user's region does not frequently change. Instead of adding a complex condition that requires a JOIN between the region table and a many-to-many table to every single task query, is there a way that you can just cache each user's region membership in memory for the duration of their user session and add a simple condition to task that does not require a JOIN (e.g. task.region IN (...list of region sys_id's from memory to which the user belongs...)? This can done using an actual in-memory cache (e.g. gs.getSession().putClientData()) and/or using a "truth table" that stores the simple results of more complex calculations. See our article Caching Data to Improve Performance.
• Avoid complex conditions using code logic in all situations where it is not necessary. For example if a task has no value in the region field, then don't even bother with doing any logic that would restrict based on the region field. Or suppose you add two conditions with an OR statement like, user is in region(1,2,3) OR region = 7. Perhaps there is some logical way to make the OR statement unnecessary based on information that is known prior to executing the query. 
• Put your data separation field directly on the table(s) that need to be separated. For example, if you need to separate data on the task table, have a field on the task table that represents the group to which each task belongs. Requiring your data separation logic to cause JOINs between multiple tables for every data retrieval is a sure fire way to add complexity and reduce query efficiency.
• Do not allow separated records to belong to more than one data group. If at all possible, find a solution that will enable you to represent group membership with a single value in a single field on any separated tables; e.g. a task should not be in both group A and group B. I have seen folks implement data separation by using a Glide List field on the table to be separated (task) with disastrous results. This may take some clever thinking to solve (ServiceNow's own Domain Separation solution solves this with the concept of Domain Paths (see Domain Separation - Advanced Concepts and Configurations), and Contains and Domain Visibility. Note, this does not mean a user cannot belong to multiple groups - it is referring to the data whose visibility is to be separated itself.

[Tom Sienkiewicz]

Thank you for another great addition! I have updated the article with a warning for those who do not read comments 😉

[Anurag Tripathi]

Great Read!! 

[GTSPerformance]

My pleasure Tomasz, and thanks for bringing up such a timely topic!

[Mwatkins]

My pleasure, Tomasz. Thanks for brining up such a timely and helpful topic! Clearly there is lots of interest.

[The SN Nerd]

Developers would be less incline to use Before Query rules if there was an option to hide the "Number of records removed by security constraints" message and the results.

I raised this on the Idea portal 11 months ago, and it is still in review.

Do you think you could escalate it?

https://community.servicenow.com/community?id=view_idea&sysparm_idea_id=8cdcc77fdbcd8090d58ea345ca96...

[Mwatkins]

So true. I will certainly escalate that idea. As you may be aware, ServiceNow is currently in the midst of rolling out new UI's that leverage web components . I will escalate this to that team in the hope that eventually the new UI can accommodate this.

The future of ServiceNow UI:

https://youtu.be/EJcGhTMQShk

https://developer.servicenow.com/blog.do?p=/post/now-experience/

[Tom Sienkiewicz]

I'll just add one more comment based on a recent discussion about Domain Separation.

Quite often, ACLs or Query Business Rules are mentioned as a "simple alternative" to the Domain Separation plugin.

I am really a big fan of the above plugin and would really recommend it to anyone seriously considering splitting data and process, but if you absolutely must do this on your own, do not use Query Business Rules for that, however tempting it might appear. It is not a good idea and will seriously impact your instance performance.

You are much better off with ACLs here in any case. If you are worried about scripts ignoring ACLs, replace your GlideRecords with GlideRecordSecure wherever needed.

[The SN Nerd]

Domain separation is a great solution for a multi-tenant instance, but in my experience, I would not recommend it for single-tenant.

[Tom Sienkiewicz]

Hah, this is probably a topic for another big discussion elsewhere. I don't see a problem using it internally, depending on the requirements - but you sure need to know what you are doing with DS 🙂 I saw a few cases of DS gone horribly wrong.

[The SN Nerd]

Given the expertise required and license uplift, I don't think the value is there for that use case. But every situation is unique 😉

[Tom Sienkiewicz]

Jeez, I am exactly in this kind of pickle right now with my ongoing project.

We have a pretty complex access checking logic, which would be perfectly suited to be implemented as ACLs.

But we just CANNOT have users click thorugh tens of empty pages to find the single record they can view, then another 5 empty pages and next 5 records they can view etc...

List filters are not a solution in this case.

So even though I HATE the idea of duplicating the ACL logic in a Query BR, in some cases there currently is no good workaround it seems. So we will have both Query BR and ACLs on same table, firstly because scoped apps require ACLs, and secondly, because I am not risking putting my access checking logic into one BR script which some junior dev can accidentally break.

The above enhancement idea is really a must have IMO.

[The SN Nerd]

Be sure to upvote the idea!

[Fred Jean]

Great stuff,

I'll just add this regarding Query Business Rules :

• Be careful with the table hierarchy, QBR only apply when query is explicitly made on the table where that QBR is defined. So if you want to restrict access to incidents for instance and do this only by a QBR on the incident table, user would be able to bypass this by querying the task table instead. So if you need a QBR you'd better double it with an ACL if security matters.
• You should not use the NQ operator on QBR, so defining proper queries can be challenging; see also this : https://alps.devoteam.com/expert-view/servicenow-system-security-before-you-go-crazy-with-before-query-business-rule/

[Tom Sienkiewicz]

Thanks for your comments!

Good point about pairing BRs with ACL sometimes. Quite often Query BRs are used together with ACLs, especially to remove the warning at the bottom of a list about restricted records/not to force users to click through empty pages. It's good to double check that the users either do not have access to the base table, or to ensure ACLs are in place too.

Also thanks for the mention about the NQ issue in Query BRs. I will link to it from the main article to make it easier to see.

[Mwatkins]

Again, love the article! Thanks so much for writing it. I'm gonna jump in again with another warning. There is a design problem with using Before Query Business Rules instead of ACLs. It can lead to unexpected behavior in your business logic. The problem is this:

1. ACLs only run after GlideRecordSecure or just prior to presenting data in the UI.
2. Before Query Business Rules run on every GlideRecord execution.

So what? Imagine this:

Suppose you have a Script Include that uses a GlideRecord query that collects a group of task records. The code then loops through the results and updates some of them. The script include gets triggered by a Scripted REST Web Service that runs as a certain integration user and has no reason to apply security. It won't be blocked by ACLs since a GlideRecord call from script doesn't get limited by ACLs.

Now suppose someone builds a Before Query Business Rule in your instance that restricts visibility of the task table to only tasks for the same company as the current user.

If your Script Include was previously updating any task records where the company was not the same as the  integration user, then your Script Include will stop working like it did before! This is just one small example of the type of unexpected behavior that can occur when Before Query Business Rules are used for access control. It all happens because they were never intended to be used as security measures and therefore they execute in contexts where security was not meant to be applied.

What can you do about it?

You could add a condition to your business rule for !gs.isInteractive() to avoid certain background GlideRecord calls. That doesn't cover every scenario though. You could also use gs.setWorkflow(false) inside your GlideRecord queries in background scripts, but that has limitations that it cannot run across application scopes and, of course, it would mean that no Script Engines would run when your GlideRecord executes.

Also, you could add debugging to your Before Query Business Rule that can be turned on/off with a property. Make sure to leave it OFF in production until needed. Then if you see weird behavior where records seem to be missing from result sets, turn on the debugging to see if your Before Query Business Rule is causing the problem.

See:

Scripting Security

Creating and Editing Access Controls

Please ✅ Correct if this solves your issue and/or 👍 if Helpful

"Simplicity does not precede complexity, but follows it"

[Fred Jean]

To me Query Business Rules should most of the time be restricted to some roles.

When that's the case your script should keep working as before.

Now I'm a bit surprised that you say QBR "were never intended to be used as security measures", because :

• documentation says "Use this query business rule to prevent users from accessing certain records"
• On top of my head I just can't think of any other use for these QBR; and I have only ever used them and seen them used for that

What are QBR intended for then ?

[Mwatkins]

Yours is a very good question! Thanks

I believe the document you are quoting is this one:

https://docs.servicenow.com/bundle/newyork-application-development/page/script/business-rules/concep...

That article is talking about a specific, long standing Business Rule, 'incident query'. It is not an article about Before Query Business Rules in general. However, I can certainly see how that specific case could be extrapolated into a more generic principle. There are a number of other out-of-box QBRs that do similar things. For example: 'user query', 'group query', 'Restrict query'...

I think the best answer is that QBRs are intended to filter data. There are many ways to filter data in ServiceNow that are not, strictly speaking, designed for the purpose of security. For example, is the purpose Filters for security?

That being said, QBRs obviously can and are used to "supplement"  security models/data separation models in limited, carefully vetted use cases. Particularly, they are helpful in removing the 'Rows removed... by security' message from the UI.

My statements are based on discussions with members of the ServiceNow platform development team. Ultimately QBRs are not owned by the Security team nor part of that Security product roadmap - they are a more general scripting API. Perhaps it would be better to say that the use of Before Query Business Rules for security/data separation is discouraged; as is described in the following article.

https://docs.servicenow.com/bundle/orlando-platform-administration/page/administer/company-and-domai...

I hope that my comments are seen as a helpful contribution and I haven't added to the confusion!

Please ✅ Correct if this solves your issue and/or 👍 if Helpful

"Simplicity does not precede complexity, but follows it"

[The SN Nerd]

If only ServiceNow removed the 'x records have been hidden' and returning record numbers you can't read, so people wouldn't need to use QBR's as a workaround for the platform's implementation of security...

How is that going, by the way, @Mwatkins  😛 

[Fred Jean]

This article mainly discourages use of QBR versus Domain separation. But if you don't have domain separation...

An it still says QBR are about "data segregation" so to me this still security.

I still still I agree it should be used with caution.

[Fred Jean]

Yes, why don't they just generate a QBR behind the scene when we define a record ACL with conditions ...

OK, some people might say they need to know some records are hidden (I have yet to meet users that would want that ...), but then it could be an option (global property or checkbox on the ACL).

[Mwatkins]

One cool change since last time we talked is that the IDEA is "In Consideration". I am still pushing and getting traction from my side and it is now a big priority for my team. I'll say that I am hopeful.

[Fred Jean]

Can you post a link to that Idea ?

[Mwatkins]

Ability to disable "Number of records removed by security constraints"... and results returned

by Paul Morris

[Fred Jean]

thanks, upvoted.

I also noticed one of the comments includes a link to an article that advertises QBR as "The Other Access Control".

https://support.servicenow.com/kb?id=kb_article_view&sysparm_article=KB0523826

[Mwatkins]

Yep. It is a fair point. FWIW, that was written in 2013 by a ServiceNow Solution Consultant who is no longer with ServiceNow, but it certainly is an example of someone from ServiceNow stating that Before Query Business Rules can be used for access control. I think it is a perfect example of the type of solutioning that many implementers, both inside and outside ServiceNow, have done when trying to get around the dreaded 'Number of rows removed from this list by Security constraints' message. I feel your pain.

[The SN Nerd]

While we are talking about BQR and Security...

I will note that OOB, ServiceNow uses BQR for security in CSM.
So while some may say ServiceNow's position is not to use BQR for security, they do it themselves in their own products. Just sayin.

@Mwatkins appreciate your continued investment in this one. I might get Developer advocates across this one.

[Tom Sienkiewicz]

Everyone, let me add one more specific case where you might want to consider using a Query BR instead of a Read ACL.

There is a specific design limitation for ACLs in the list view. You can read about it here: https://support.servicenow.com/kb?id=kb_article_view&sysparm_article=KB0529493

Basically, if within your ACL you use dot-walked fields for evaluating a condition, it will only work if you add those fields to the default list view for everyone.

E.g. if we are on the "Incident" table and you want to restrict read with ACL to only the records where assigned_to.manager | is dynamic | Me, it will not work correctly, unless you add the assigned_to.manager dot-walked field to the list layout.

I have just tested this exact behaviour on a Quebec instance and while a Query BR works perfectly fine in this scenario, the ACL was restricting too many records. Once the above field was added to the list layout, both scripts restricted the same amount of records.

I wonder if this is a consistent behaviour, let me know if this actually works differently for you. thanks!

[Tom Sienkiewicz]

EDIT: sorry, I need to debunk this below false statement after all. I tried this now in several other Utah instances and can still see the "old behaviour", looks like some issue with my PDI.

Looks like we still need to use both ACLs and Query BRs o some occasions!

Everyone, a great change in Utah! (at least what I can observe on my instance)

I'm commenting here as I cannot edit my original article anymore, which is less than optimal.

In Utah there is no longer a need to "duplicate" the behaviour of ACLs with Query Business Rules. With just applying record-level ACLs, if a user cannot see specific records, they now get filtered out from any list view. No more "empty" rows and clicking through many pages and also no more "Number of records removed due to security constraints..."!

[Fred Jean]

Hello @Tom Sienkiewicz are you sure of that ? Is that not only if you use the new Data Filtration feature ?

[Tom Sienkiewicz]

@Fred Jean thanks for asking, well, I've only checked it on one Utah instance so far, however that instance does not even have the Data Filtration plugin installed 🙂

And I also heard similar reports of ACLs being "transparent" from other sources. But please do let me know if you notice anything suggesting there might be other things involved in this 🙂 

[The SN Nerd]

I don't believe this has fixed the 'Security constraints' message issue, for the following reasons:

1. Developers have stated that currently data filtration happens after the DB query and pagination and does not fix the issue.
   Source: https://www.youtube.com/watch?v=UsjbPMHVs7U&ab_channel=ServiceNowDevProgram
2. According to the Utah product documentation, which is unchanged since Tokyo:
   "Data filtration rules run after the database query for READ operations and are evaluated before ACLs."
   So this wasn't changed in Utah.
   Source: https://docs.servicenow.com/bundle/utah-platform-security/page/administer/security/concept/data-filt...
3. My idea to fix this on the ideas portal is still "In consideration".
   Source: https://support.servicenow.com/ideas?id=view_idea&sysparm_idea_id=8cdcc77fdbcd8090d58ea345ca9619dd&s...

I haven't tested this comprehensively, though.

[Tom Sienkiewicz]

@Fred Jean @The SN Nerd yup that was some sort of instance fail after all. I have now done more testing on other instances and can still see the "old" behaviour. I edited my above post to not mislead any folks out there!

It's a pity though. Maybe one day it will be handled.

[Mwatkins]

@Tom Sienkiewicz @The SN Nerd @Fred Jean I spoke with ServiceNow's product team in charge of this feature and confirmed we didn't do anything in Utah to remove the "Number of rows removed..." message. It is still on their radar, but I don't have a solid target for the roadmap yet. Let's continue to upvote the IDEA. It's #3 most voted at the moment.

IDEA: Ability to disable "Number of records removed by security constraints"... and results returned 

[Sandip Patil1]

Very Informative.. Thank you for sharing.

[Harini Dhanapal]

Amazing differentiation. Much needed. Thanks for sharing this!!

[Tom Sienkiewicz]

An update on the topic of debugging ACL and Query BR - there is now a very nice SN application called "Access Analyzer"

https://store.servicenow.com/sn_appstore_store.do#!/store/application/21d5e77677171110638cfe21fe5a99...

This app checks if the user passes both ACL and Query BR for a specific table, making it much easier to figure out if a Query BR is messing things up.

Highly recommend to download and use this app (at least in non-prod instances).

[VasuM]

It is very informative and deep comparison between these two, really very good