Before Query Business Rules - The *Other* Access Control

Access Controls are a great tool to limit data visibility to those who need it.  However, there are some drawbacks:

1. The dreaded 'Number of rows removed from this list by Security constraints' message.  Many organizations don't want their users to know they are being denied access, but this message makes it all too apparent.
2. In large lists of data, the 'allowed' records do not bubble to the top - they can be hidden pages down in the list where they are difficult for users to find.
3. In a script-based ACL, the script must run for each row returned - In some cases, this can cause significant performance degradation.

Before Query Business Rules can help!

Detailed Explanation

Before Query Business Rules are just what they sound like - Business Rules that run before the query on a table occurs.  They offer an opportunity to add query terms that will limit the data returned, and can therefore act as security measures.  There are many advantages to using these where possible:

1. Because the list of data returned is pre-filtered, no more 'Number of rows removed from this list by Security constraints' message, and all data is returned in a clean list that is seemingly 'un-filtered' to the end-user.
2. If scripting or an additional query is necessary to determine how the data must be filtered, it is far more efficient to do that once in a Before Query Business Rule than to do it hundreds or thousands of times through iterative runs of an Access Control script on each row of the table.

Steps to Implement

An example scenario:

An organization wishes to segment users by group - Any given user should only have visibility into other users that belong to one of their groups.  In order to accomplish this, one must gather a list of all groups a user belongs to.  Secondly, one must use that list of groups to gather every member of each group.  This can be tricky in an ACL, as a query against the 'sys_user_grmember' table is necessary in order to gather all users in a particular user's groups.

To accomplish this via an ACL script on the 'sys_user' table, a possible solution is:

answer = false;
var myGroups = gs.getUser().getMyGroups();
var myGroupMembers = [];
var it = myGroups.iterator();
var i=0;
while(it.hasNext()){
   var myGroup = it.next();
   var gr = new GlideRecord("sys_user_grmember");
   gr.addQuery("group", myGroup);
   gr.query();
   while (gr.next()) {
      myGroupMembers.push(gr.user.sys_id);
   }
   i++;
}
for (var i=0; i < myGroupMembers.length; i++) {
   if (current.sys_id == myGroupMembers[i]){
      answer = true;
   }
}

This accomplishes the goal, but must run for each row that is accessed - in an organization with thousands of users and hundreds of thousands of group memberships, the performance impact can be debilitating!

Alternatively, the following Before Query Business Rule on the 'sys_user' table could be implemented:

var myGroups = gs.getUser().getMyGroups();
var myGroupMembers = [];
var it = myGroups.iterator();

var gr = new GlideRecord("sys_user_grmember");
if (it.hasNext()){
    myGroup = it.next();
    var grps = gr.addQuery("group", myGroup);
}
while (it.hasNext()){
    myGroup = it.next();
    grps.addOrCondition("group", myGroup);
}
gr.query();
while (gr.next()) {
    myGroupMembers.push(gr.user.sys_id);
}

if (myGroupMembers.length > 0){
    var q = current.addQuery('sys_id',myGroupMembers[0]);
    for (var i=1; i < myGroupMembers.length; i++) {
        q.addOrCondition('sys_id', myGroupMembers[i]);
    }
}

This will return the same result as the Access Control script but only needs to run a single time before the query.  In actual implementations, this technique has been known to reduce processing time to return a table by several minutes, given a large enough data set.

• A Before Query Business Rule is very effective to limit data visibility at the row level.  Access Controls must still be used to limit visibility at the field level.
• Because Before Query Business Rules run before the data set is returned, they will run prior to processing of Access Controls.  If a customer prefers the warm reassurance of security that Access Controls provide, you may implement both a Before Query Business Rule and an Access Control to accomplish the same ends concurrently - the same performance benefits will be realized.
• Performance Best Practice for Before Query Business Rules