Work With Row-Level Security

This topic provides information to help you understand and define semantic model row-level security.

Topics:

About Row-Level Security

Some data sources apply row-level security policies to determine what data can be queried by an individual user.

Data security is described using various terms such as row-level security, data-level security, or Virtual Private Database (VPD) policies. This document uses the term row-level security.

Some data sources support connections using a privileged user that can impersonate the end user running a query. Connection pools allow parameterization of connection string information, and on-connection and on-query scripts that run prior to data queries. When Oracle Analytics connects to a data source by using a privileged user that can impersonate the actual end user, the data source’s data security policies apply to the end user queries.

In addition to the connection string and query script configuration, Oracle Analytics provides a Virtual Private Database (VPD) data source property for each database in the semantic model's physical layer. When you enable the Virtual Private Database (VPD) option, you can prevent sharing of query cache between users because each user needs to retrieve only the data they are permitted to query.

You must define the users, permissions, and security policies in the database. Refer to your database documentation for more information.

You can use a connection script to achieve the same row-level security for Oracle Database data sources.

Where to Set Up Row-Level Security

You can set up row-level security in the semantic model or in the database.

Implementing row-level security in the semantic model provides benefits such as:

All users share the same database connection pool for better performance.
All users share cache for better performance.
Security rules can be defined and maintained to apply across many federated data sources.

Implementing row-level security in the database is good for situations where multiple applications share the same database. When you design and implement row-level security in the database, you should also define and apply object permissions in the semantic model.

Although it's possible to set up row-level security in both the semantic model and in the database, you typically don't enforce row-level security in both places unless there is a specific need to do so.

Set Up Row-Level Security in the Database

Implement row-level security in the database when multiple applications share the same database.

If you configured the database to use the Virtual Private Database (VPD) feature, then perform this task to make database queries through a semantic model.

Selecting the Virtual Private Database field in the physical database's Advanced properties ensures that the Oracle Analytics query engine protects cache entries for each user. Oracle Analytics query engine matches a list of security-sensitive variables to each prospective cache hit. Cache hits would only occur on cache entries that included and matched all security-sensitive variables.

After you set up row-level security in the database, you can set up object permissions in the semantic model for the presentation layer or other objects. You can also set query limits (governors). See Set Up Presentation Object Permissions and Limit the Number of Rows in a Database Query .

On the Home page, click Navigator and then click Semantic Models.
In the Semantic Models page, click a semantic model to open it.
Click Physical Layer, and then double-click the database you want to edit.
Click the Advanced tab.
In Data Source Properties, select Virtual Private Database.
Click Save.

About Data Filters and Row-Level Security

Define data filters on semantic model objects for specific application roles.

Typically you don't set up data filters if you've implemented row-level security in the database. Row-level security policies are enforced by the database and not by Oracle Analytics.

You can set data filters for objects in the logical layer and the presentation layer. Applying a filter on a logical object impacts all presentation layer objects that use the object. If you set a filter on a presentation layer object, it's applied to the object along with any other filters that are set on the underlying logical objects.

The image shows how data filter rules are enforced in the Oracle Analytics query engine. The security rules are applied to all incoming clients and can't be breached, even when the Logical SQL query is modified.

In this example, a filter has been applied to an application role. When Anne Green, who is a member of that role, sends a request, the return results are limited based on the filter. Because no filters have been applied to the application roles for the Administrator user, all results are returned. The Oracle Analytics query engine-generated SQL takes into account any data filters that have been defined.

Description of the illustration data_sec_1.gif

Set Up Data Filters in the Semantic Model

You can assign data filters for specific application roles to enforce row-level security rules in the semantic model.

To create filters, you select objects from subject areas where you want to apply the filters and then you provide the filter expression information for the individual objects. For example, you might want to define a filter like "Sample Sales"."D2 Market"."M00 Mkt Key" > 5 to restrict results based on a range of values for another column in the table.

You can also use semantic model and session variables in filter definitions.

When a semantic model object such as a logical fact table is accessed by multiple application roles with different levels of access, you can create functional groups to prevent application roles from viewing data restricted from view by that specific application role.

For example, suppose you want your regional sales associates to see the revenue for a quarter in their assigned region, but to avoid exposing sensitive information you want to prevent your regional sales associates to see to total segment sales for all of the regions, In this scenario you create functional groups with different levels of access as appropriate for the specific application role to the filter. See Specify a Functional Group for a Data Filter's Application Role.

On the Home page, click Navigator and then click Semantic Models.
In the Semantic Models page, click a semantic model to open it.
Click Logical Layer or Presentation Layer and double-click the table where you want to set up data filters.
Click the Data Filters tab.
In Add, search for and select the application role that you want to set the data filter for.
Click Open Expression Editor.
In the Expression Editor, define the condition using the semantic model objects and operators.
Click Save.

About Specifying Functional Groups for Application Roles in Data Filters

When a semantic model object such as a logical fact table is accessed by multiple application roles with different levels of access, you can specify functional groups to prevent application roles from viewing data restricted from view by that specific application role.

When there are no functional groups defined, all the security filters applied to a given table, regardless of the associated role, and are combined using the OR operator. Using the OR operator works in most cases because a role can view a union of all the rows selected by the security filters. For example, consider the following filters:

Role A is assigned the filter, Product = 'Camera'

Role B is assigned the filter, Product = 'Monitor'

If an application role is given Role A and Role B, then the role can view data for both the Camera and Monitor products.

When the two security filters from the same table are combined in the query, the filter conditions are combined using the OR operator, this is appropriate for most security filters defined on dimension tables, for example:

Product = 'Camera' OR Product = 'Monitor'

Using functional groups is necessary when securing a single fact table, using data filters from different dimensions.

In this example, a fact table is secured using the following filters:

Role A is assigned the filter, Product = 'Camera'

Role B is assigned the filter, Product = 'Monitor'

Role C is assigned the filter, Region = 'Southwest'

If you don't use functional groups, a user with roles A, B, and C would have all three filter conditions combined in the query using the OR operator, for example:

(Product = 'Camera' OR Product = 'Monitor' OR Region = 'Southwest')

Combining the results of Role A, B, and C doesn't make sense because Product and Region are independent dimensions. Combining data filters from different dimensions using OR operator provides the application roles access to more data values than the roles should view.

In this example, the application role can see data for all products within the Southwest region as well as data for all regions within the Camera and Monitor products.

To get the expected behavior, that is allowing the application role to see data only for the Camera and Monitor products within the Southwest region, you need to change the filter to combine the product filters with the region filter using the AND operator, for example:

(Product = 'Camera' OR Product = 'Monitor') AND (Region = 'Southwest')

To achieve this using functional groups, assign the security filters to functional groups as follows:

Role A is assigned the filter, Product = 'Camera' with functional group "Product"

Role B is assigned the filter, Product = 'Monitor' with functional group "Product"

Role C is assigned the filter, Region = 'Southwest' with functional group "Region"

All the filters in the same functional group are combined using the OR operator and all sets of filters in different functional groups are combined using the AND operator. By choosing the functional groups associated with each security filter, you can control how the filters are combined using the OR and AND operators.

Specify a Functional Group for a Data Filter's Application Role

You can specify a functional group for an application role with different data access filters on the same semantic model object, usually a logical fact table.

See About Specifying Functional Groups for Application Roles in Data Filters and Set Up Data Filters in the Semantic Model.

On the Home page, click Navigator and then click Semantic Models.
In the Semantic Models page, click a semantic model to open it.
Click Logical Layer or Presentation Layer and double-click the table containing the data filter that you want to specify a functional group for.
Click the Data Filters tab.
Select the filter that you want to specify the functional group for.
Click the Functional Group field and select an existing group or enter the name of a new group.
Click Save.