Business Objects in Visual Builder allow you to setup security authorization rules to control who can access and modify data. These rules can take into account the value of specific fields in a row. For example, you can limit updated to rows in the employee table to be allowed only if the job level is less or equal to 3 or for cases where the job level is higher than 3 and the salary is less than 5000:
In addition, you have access to a function that provides you the username of the currently logged in user that you can use in these conditions. Another function returns true/false based on whether the currently logged in user has a specific application role. A common example for this would be to limit the ability to delete a record so only the user who created a record is allowed to delete specific that record:
More Complex Situations
A limitation of the rules above is that you can't add more complex logic into the right side of the equations – for example, you can't use groovy code and add "if then else" condition.
A possible solution when the rules are more complex would be to use a function at the database level that will contain the logic that helps control who can update a record. This data can then be used in the left side of the equation. This means that your application needs to base the business objects on table in an existing schema into which you can add your own function.
The PL/SQL function can execute complex logic and return a value that will have the info you need to establish your access rule. These functions can be part of database views that your BO is based on. The result of the function then appear as "regular" columns in the BO that your rule is based on and can be used in the left side of the rules.
In the example video below we use a PL/SQL function that calculates the username of the user who is allowed to view specific rows based on some logic. While the demo used a very simple logic that involves accessing another table with the id of the record we want to check, the function can of course have more complex logic that uses if/else conditions, fetches from multiple tables etc.
Once the function is in place we create a view that selects the fields from the table along with an additional column that is based on the function. That column value is dynamically calculated for each row when the table is queried.
Now our BO authorization can use the value of this dynamic column in our authorization logic.