Number of rows removed from this list by Security constraints

[Ariel Aharon]

Hi, 

I got Database view and I get this message on all of featured records.

I set ACL rules to the:

read operation - database view table

read operation - sn_hr_core_case

read operation - metric_instance

I can see as sn_hr_core.manager the tables that are joint into the db view but I get this security constraints rows error when viewing the db-view.

What am I missing here?

[olpl]

The way we have figured out how to remove the “Number of rows..” message was with a onBefore query Business Rule and replicate the ACL rules there as @Community Alums explained.

But at first this resulted in users not being able to do their own filtration in lists, in addition to messing up our data resources in a custom Workspace where we no longer could add filter conditions on Look up multiple records resouces.

It seems ServiceNow hasn’t got any functionality to “concat” the encoded query the User sets in a list and the encoded query you set in the onBefore query BR. In addition, this breaks the query you set on a data resource in UI builder with the encoded query you set in the onBefore query BR since they dont seem to be combined. If anyone can confirm this, that would be appreciated.

What we ended up doing, or found out, was you could retrieve the encodedQuery sat by the user or by another ServiceNow resource in the onBefore BR and combine them yourself.

So in the start of the onBefore query BR add this:

//Get the current encodedQuery from the user or another SN resource
let encodedQuery = current.getEncodedQuery();
//Collect all matched ORDERBY parts with regex
let matches = encodedQuery.match(/ORDERBY[^\\^]*/g);
let orderByEntries = matches || [];
let baseUserQuery = encodedQuery;

//Remove all ORDERBY segments from the encoded query string.
orderByEntries.forEach(orderBy => {
            baseQuery = baseQuery.replace(orderBy, “”);
});

//We do this to remove orderBy in the encodedQuery since this breaks the query. But we add it at the end.

let query = baseQuery;
//Build your query variable according to your ACL rules.

!Note – If your rules include the “^NQ” (The “Big” OR) you must add the baseQuery variable again.

e.g:

if(gs.hasRole(“roleX”){
            query += “^NQ” + baseQuery + “active=true”
}

Then at the end add your orderBy variable to the query.

orderByEntries.forEach(orderBy => {
query += "^" + orderBy;
});

current.addEncodedQuery(query);

 This works, but requires a bit of code and is in no way ideal. You still have to maintain both ACL's and the BR to mirror the result and I am afraid this could result in performance loss.