Concepts → Runtime Security Filter

About a runtime security filter

A runtime security filter is a filter expression that the Analytics Service applies when a user accesses an in-memory physical schema table, materialized view, Incorta Analyzer Table, or Incorta SQL Table. Typically, the purpose of a runtime security filter is to apply a filter expression that implements Row Level Security (RLS) using a form of Role-Based Access Control (RBAC).

For example, when a user signs in and opens a dashboard with a single KPI insight that counts the number of rows, the Analytics Service processes the query plan for the aggregated measure. Depending on the filter expression for the runtime security filter, one user may receive a count of 17 rows and another user may receive a count of 9,987.

A runtime security filter differs from a Load Filter. A load filter affects how the Analytics Service loads in to memory a given physical schema table or materialized view from shared storage. A runtime security filter affects how the Analytics Service filters rows for a given entity object for a given user query. And unlike a load filter, the Analytics Service immediately applies a newly created runtime security filter without requiring a new load.

There are two types of runtime security filters: Regular and Formula. Often, a filter expression implements Row Level Security (RLS) using a form of Role-Based Access Control (RBAC). You can have multiple runtime security filters for a given entity object.

Note

An Alias inherits the runtime security filter of its selected physical schema table.

About a regular runtime security filter

A regular runtime security filter applies a filter expression. A filter expression contains the following:

  • filterable column
  • filter operator
  • filter value

A regular runtime security filter consists of the following:

  • Table
  • Column
  • Operator
  • Value

Table and column

For a regular runtime security filter, the physical schema is the current physical schema. A table can be the entity object itself or a parent object. If the entity object is a child object with joins to one or more parent objects, you can select the object itself or a parent object as the table.

To learn more about a join, see Concepts → Join.

A column is a filterable column. A filterable column for regular runtime security filter is a data-backed column or a formula column.

Operator

A filter operator defines the operation for the filter expression. Here is a list of the filter operators available for a regular runtime security filter:

Operator Symbol
Less Than <
Less Than or Equals <=
Equals =
Does Not Equal <>
Greater Than >
Greater Than or Equals >=
In in

Value

A value can be one of the following:

In order to select a filter expression session variable, internal session variable, or external session variable, you must have View access rights to the variable. A global variable is available to all users.

Note

A filter expression session variable, internal session variable, and external session variable store a value as a string. Global variables are strongly typed. A filter value must be of the same type as the filterable column or must support a native conversion such as string to integer.

About a formula runtime security filter

A formula runtime security filter evaluates a formula that typically contains a filter expression. A filter expression has a filterable column, a filter operator, and a filter value. A complex formula expression may contain several boolean built-in functions that evaluate multiple filter expressions.

Note

A filter expression is not a requirement of a formula runtime security filter. It is possible to simply specify a formula expression, such as 1=0, and as a result, filter out all rows.

Recommended practice

As a schema developer, it is important to evaluate the refresh rate of the tables in the security related physical schema tables. In your evaluation, consult a Security Administrator in your organization, review your organization’s security policies, and determine a frequency interval that meets your organization’s security needs. For example, consider a security related physical schema that contains a hierarchy of team roles in a self-referential table. Only the supervisor role allows mapped users to view employee personal information. The external data source for the security related physical schema is a human resources application. This application updates every four hours. With that frequency interval in mind, you most likely can schedule a load job for the security related physical schema to run every four hours in order to ensure compliance with the organization’s data access and security governance policies.

Example of a formula runtime security filter

In this example, there are two groups, grp_Sales and grp_Admin. Zero or more users can belong to a group.

There are several users that belong to grp_Sales: user_1, user_2, and user_3. Only one user, admin_1, belongs to grp_Admin. Only users that belong to grp_Sales can view rows from the SALES table where the CUSTOMER_ID is greater than or equal to 10,000. A user that belongs to grp_Admin can view all rows from the SALES table.

There are two external session variables that return the usernames (LOGINNAME is the column name) for all members of each group: evar_getLoginNames_grp_Admin and evar_getLoginNames_grp_Sales. The external data source is a MySQL database for the Incorta Metadata database. The TENANTID is 11 in this example.

The Query for evar_getLoginNames_grp_Admin is:

select t1.LOGINNAME from  GROUP_USER t2
Left outer join `GROUP` t3 on t2.GROUPID = t3.ID
left outer join USER t1 on t2.USERID = t1.ID
where 1=1
and t1.TENANTID=11
and t3.NAME = "grp_Admin"

The Query for evar_getLoginNames_grp_Sales is:

select t1.LOGINNAME from  GROUP_USER t2
Left outer join `GROUP` t3 on t2.GROUPID = t3.ID
left outer join USER t1 on t2.USERID = t1.ID
where 1=1
and t1.TENANTID=11
and t3.NAME = "grp_Sales"
Note

The Analytics Service evaluates all session variables for a user when they sign in to the Analytics Service.

The formula runtime security filter exists on the SALES table in the SALES schema. Here is the formula expression:

or(
	and(
		SALES.SALES1c.CUSTOMER_ID >= 10000,
		inList(
			$user,
			$evar_getLoginNames_grp_Sales
		)
	),
	inList(
		$user,
		$evar_getLoginNames_grp_Admin
	)
)

© Incorta, Inc. All Rights Reserved.