Troubleshooting row-level security issues

The row-level security (RLS) enables access to form data that you want to restrict and control at the individual record level.

BMC Helix ITSM applications use row-level security to provide multi-tenancy and manage access at the company or support group level. Access to records is controlled by the AR System groups that a user is a member of and can be changed dynamically through workflow setting fields at the record level.

The SQL, which is used to query the database, is complex and might lead to performance issues.

Symptoms

Performance issue is observed when you fetch data by using the RLS query.
Users who are members of different AR System groups experience different performance issues when carrying out similar activities in an application.
Users who are members of many AR System groups experience performance issue as compared to users who belong to the lesser groups.

Scope

Administrators (users who are a member of the Administrators group and have a fixed license) do not experience the problem as they have implicit access to all records and are not subject to RLS restrictions.
Usually experienced by multiple users and does not vary over time.

Resolution

Perform the following steps to troubleshoot RLS-related issues:

Identify long running RLS queries
Confirm that RLS is causing performance issue
Analyze RLS terms
Identify the alternate RLS option

Step 1 — Identify long running RLS queries

Step	Task	Description
1	For AR System server versions earlier than 20.02, consider applying the latest platform hotfix to obtain the AR System:RLS Autodiscovery form	The RLS autodiscovery form automatically records details of slow RLS queries and their related forms. Analyze the RLS queries and their related forms to identify an alternate RLS option or perform tuning at the database level. For more information, see Improving-performance-by-using-RLS-algorithms.
2	Review logs from the arexception.log file	The arexception.log file captures examples of poorly performing RLS queries. You can enable the logs by using the AR System Administrator — Server Information form. For information about arexception.log, see Viewing-exception-logs-for-SQL-and-API-calls On the Server Statistics tab of the Server Information form, configure the threshold over which long running activities are logged. If you enable exception logging in the API/SQL Performance Tracking form, the long running SQL calls are logged in the arexception.log file. When the exception logging is enabled, all calls that exceed the minimum elapsed time value are recorded in the exception log. The performance statistics is collected regardless of whether exception logging is enabled or not. You can save the top 20 long-running API or SQL calls in memory without influencing the logging behavior. If the list of long running API or SQL calls already has 20 entries that have elapsed the time, more entries qualifying the same criteria are not saved. However, all long-running API and SQL calls are logged.
3	Collect logs	If the RLS autodiscovery form is not available or the arexception.log file does not show the expected logs, try to reproduce the issue by enabling AR System server SQL and API logs. Make sure that multiple users, including the AR System administrator, are performing this task.
4	Analyze logs	Use the AR Log Analyzer tool to identify the RLS enabled SQL queries that have performance issues. For information about the AR Log Analyzer tool, see ARLogAnalyzer Version 3 for ARSystem versions 2x.xx, 19.xx, 18.xx, 9.1.xx on the BMC Community site. The following screenshot shows the list of 50 longest running SQL statements:
5	Identify RLS queries	The RLS algorithm varies depending on the use. Review queries having performance issues. Observe the WHERE clause in the query. See the following examples of different algorithms. RLS query with Combined Likes algorithm WHERE (((T4387.C1000000337 = 'user') OR (T4387.C1000000338 = 'user')) AND ((T4387.C2 = 'user') OR (T4387.C60600 LIKE '%;'user';%') OR (T4387.C60600 LIKE '%;0;%') OR (T4387.C60600 LIKE '%;20032;%') OR (T4387.C60600 LIKE '%;20225;%') OR (T4387.C60600 LIKE '%;13006;%') OR (T4387.C60600 LIKE '%;13007;%') OR (T4387.C60600 LIKE '%;13010;%') OR (T4387.C60600 LIKE '%;1000000463;%') OR (T4387.C60600 LIKE '%;20055;%') OR (T4387.C60600 LIKE '%;20313;%') OR (T4387.C60600 LIKE '%;1000000000;%') OR (T4387.C60600 LIKE '%;20315;%') OR (T4387.C60600 LIKE '%;20316;%') OR (T4387.C60600 LIKE '%;20061;%') OR RLS query with RLS Split algorithm WHERE ((T1436.C2 = 'user') OR (T1436.C4 = 'user') OR EXISTS(SELECT 1 FROM(SELECT Item FROM RLS_split(T1436.C60901, ';') UNION ALL SELECT Item FROM RLS_split(T1436.C112, ';') UNION ALL SELECT Item FROM RLS_split(T1436.C60989, ';') UNION ALL SELECT Item FROM RLS_split(T1436.C60900, ';'))privs join RLS_split '1000000023;1000000024;1000000021;1000000027;804;0;1058;20078;20055;20077;20032;', ';') u ON privs.Item = u.Item)))... or ...WHERE ((T3610.C2 = 'user') OR (T3610.C4 = 'user') OR (EXISTS (SELECT TO_CHAR(item) FROM (SELECT regexp_substr(str, '[^;]+', 1, rownum) item FROM (SELECT '''user'';0;-20225;-20213;-20000;440;803;804;1058;13006;13007;13010;20000;20003;20031;20032' str FROM dual) CONNECT BY level <= length (regexp_replace (str, '[^;]+')) + 1) u_privs WHERE item IS NOT NULL INTERSECT ( SELECT TO_CHAR(regexp_substr (T3610.C112, '[^;]+', 1, rownum)) item FROM dual CONNECT BY level <= length (regexp_replace (T3610.C112, '[^;]+')) + 1) ) OR... RLS query with Subquery algorithm WHERE C7 = 1 AND EXISTS(select 1 from S560 where entryId = T560.C1 AND labelId in (112, 60900) and labelValue in ('user', '0', '20042', '20032'))

Step 2 — Confirm that RLS is causing the performance issue

Perform the following steps to analyze whether the RLS algorithm is significant in determining the performance:

Step	Task	Description
1	Perform the test as an administrator	Administrators have access to all records and no RLS algorithm is set for the queries.
2	Test as a user who is a member of fewer AR System groups	When a user is a member of fewer AR System groups, the RLS query is less complex and fetches results faster. However, the design of an application might make it difficult to test, because the functionality varies depending on the group membership.
3	Run an RLS enabled SQL query and an SQL query without enabling RLS	You can run the RLS enabled and RLS not enabled queries by using a suitable SQL client or JDBC Query Test tool. This helps to compare and understand the impact of RLS algorithm. For more information, see How can I test a DB Query using JDBC outside of ARServer? article on the BMC Community site.

Step 3 — Analyze the RLS term

If the RLS enabled and RLS not enabled queries take the same time, consider other parts of the query qualification. In this case, we recommend using other SQL tuning methods.

Step	Task	Description
1	SQL tuning	Review the query with your database support team to see if there are database changes such as index additions or modifications, which might help improve the performance. For more information, see Troubleshooting-SQL-Tuning-issues.
2	Data cardinality and selectivity	Review the non-RLS section of the query qualification. This helps to determine a way to modify the fields used or referenced. This helps in reducing the number of records for which you plan to apply the RLS algorithm.
3	Analyze the application configuration	Queries that use join forms or database joins such as the Overview Console might be improved by changing the application or user preferences. You can reduce the number of forms that are queried or apply other criteria that might help improve the query performance. Watch the following webinar (22:46) to learn more about Overview Console Performance Considerations:
4	Analyze data management and archiving	If the form you are querying has outdated or unnecessary records, consider deleting or archiving some records to reduce the number of records to be searched.

Step 4 — Identify an alternate RLS option

If the RLS algorithm is the cause of the performance issue, we recommend testing and evaluating other available RLS algorithms to improve the performance.

Step

Task

Description

Server level RLS algorithm

At the server level, you can set either the RLS Split algorithm or the Combined Likes algorithm. The value of the algorithm is determined by the Disable-New-RLS-Implementation parameter in Centralized Configuration.

Disable-New-RLS-Implementation	Effective RLS Option
F (default)	RLS_Split/regex
T	Combined LIKEs

Use the AR System Configuration Generic UI form to change the setting of the Disable-New-RLS-Implementation parameter. You can set this parameter at the global as well as at the local level. If you change the value of the Disable-New-RLS-Implementation parameter, you need not restart the AR System server.

The following screenshot shows the Disable-New-RLS-Implementation parameter on the AR System Configuration Generic UI form:

Form level RLS algorithm

You can set the RLS algorithm at an individual form level. When the RLS algorithm is set at the form level, you can use the Subquery algorithm.

To change the RLS algorithm in Developer Studio, you must create an overlay of the form.

Create an overlay for the out-of-the-box form. You need not create overlay for the custom form.

The following screenshot shows the Subquery algorithm available for a form:

When you are using the RLS split algorithm, you can use the Field Order for Qualification list to specify how RLS fields are ordered in queries.

By default, fields are ordered by increasing field ID. However, this might not give the expected performance. You can add fields to the Field ID Order for Qualification table and run queries in the order.

See the following example:

Setting	Query RLS component
No field order specified	... AND EXISTS(SELECT 1 FROM(SELECT Item FROM RLS_split (T2803.C60005, ';') UNION ALL SELECT Item FROM RLS_split (T2803.C60012, ';') UNION ALL SELECT Item FROM RLS_split (T2803.C60020, ';') ...
Custom field order	... AND EXISTS(SELECT 1 FROM(SELECT Item FROM RLS_split (T2803.C60012, ';') UNION ALL SELECT Item FROM RLS_split (T2803.C60020, ';') UNION ALL SELECT Item FROM RLS_split (T2803.C60005, ';') ...

Test to determine the optimal ordering.

Effects of enabling the Subquery algorithm

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

Note that data continues to be populated in the original permission fields on the form so that the change may be reverted quickly. This helps if you later disable the Subquery algorithm.

The time taken to populate the S table varies depending on the complexity and volume of data in the form. Use the RLS Migration Pending form to monitor the progress of the S table population.

The following screenshot show the RLS Migration Pending form:

The following table shows some sample timings of S table population on different test systems:

Evaluate the impact of the change

When you set a new RLS algorithm, repeat the test and analyze the performance.

You can predict how the RLS algorithm performs because it largely depends on the volume and type of permissions in your system.

For more information about examples of the Subquery algorithm, see Massive performance benefit for your use cases in 19.02+on the BMC Community site.

Availability of the Subquery algorithm with AR System server

The Subquery algorithm is available from AR System version 19.02 and later. From version 19.08 and later, you can set the Subquery algorithm for a form by using Developer Studio. Contact BMC Support to enable the Subquery algorithm for version 19.02.

You can identify long running SQL queries by using the AR System:RLS Autodiscovery form. This form records the details of a slow RLS query. However, it does not include the ability to use the Subquery algorithm.

The following table shows the availability of the AR System:RLS Autodiscovery form across versions:

For more information, see: