ACLs Vs. Business Rules for security?

Short answer: ACLs.

Longer answer: ACLs, but also sometimes query business rules; but usually for performance reasons more than security.

There are benefits to using Query Business Rules. Namely, performance benefits. This is because query Business Rules fire before ACLs run, which means that not only are you potentially returning fewer records, but you’re also eliminating whatever performance cost may have been associated with the ACLs themselves (which is a non-zero amount, especially if they’re script-heavy). That’s not to say that you should aim to go and replace all of your ACLs with query Business Rules, but there is definitely a case to be made for using both.

On the topic of security, though: one reason that I see people using query Business Rules is because they want to get rid of that “n records removed due to security constraints” message which shows up when ACLs block access to records in a list view. However, that message is often good! It makes troubleshooting really easy! Personally, I've never had a user complain about this or be confused by it, and it's really simple to explain if they do. The lack of such a message, when records are removed by "security" (using query business rules, which provide no such message) is what anyone trying to troubleshoot missing records could find confusing. 

Note: There are some situations where that “n records removed by security…” message can leak information that you might otherwise want to keep secret. For example, I might search the HR case table for cases where I am the subject person, and someone I dislike is the reporter. Just knowing how many records match that query (for example, any number greater than zero) could be more information than I want a user to have!

There are a lot of reasons not to go completely replacing your ACLs with query Business Rules; not least of which is that query Business Rules are simply not meant to act as a security measure, can be overridden, and don't provide the security-oriented features that ACLs do.

But most importantly is that some query Business Rules can cause major issues in your query results! Not just filtering records without any indication they're doing so (and being difficult to troubleshoot at that), but also in terms of the actual query results that should be returned. This is more of an issue with the query-logic in ServiceNow (and more specifically, in SQL), but query Business Rules can make this issue a lot harder to troubleshoot if you aren’t aware of the awkward anti-logic that can ensue.

Imagine you have a table with the following records: 

Shoes

Most of the records have a value in the Material field, but Barefoot [04] doesn't. This is perfectly fine, as Material is not a required field. 

Now, let's introduce a Query Business Rule that contains the code: 

This query results in the following records being returned for anyone who isn't an admin: 

Now, anyone who isn't an admin will not be able to see my Blue Suede Shoes in that table. -- But wait, what's this? Where is Barefoot [04]?

A blank string "" is not equal to the string "Suede"; not even loosely equal. If we're worried we're going insane, we can even test this by running the following line of code: 

(which of course, prints true). 

But SQL doesn't care about your basic logic, because adding a "not equal to" query on any string field, filters out all blank values!

Why? I assume because SQL hates you is why. I was unable to find any other explanation for this behavior.

But let’s say you’ve thought about it carefully, and a query Business Rule really is the best solution for what you’re trying to do. How do you get around this SQL weirdness, so you can add a “not equal to” query, using a query Business Rule? Well as it happens, you can do so in the same way that the platform does for you automatically (sometimes)!

If you have a table of shoes just like the one in the above example, and you use the query builder to do an "is not" query, you run into the same issue where records where that field is blank don't show up.

BUT - if instead of using the query builder, you right-clicked on “Suede” in the Material column, and clicked on Filter out, you would get the results you’d expect:

It works! — but why? What is this sorcery!?

What’s going on is that even though the UI shows you a neat little breadcrumb that just says “Material != Suede”, the platform pulled a sneaky on ya! If you expand the filter builder, you’ll see that the actual query says “Material is not Suede or Material is NULL”.

Taking a hint from this behavior, we can resolve the anti-logic issue whenever we want to add an “is not” query through a query Business Rule or even through the query builder on a list, by also adding an “or condition” that says “or [field name] is [‘NULL’]” - and yes, actually passing in the string “NULL”. See the following Business Rule script for an example:

This issue (and solution) are not actually specific to query Business Rules - This is regular old everyday SQL behavior. If you have a filter that says “[some field] is not [some value]” and you want blank values to show up in the results, you must add “…or [that field] is ‘NULL’”.

NOTE: You could also use “…is” followed by a blank string, or “…is empty” (instead of “…is ‘NULL’”), but it’s worth mentioning that only “…is ‘NULL’” hides the sub-condition to make the query breadcrumb look nice and succinct. This doesn’t matter as much for query Business Rules though, as their conditions don’t show up in the condition builder.

UPDATE: Turns out that this is not just an issue with “is not”/“not equal to” queries - It also applies to “is different from” and “same as” conditions. Keep that in mind when building your queries.