getRowCount() incorrect when using GlideRecordSecure()

Nikolay Megdano · ‎06-18-2020

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 · ‎06-21-2020

Hi there,

Thanks for your information. However I am not sure what you mean.

Here is my understanding, please correct if this seems wrong to you.

When building pagination chooseWindow() is used. gr.chooseWindow(currentLocation, currentLocation+pageSize). This way we take the data by chunks with light queries to the DB helping performance. This means we can iterate over only one chunk of data(initialy we are on page 1) therefore total count can not be calculated with loop.

If we take the whole data set with one query and iterate over it this makes the pagination pointless.

Simon Christens · ‎06-21-2020

Hi again

This is a working solution for pagination used to grab all knowledge articles from a 3rd party which also counts the total amount of articles that ive gotten.

As you can see i have both "rowcount" and "offset" because if query for all / or most data you might not get what you want so offset is then used to start again where the last query stopped while rowcount is the current amount of data recieved

Hope it makes sense

this.offset = 0;
(function(){
    
    getKnowledge();

})();

function getKnowledge(){


    var r = new sn_ws.RESTMessageV2('Known errors', 'Default GET');
    r.setStringParameter('offset', this.offset);
    var response = r.execute();

    var failSafe = 10000; //Used to stop the script if something goes wrong to prevent infinite loop - can be adjusted if needed
    var responseBody = response.getBody();
    var resultBody = JSON.parse(responseBody);
    var rowCount = resultBody.result.length;

    if(rowCount == 0){
		
		gs.log('No more articles reviewed', 'REST Known errors');
        return;
    }
	if(this.offset > failSafe){
        
        gs.log('Offset over 10k - stopping to prevent infinite loop', 'REST Known errors');
		return;
	}

    for (var i = 0; i < rowCount; i++) {

        this.createKnowledge(resultBody.result[i]);
    }

    this.offset = this.offset + rowCount;

    getKnowledge();
}

function createKnowledge(kbRec){

    var excludeList = ''; //comma separated list of fields that should be excluded from the mapping

    var kb = new GlideRecord('kb_knowledge');
    if(!kb.get(kbRec.sys_id.value)){

        //We dont have this record so lets create it
        kb.initialize();

        for(var key in kbRec){

            var base = '';
            var kbcategory = '';

            if(excludeList.indexOf(key) > -1){

                continue;
            }

            if(kb.isValidField(key)){

                if(key == 'kb_knowledge_base'){

                    base = this.checkBase(kbRec[key]);
                    kb.setValue('kb_knowledge_base', base);
                
                }else if(key == 'kb_category'){
                    
                    if(base == '')
                        base = this.checkBase(kbRec['kb_knowledge_base']);

                    kbcategory = this.checkCategory(kbRec[key], base);

                    kb.setValue('kb_category', kbcategory);

                }else if(key == 'category'){

                    this.ensureCategoryExistance(kbRec[key]);
                    kb.setValue('category', kbRec[key].value);

                }else if(key == 'sys_id'){

                    kb.setNewGuidValue(kbRec[key].value);
                }else{

                    kb[key] = kbRec[key].value;
                }
            }
        }

        kb.setWorkflow(false);
        kb.insert();
      
    }
}

function checkBase(baseObj){

    var kbase = new GlideRecord('kb_knowledge_base');
    if(!kbase.get(baseObj.value)){
       
        kbase.initialize();
        kbase.setValue('title', baseObj.display_value);
        kbase.setNewGuidValue(baseObj.value);

        return kbase.insert();

    }else{
      
        return kbase.getUniqueValue();

    }

}

function checkCategory(catObj, base){

    var cat = new GlideRecord('kb_category');
    if(!cat.get(catObj.value)){

        cat.initialize();
        cat.setValue('label', catObj.display_value);
        cat.setValue('value', catObj.display_value.toLowerCase());
        cat.setNewGuidValue(catObj.value);
        cat.setValue('parent_table', 'kb_knowledge_base');
        cat.setValue('parent_id', base);
        cat.setValue('active', 'true');
        
        cat.setWorkflow(false);
        return cat.insert();

    }else{

        return cat.getUniqueValue();
    }

}

function ensureCategoryExistance(catObj){

    var cat = new GlideRecord('sys_choice');
    cat.addEncodedQuery('nameSTARTSWITHkb_knowledge^elementSTARTSWITHcategory^value=' + catObj.value);
    cat.query();
    if(!cat.next()){

        cat.initialize();
        cat.setValue('name', 'kb_knowledge');
        cat.setValue('element', 'category');
        cat.setValue('label', catObj.value);
        cat.setValue('value', catObj.value);
        cat.setValue('dependent_value', 'Known Error');
        cat.insert();
    }
}

Satyanarayana C · ‎06-19-2020

Hi,

Just wanted to confirm that you verified all ACLs on the table to make sure there is no other ACL that is granting access to the records.

Thanks

Nikolay Megdano · ‎06-21-2020

Hi there,

I prepared this quick example with a clean table with no other ACLs specially to confirm this behavior.

Rishabh Jha · ‎06-21-2020

Hi @Nikolay Megdanov,

Please try the snippet below in your script. It uses GlideAggregate, and should hopefully give you the result that you're looking for.

var table = "someTable";
var count = new GlideAggregate(table);
count.addAggregate('COUNT');
count.query();
var recordsWithAccessCount = 0;
if(count.next() && count.canRead()) {
  recordsWithAccessCount = count.getAggregate('COUNT');
  gs.info('recordsWithAccessCount: '+recordsWithAccessCount);
}

Thanks & Regards,

Rishabh Jha

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