Reference Cascade Rule ignored because of before query business rule - why?

[maynartt]

Background:

We're developing a scoped app that has data access/security requirements that go beyond normal ACL "Requires role" and Condition.   Therefore, our scoped app's ACLs have Advanced checked, and the script reaches out to client data (rather than the db for response time/performance reasons) to determine if the user has access.   However, upon further testing, we encountered certain times when that client data was unavailable, for example, when trying to export a CSV from a list of records, the user doesn't get the records they see in the list.

Therefore we changed the record read acls by unchecking Advanced, and created before query business rules on each table (calling a single script include that does the work) to determine whether or not the user has access.   At the same time we created a new record read acl on the same table, with the same "Requires role" roles and Condition, but no script and Advanced unchecked.   This works for the most part, but for the following scenario:

Record 1 (from Table 1) exists with Field 1 that's a reference to Record 2 in Table 2.   The Reference Cascade Rule on Field 1 is Restrict.

User 1 has no access to Record 1, but has crud access to Record 2.   User 1 deletes Record 2.   Instead of getting that "Delete of Record 2 not allowed because of a reference in record Record 1 within the Table 1 file" error message and the deletion being prevented, it allowed the deletion.

Note that when we didn't have the before query bus rule, and we had the record read acl with the scripting, the deletion was prevented with that error message.

Has anyone run into a similar scenario, and/or know what can be done about it?

Thanks!

[LaurentChicoine · Accepted solution]

Normal,

Query business rule applies on server side script (so even the system does not see the record 1 when the user deletes the record 2). What you had before (ACLs), are not applied on server side unless using GlideRecordSecure, or validations like canRead() and the cascade rule must be using standard GlideRecord.

Switch back to ACL or, add a manual cascade rule using a on before business rule which would query the Record 1 table for any existing reference but by setting current.setWorkflow(false); before querying the GlideRecord. This will bypass the query business rule.

[maynartt]

Laurent,

Thanks for the recommendation.   Since we can't go back to the ACL, I attempted your business rule recommendation, and it worked.  

Listing the details of that business rule just to confirm this is what you had in mind (if a business rule field isn't mentioned, it was left blank or as the default).

Table = Table 2

Advanced is checked.

When = before

Delete is checked.

Script is the following:

(function executeRule(current, previous /*null when async*/) {

      // look to see if Record 2 is referenced by any records in Table 1, and if so,

      // bypass execution of the already existing before query bus rule on Table 1

      // that checks if the current user has access to records on Table 1,

      // because the user may not have access to it, and

      // we need to stop it from being deleted if it's used.

      var tableOne = new GlideRecord('table_1');

      tableOne.addQuery('field_1',current.sys_id.toString());

      tableOne.setWorkflow(false);

      tableOne.query();

      if (tableOne.hasNext()) {

              current.setAbortAction(true);

              gs.addErrorMessage("Record 2 cannot be deleted because it is used on a record in Table 1");

      }

})(current, previous);

If we were to use this method, we'll probably have to make it more robust because in the future we may have new references to records in Table 2.   In that case I think we can probably query the sys_dictionary table for all references to Table 2, and execute the hasNext for each one found.   This way we won't have to remember to change this business rule each time we create a new reference field to it.

Thanks again!

[LaurentChicoine]

Yes the business rule is what I had in mind.

Related to making it dynamic and querying the sys_dictionary, yes that would make sense. You could query for fields referencing that table and looking if the cascade rule is restrict (even if the restrict won't apply this could be how you filter the dictionary record to know if the rule should apply).

[maynartt]

Excellent suggestion concerning setting those fields' cascade rules to restrict to guide the process.   I'm going to mark your first reply as Correct Answer.   Thanks!