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 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 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.

Note

You cannot use the RLS Split algorithm on a Vendor 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 flag for the current form.

Subquery

Before using the Subquery algorithm, ensure the following:

Select the Enable Security Table checkbox on the Row-Level security fields panel.
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 (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.

Note

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.

https://youtu.be/qaS1Ha5yigA

&amp;amp;lt;p&amp;amp;gt;&amp;amp;lt;br/&amp;amp;gt;&amp;amp;lt;/p&amp;amp;gt;

Important fields on the RLS Migration Pending form

The 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:

Field	Description
Form	The source form from which the RLS field data is populated.
Status	Describes the S table status with one of the following options: Pending In Progress Completed Terminated With Error
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 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.