Improve performance by using RLS algorithms

The Row-Level security (RLS) feature enables an administrator to filter database content based on user-specific roles. 

The AR System server adds constraints to the SQL statement to restrict access. This might increase the complexity of SQL or might affect the AR System server performance. For this, the AR System server provides multiple RLS algorithms. Depending on your requirement, you can select an algorithm at the system level or at a form level.

For example, in Remedy Smart Reporting, querying the database might take more time for users who are not administrators. In this case, using the Subquery algorithm may fetch the results faster than with the default RLS algorithm.

Using the AR System:RLS Autodiscovery form to set the RLS algorithm

The AR System:RLS Autodiscovery form provides you with some insight into the RLS queries used in the backend. The AR System:RLS Autodiscovery form displays the details of forms where the RLS query takes longer time to fetch the underlying data. For such forms, you can consider switching to an alternative RLS algorithm, such as Subquery.

The AR System:RLS Autodiscovery form displays the following details:

• RLS queries that have been run in your environment that cross the threshold limit set in the Rls-Autodiscovery-Minimum-Elapsed-Time setting. This setting is for the com.bmc.arsys.server.shared component.
• List of forms where the RLS query crossed the threshold limit set in the Rls-Autodiscovery-Minimum-Elapsed-Time setting.
• The number of times RLS query runs on a particular form that crosses the threshold limit set in the Rls-Autodiscovery-Minimum-Elapsed-Time setting.
• The time required to fetch data from each RLS query if the query crosses the threshold limit set in the Rls-Autodiscovery-Minimum-Elapsed-Time setting.

For more information, see Rls-Autodiscovery-Minimum-Elapsed-Time.

The following screenshot displays the AR System:RLS Autodiscovery form:

The AR System server adds a record in the AR System:RLS Autodiscovery form through an asynchronous process without impacting the system performance. Before adding a record, the AR System Server verifies the following details:

• The query is RLS based.
• The Subquery algorithm is not enabled on the queried form.
• At the database level, the RLS query takes more time than the value specified for the Rls-Autodiscovery-Minimum-Elapsed-Time setting in the Centralized Configuration. 
  The default value of the Rls-Autodiscovery-Minimum-Elapsed-Time setting is 5 seconds. 

RLS algorithm options

The Row Level Security Fields panel on the Definitions tab in the Remedy Developer studio offers the following algorithms to fetch data from the database. 

• Default
• RLS Split
• Combined likes
• Subquery

Default

The value of the Default algorithm is set by the AR System server and is dependent on the value of Disable-New-RLS-Implementation parameter that is set in the Centralized Configuration.

• If the Disable-New-RLS-Implementation parameter is set to True, the Combined likes algorithm is set as the Default algorithm.

• If the Disable-New-RLS-Implementation parameter is set to False, the RLS Split algorithm is set as the Default algorithm.

RLS Split

When you select this option, the AR System Server builds an SQL query by using the predefined RLS Split function to evaluate the RLS. This option overrides the value of Disable-New-RLS-Implementation parameter for the current form.

Combined likes

When you select this option, the AR System server builds an SQL query by using LIKE and OR operators. This option overrides the value of the Disable-New-RLS-Implementation parameter for the current form. 

Subquery

Before using the Subquery algorithm, ensure the following:

• On the Row-Level security fields panel, select the Enable Security Table checkbox.
  
• Ensure that the value of the Overlay-mode parameter in the Centralized Configuration is not zero.

When you select this checkbox, the AR System server creates a new Security table (S table) with the same schema ID as that of the database table. This one-time asynchronous operation separates permission data to a new S table.

When performing a query on a form by using the Subquery algorithm, the S table is accessed as a nested query of the main query against the data table (T table).  This enables the database to utilize indexes more efficiently and return results faster.

• The AR System server might take time to populate initial data in an S table. The time depends on the number of records you have in the database. You can view the status of the S table by using the RLS Migration Pending form. While the AR System server is populating data in the S table for a form and you update another form to populate the S table, the request for populating data in the S table is queued.
• When you select the Subquery algorithm and you run a query while data is populating in the S table, the AR System server uses the value set for the Default algorithm.

Using the Subquery algorithm for Join forms

A join form fetches data from the underlying member forms. Therefore, to use the Subquery algorithm on Join forms, you must enable the Subquery algorithm for the member forms.

To enable the Subquery algorithm on a Join form, you must:

• Identify the dynamic access fields on the Join form.
• Identify the member forms from where the dynamic access fields are coming.
• Enable S table for all member forms that are sourcing the dynamic access fields.

You can use the Subquery algorithm on the Join form.

See to the following example:

In this diagram:

• The AST:ComputerSystem is the join form with AST:Attributes and BMC.CORE:BMC_ComputerSystem as member forms.
• The BMC.CORE:BMC_ComputerSystem is also a join form with the BMC.CORE:BMC_ComputerSystem_ and BMC.CORE:BMC_BaseElement as member forms.
• Since the dynamic access fields 112 and 60989 are coming from the BMC.CORE:BMC_BaseElement form, you must enable the subquery algorithm on the BMC.CORE:BMC_BaseElement form and on the AST:ComputerSystem form.

Learning more about improving performance by using the Subquery algorithm

Watch the following video (37:21) to learn how to use the subquery algorithm to optimize Row Level Security searches. The webinar also covers RLS Subquery implementation, query format, and troubleshooting best practices.

 https://youtu.be/qaS1Ha5yigA

Changing the Row-level security (RLS) algorithm by using the arrls command

Use the arrls command on the RLS-enabled forms to automatically set the RLS algorithm to Subquery.  When you set the Subquery algorithm, it facilitates to fetch search results faster. 

The arrls command accepts inputs from the AR System:RLS Autodiscovery form and also from the flat file. You can manually create the flat file. The flat file and the arrls command file (arrls.bat or arrls.sh) must reside in the same folder. For the sample file, see rls_form_list.txt.

The arrls command first updates the form names in the AR System:RLS Autodiscovery form, where the repeat count is more than the value specified in the  Rls-Autodiscovery-Minimum-Elapsed-Time setting.  After that the AR System server updates the RLS algorithm that you have given as an input. Later on, it updates the algorithm for the forms specified in the flat file.

Supported view forms

The arrls command supports the following view forms to build a corresponding S table:

• SMT:UnionSmartIT_TicketConsole
• SHR:Union_OverviewConsole
• SMT:Social_WorklogView

If the AR System:RLS Autodiscovery form or the flat file has supported view forms, the AR System server enables the Subquery algorithm for all corresponding forms and creates S tables for forms.

The arrls command and options

The arrls command (arrls.bat or arrls.sh) is available in the ARSystemInstallationFolder\ARSystem\artools folder. 

[-u] [-p] [-x] [-t] [-file] [-algo]

arrls -u Demo -p Password -x vw-pun-ar1 -t 46262 -file /temp/rls_form_list.txt -algo Subquery

The following table describes the arrls command options, which can be used in any order in the command:

Important fields on the RLS Migration Pending form 

The following RLS Migration Pending form shows the S table population status:

The following table describes some of the important fields on the RLS Migration Pending form:

Related topics

Setting the Row-level Security fields for fetching data

Controlling access by using implicit groups: Row-level security