Docs.bmc.com will undergo a brief maintenance outage 27 March 2025. The site will be unavailable for ten minutes starting at 6:30 AM CDT/5 PM IST.

Improving data retrieval performance by selecting a row-level security algorithm

As an administrator, use row-level security (RLS) to secure record data and provide access to only specific users or groups. For example, by using row- level security, an accounts manager can restrict access to the salary data for other employees, or an administrator can restrict access to only the relevant data about their company to customers.

BMC Helix Innovation Studio restricts access to record data by adding constraints to the SQL statement. This step increases the complexity of the SQL statement and affects the   server performance.

BMC Helix Innovation Studio supports multiple row-level security (RLS) algorithms to enhance row-level access control and retrieve the record data quickly. 

Related topics

Creating-join-record-definitions

Enabling-row-level-security-by-defining-security-labels


Example

In your custom application, querying the database to retrieve a large number of records might take more time for users who are not administrators. To overcome this challenge, use a row-level security (RLS) algorithm. For example instead of using the default RLS algorithm, use the  Subquery  algorithm to retrieve the data faster.

To select a row-level security algorithm

  1. Log in to BMC Helix Innovation Studio, navigate to the Workspace tab, and select the application.
  2. On the Records tab, navigate to the record definition to which you want to add an RLS algorithm.

  3. Click edit_icon.png icon in the Properties pane on the right side, and in the Security labels section, update the following fields:  

    Properties

    Description

    Example

    RLS algorithm

    Select from the following RLS algorithm option for enhanced row access control:

    • Default
    • RLS Split
    • Combined Likes
    • Subquery

    21310_RLS algorithm configuration.png

    Enable security table

    Select the toggle to store permissions in a separate security table to enhance index utilization in the database and return the results faster.

    Important: If you select the Subquery option, the Enable security table is enabled by default.

  4. Click Save.

You can modify the RLS algorithm option at any time.

Important

If you modify the RLS algorithm during migration (which is the updating of the security table records for the associated parent table records), the algorithm is updated after the migration is completed.

You can also change the row-level security (RLS) algorithm by using the arrls utility. For more information, see Changing the Row-level security (RLS) algorithm by using the arrls command.

RLS algorithm and security table applicability

The following table displays how RLS algorithm and security table settings are applicable to various record definitions:

Record type

RLS algorithm

Security table

Regular

✅️

✅️

Join

✅️

❌️

Archive

✅️

✅️

Audit

✅️

✅️

External

❌️

❌️

Custom

❌️

❌️

RLS algorithm options

BMC Helix Innovation Studio supports the following algorithms:

RLS algorithm

Description

Default

The value of the Default algorithm is set by the BMC Helix Innovation Studio server and is dependent on the value of the Disable-New-RLS-Implementation parameter 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

BMC Helix Innovation Studio server builds an SQL query by using the predefined RLS Split function to evaluate the row-level security. This option overrides the value of Disable-New-RLS-Implementation parameter.

Combined Likes

BMC Helix Innovation Studio 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 record . 

Subquery

BMC Helix Innovation Studio server creates a new Security table (S table) with the same schema ID as the database table. This one-time asynchronous operation separates permission data into the new S table. When performing a query on the record by using the Subquery algorithm, the S table is accessed as a nested query of the main query against the database table (T table). This step enables the database to utilize indexes more efficiently and return results faster.

Before using the Subquery algorithm, make sure you have selected the Enable Security Table check box.

BMC Helix Innovation Studio server might take time to populate initial data in an S table. The time depends on the number of record instances you have in your record definition. You can view the status of the S table by using the RLS Migration Pending form. While BMC Helix Innovation Studio server is populating data in the S table for a record, if you update another record to populate the S table, the request to populate data in the S table is queued.

Important:

  • We recommend monitoring the RLS Migration Pending form for any failure. If an error occurs, correct the error, and resume the migration process.
  • When you select the Subquery algorithm and run a query while data is being populated in the S table, the BMC Helix Innovation Studio server uses the value set for the Default algorithm.

RLS Migration Pending form 

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

 21310_RLS migration form.png 

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

Field

Description

Form

Identifies the source record 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

Indicates the time when data starts being populated 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 it is 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 record.

BMC Helix Innovation Studio 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

Indicates the number of entries processed so far.

Error Details

Describes errors (if any) encountered while the S table is being populated.

Resume Migration

Resumes populating data in the S table if an error occurs during data population, after the error is rectified.