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.
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.
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.
You cannot use the RLS Split algorithm on a Vendor form.
When you select this option, the AR System server builds an SQL query by using
OR operators. This option overrides the value of the Disable-New-RLS-Implementation parameter for the current form.
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.
You cannot use the Subquery algorithm on a Vendor form.
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.
Changing the Row-level security (RLS) algorithm by using the arrls command
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.
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.
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
arrls command supports the following view forms to build a corresponding S table:
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
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:
|Name that identifies the user account for the AR System server.|
Password for the user account.
If you have a blank password, you can ignore this option.
|Name of the server to connect to.|
TCP port number to connect to.
If the port number is unknown, use
Text file with form names specified on a new line. For the sample file, see rls_form_list.txt.
The RLS algorithm. The options are:
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:
|Form||The source form from which the RLS field data is populated.|
Describes the S table status with one of the following options:
|Processing Start Time||Describes the time when data starts populating in the S table.|
|Last Processed Entry ID|
Indicates the last Entry ID processed from the last chunk of records.
If the server stops while populating data in the S table, the thread uses the Entry ID to continue populating data in the S table.
|Old RLS Algorithm|
Indicates the previously configured algorithm for the form.
The AR System Server uses the old algorithm when populating data in the S table.
|Total Entries||Indicates the total number of entries to be moved to the S table.|
|Entries Processed||The number of entries processed so far.|
|Error Details||Describes errors (if any) encountered while the S table is being populated.|
|Resume Migration||If an error occurs when the AR System Server populates data in the S table, rectify the error, and select this option to resume populating data in the S table.|