getRowCount() incorrect when using GlideRecordSecure()

[Nikolay Megdano]

Hi,

When we have some row level security ACLs on a table we can use GlideRecordSecure() to iterate over the rows on which the user has permissions without having to explicitly put if statement to check if gr.canRead() for every gr.next() in the loop. This is great but if we do a gr.getRowCount() it returns the count without subtracting the records which the user is not allowed to see. Here is one example for better understanding:

var table = "someTable";
/*lets say this table has 6 records. 4 records with field u_active=true 
and 2 records with field u_active=false */
/*create row level ACL which based on the user role will restrict this role 
to access only the records where u_active=true(in total this role should 
have access to 4 records)*/


var grs = new GlideRecordSecure(table);
grs.query();
console.log('GRS: ' + grs.getRowCount()); /* this will return 6 (which is wrong  
because the user doesn't have permissions to 2 of the records) */

var scnt = 0; //create manual counter to increase on grs.next()
while(grs.next()){
	scnt++;
}
console.log('GRS Manual Count: ' + scnt); /* this will return 4 which is correct 
because the iteration skips the records to which we don't have permissions */


//compare with GlideRecord
var gr = new GlideRecord(table); //this will return 6
gr.query();
console.log('GR: ' + gr.getRowCount());

var gcnt = 0;
while(gr.next()){
	gcnt++;
}
console.log('GR Manual Count: ' + gcnt); //this will return 6

So the question is how to get the correct row count when using the GlideRecordSecure. Iterating and calculating it manually like in the example above is not an option because we might have large data set and use pagination.

I was thinking also to use GlideAggregate somehow but couldn't think of a way.

Any help is appreciated. 🙂

[Nikolay Megdano]

Hi,

Thanks for the hint but this doesn't work.

This logic assumes that the access on the whole table(all rows combined) is boolean value but doesn't consider that we might have different access to a few fields in this table for example.

In other words this logic won't decrease the amount counted but it will either return the count of all records selected by the query or will return nothing if the user is restricted on this table.
P.S.

The same is mentioned in another thread -> here.

[Rishabh Jha]

Hi @Nikolay Megdanov ,

Yes, you're right. In order to check the access to the individual records, and return the row count based on the current user's access, you'll have to iterate and capture the counter. Even if there was an OOTB function to count that for you, it'd have done the same implicitly, and would've probably given you more-or-less the same performance.

That's why even in report dashboards, the aggregate counts actually show all the rows, and it's the drill-down to the rows that displays data according to user's access to records.

Starting the Orlando release, ServiceNow has introduced GlideQuery, that has type checking, and many other features, and to get the count, one can simply do :

var numRows = new GlideQuery(table).count();

(https://events.servicenow.com/widget/servicenow/knowledge2020/knowledgecatalog/session/1581555110988...)

But I'm not sure even if this would have an implicit row level access checking.

Thanks & Regards,

Rishabh Jha

Aavenir (http://www.aavenir.com/)

[Nikolay Megdano]

The ideal solution should be the possibility to select only those records of interest in the initial query but this is probably complicated otherwise ServiceNow would have implemented it like this already.

This way the queries will be lighter because no excessive data will be selected so no need to filter it while looping.

[Rishabh Jha]

Yep, but the problem is, in order to know the records of interest, which in this case is the records accessible to the context user, ACLs will have to be executed on each record. Also, since the record count, and ACLs both keep changing, and has to be executed on user context, it is something that can't be cached or saved to be able to refer/query it later; it has to be real time, executed in the logged on user's context.

If your use-case is limited to get the count for a particular user-role, you can trigger an event through an after insert-delete business rule (so that it's async), and then on the event listener, write the logic to run a GlideRecordSecure table scan for that user-role, and then save the counter to some system property or field. That way, you can read the counter readily from that property/field when needed.

Thanks & Regards,

Rishabh Jha

Aavenir (http://www.aavenir.com/)

[MG Casey]

I am running into the same issue. I only see two paths:

• You have to run the GlideRecordSecure query twice. 
  • Once to get your desired window of records
  • Another time to loop through and get an accurate total count of records

OR

• You need to set up "Before Query" business rules so that the table is automatically filtered to what you estimate the user should have access to.

Either way is not ideal at all. I would opt for the second route only because it won't negatively affect performance.