Best Practices For Implementing Row-Level Security In Oracle Analytics

It’s intended for Model Administrators who are using row-level security filters and covers row-level security which has “or logic” and uses empty values when a part of the filter is not applicable. Many businesses have security constraints involving different dimensions for different groups of users and for combinations of them. The simplest implementation will apply filters for all possibilities and use an empty value for dimensions that do not apply to the specific user. The correct results are obtained, but the SQL contains extra tables that are not used, which can negatively impact performance. This post explains how to get Oracle Analytics to dynamically exclude these tables.

Row-Level Security Example

There are a couple of parts to setting up table elimination for row-level security. Set up a series of session variables that determine first, when to apply a security filter and second, which security filters to apply.

First, set up a series of control session variables that determine whether security should be applied. The session variables are binary and are set to either 1 or 0. 1 means the data is restricted and to use the security session variables; 0 means the data is unrestricted and ignores the security session variables.

This example describes setting up security on 4 entities: business hierarchy, country, department, and legal organization.

There are 5 control session variables set up, one for turning on security in general, and one to turn on security for each of the entities.

Control variables:

1. NQ_SESSION.Data_Security_Restricted – turns security on or off.
2. NQ_SESSION.Data_Sec_Control_BH – turns security on or off for the business hierarchy entity.
3. NQ_SESSION.Data_Sec_Control_Country – turns security on or off for the country entity.
4. NQ_SESSION.Data_Sec_Control_Department – turns security on or off for the department entity.
5. NQ_SESSION.Data_Sec_Control_Legal_Org – turns security on or off for the legal organization entity.

After that, set up security session variables for each entity that contains restricted data. These variables contain the values of the security filters to be passed to report filters.

Security filter variables:

1. NQ_SESSION.Data_Security_BH – sets the security filter for the business hierarchy entity.
2. NQ_SESSION.Data_Security_Country – sets the security filter for the country entity.
3. NQ_SESSION.Data_Security_Department – sets the security filter for the department entity.
4. NQ_SESSION.Data_Security_Legal_Org – sets the security filter for the legal organization entity.

You will want to pay attention to the underlying datatypes, as they determine the format of the value of the session variable. In this case, business hierarchy and legal organization are integers, while country and department are character strings. The value of the session variable is the actual value of the entity to be passed to the filter.

To help understand how the control variables and the indexcol function work, let's review an example in which security is set only for department, and the control variables and indexcol function are not used. The row-level filter is shown below:

"Core"."Dim - Business Hierarchy"."Business Hierarchy Identifier" = VALUEOF(NQ_SESSION."Data_Security_BH")

OR "Core"."Dim - Country"."Country Name" = VALUEOF(NQ_SESSION."Data_Security_Country")

OR "Core"."Dim - Legal Org"."Legal Org Identifier" = VALUEOF(NQ_SESSION."Data_Security_Legal_Org")

OR "Core"."Dim - Department"."Department Name" = VALUEOF(NQ_SESSION."Data_Security_Department")

The session variables are populated with the list of values the user can access and, if there is no restriction, then the “empty” placeholder value is used.

When applied to the sample report below, the security filter is: (with Data_Security_Department = ‘Dept 1’, Data_Security_Country = ‘No Values’, Data_Security_Legal_Org = -1, Data_Security_Department = -1 )

SELECT

   0 s_0,

   "Jobs Analysis"."Grade"."Grade Name" s_1,

   "Jobs Analysis"."Time"."Month Name" s_2,

   "Jobs Analysis"."Time"."Year" s_3,

   DESCRIPTOR_IDOF("Jobs Analysis"."Time"."Month Name") s_4,

   SORTKEY("Jobs Analysis"."Time"."Month Name") s_5,

   "Jobs Analysis"."Facts - Job Counts"."Jobs" s_6

FROM "Jobs Analysis"

WHERE

(("Time"."Year" = 2023) AND ("Time"."Month Name" = 'July'))

ORDER BY 4 ASC NULLS LAST, 6 ASC NULLS LAST, 3 ASC NULLS LAST, 5 ASC NULLS LAST, 2 ASC NULLS LAST

FETCH FIRST 500001 ROWS ONLY

Here is the SQL generated by the report:

In the sample above, the extra SQL is highlighted. There are 3 tables, DW_BUSINESS_DF_DH, DW_LEGAL_EMPLOYER_D and DW_COUNTRY_D_TL, that are used in the security filter but are not filtering anything. Depending upon the scope of the data in each of the additional tables, the query execution time is going to be higher than if those tables were not included.

To get those tables eliminated from the query, replace the row-level filter from above with this one:

INDEXCOL( VALUEOF(NQ_SESSION."Data_Security_Restricted"), '0', '1') = '0' OR  INDEXCOL( VALUEOF(NQ_SESSION."Data_Sec_Control_BH"), '0',  VALUEOF(NQ_SESSION."Data_Security_BH")) =  INDEXCOL( VALUEOF(NQ_SESSION."Data_Sec_Control_BH"), '1', "Core"."Dim - Business Hierarchy"."Business Hierarchy Identifier") OR  INDEXCOL( VALUEOF(NQ_SESSION."Data_Sec_Control_Country"), '0',  VALUEOF(NQ_SESSION."Data_Security_Country")) =  INDEXCOL( VALUEOF(NQ_SESSION."Data_Sec_Control_Country"), '1', "Core"."Dim - Country"."Country Name") OR  INDEXCOL( VALUEOF(NQ_SESSION."Data_Sec_Control_Dept"), '0',  VALUEOF(NQ_SESSION."Data_Security_Department")) =  INDEXCOL( VALUEOF(NQ_SESSION."Data_Sec_Control_Dept"), '1', "Core"."Dim - Department"."Department Name") OR  INDEXCOL( VALUEOF(NQ_SESSION."Data_Sec_Control_Legal_Org"), '0',  VALUEOF(NQ_SESSION."Data_Security_Legal_Org")) =  INDEXCOL( VALUEOF(NQ_SESSION."Data_Sec_Control_Legal_Org"), '1', "Core"."Dim - Legal Org"."Legal Org Identifier")

The SQL now generated by the report will have only the needed tables:

The key function to use to get these dynamic security filters is the indexcol function. To set up the indexcol function, use the session variables set up in the example and populated with the entity values.

A control initialization block is set up to determine which filters to apply based on the original control variables.

Control initialization block:

select 'Data_Sec_Control_BH', case when (Instr('valueof(NQ_SESSION.Data_Security_BH)','-1')>0) then 0 else 1 end from dual

union

select 'Data_Sec_Control_Dept', case when (Instr('valueof(NQ_SESSION.Data_Security_Department)','No Values')>0) then 0 else 1 end from dual

union

select 'Data_Sec_Control_Country', case when (Instr('valueof(NQ_SESSION.Data_Security_Country)','No Values')>0) then 0 else 1 end from dual

union

select 'Data_Sec_Control_Legal_Org', case when (Instr('valueof(NQ_SESSION.Data_Security_Legal_Org)','-1')>0) then 0 else 1 end from dual

It is important to set the precedence property on the control initialization block so it is executed after the initialization blocks that populate the security filter variables. In other words, the security filter variables must be populated before they are used in the control initialization block.

Call to Action

Following these steps will help ensure your row-level security formulas have the best possible performance by eliminating unnecessary filters and tables.

Source: oracle.com