Setting Up Row-Level Security

You can choose to set up row-level security in the repository, or in the database.

Implementing row-level security in the repository provides many benefits, including the following:

• All users share the same database connection pool for better performance

• All users share cache for better performance

• You can define and maintain security rules that apply across many federated data sources

Implementing row-level security in the database, in contrast, is good for situations where multiple applications share the same database. Note that even when you design and implement row-level security in the database, you should still define and apply object permissions in the repository.

Although it is possible to set up row-level security in both the repository and in the database, you typically do not enforce row-level security in both places unless you have a particular need to do so.

This section contains the following topics:

• Setting Up Row-Level Security (Data Filters) in the Repository

• Setting Up Row-Level Security in the Database

Setting Up Row-Level Security (Data Filters) in the Repository

Data filters are a security feature that provide a way to enforce row-level security rules in the repository.

Data filters are set up in the repository using the Administration Tool and are applied for a particular application role.

You typically do not set up data filters if you have implemented row-level security in the database, because in this case, your row-level security policies are being enforced by the database rather than the Oracle BI Server.

Data filters can be set for objects in both the Business Model and Mapping 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 is applied in addition to any filters that might be set on the underlying logical objects.

The image shows how data filter rules are enforced in the Oracle BI Server. The security rules are applied to all incoming clients and cannot 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 BI Server-generated SQL takes into account any data filters that have been defined.

Description of the illustration GUID-5BF2A238-B5D9-4C0A-9228-EE2702956AA1-default.gif

You should always set up data filters for particular application roles rather than for individual users.

To set up data filters to apply row-level authorization rules for queries:

1. Open your repository in the Administration Tool.
2. Select Manage, then select Identity.
3. In the Identity Manager dialog, in the tree pane, select BI Repository.
4. In the right pane, select the Application Roles tab, then double-click the application role for which you want to set data filters.

   Note that if you are in offline mode, no application roles appear in the list unless you have first modified them in online mode. See About Applying Data Access Security in Offline Mode for more information.

5. In the Application Role dialog, click Permissions.
6. In the User/Application Role Permissions dialog, click the Data Filters tab.

   To create filters, you first add objects on which you want to apply the filters. Then, you provide the filter expression information for the individual objects.

7. To add objects on which you want to apply filters, perform one of the following steps:

   • Click the Add button. Then, browse to locate the object you want, select it, and then click Select.

   • Click the Name field for an empty row. Then, browse to locate the object you want, select it, and then click Select.

8. To enter the filter expression for individual objects, perform one of the following steps:

   • Select the data filter, then click the Expression Builder button. Create the filter expression in Expression Builder, then click OK.

   • Click the Data Filter field for the appropriate filter, then type the filter expression.

   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 repository and session variables in filter definitions. Use Expression Builder to include these variables to ensure the correct syntax.

9. Optionally, select a status for each filter from the Status list. You can choose one of the following options:

   • Enabled: The filter is applied to any query that accesses the object.

   • Disabled: The filter is not used and no other filters applied to the object at higher levels of precedence, for example, through an application role, are used.

   • Ignored: The filter is not in use, but any other filters applied to the object, for example, through a different application role, are used. If no other filters are enabled, no filtering occurs.

10. In addition to defining new filters, you can perform other operations in the Data Filters tab. The table lists and describes the other buttons and options.

    Option Name	Description
    Subject Area	Select a subject area to only view data filters for that individual subject area, or select All to view all filters.
    Total Filters	Lists the total number of data filters that have been defined for this particular user or application role.
    Add	Click Add to open the Browse dialog to add objects on which you want to apply data filters.
    Delete	Select a row and click Delete to remove a filter.
    Browse	Select a row and click Browse to change the object on which the filter is applied.
    Edit Expression (Expression Builder)	Select a row and click Edit Expression to add or change a filter expression for a particular object. You must first add an object before you can apply a filter expression to the row.
    Find	Enter text in the Find field and click Find Down or Find Up to find a particular string.

11. Click OK, then click OK again to return to the Identity Manager.

Setting Up Row-Level Security in the Database

You must implement row-level security in the database to set up Oracle Business Intelligence. You can configure your connection pools so that the Oracle BI Server passes the credentials for each user to the database.

The database then uses the credentials to apply its own row-level security rules to user queries.

Row-level database security is different from database authentication. See About Authentication in the Security Guide for Oracle Business Intelligence Enterprise Edition. Rather, row-level database security provides database authorization. In other words, it applies access security to particular rows in the database.

The image shows how row-level security is enforced in the database for Oracle Business Intelligence queries. The security rules are applied to all incoming clients and cannot be breached, even when the Logical SQL query is modified. In this example, the results returned are different depending on which user generated the query, even though the SQL query generated by the Oracle BI Server is the same. The returned results are based on rules created and enforced in the database.

Description of the illustration GUID-5527A825-70A7-48FA-96A2-A7EA2391C567-default.gif

In addition to setting up Oracle Business Intelligence for row-level security in the database, you must define your set of users, permissions, and security policies in the database itself. Refer to your database documentation for more information.

Consider the following about this configuration:

• Row-level security does not work when SSO is used, or for any cases that involve impersonation such as Delivers, because the password for the end user is not available to the Oracle BI Server.

• A connection script can be used to achieve the same functionality for Oracle Database data sources.

• For Essbase or Hyperion Financial Management data sources, the connection pool displays an additional option to implement SSO.

To set up Oracle Business Intelligence for row-level access security in the database:

1. Open your repository in the Administration Tool.
2. Double-click the connection pool associated with the database for which you want to set up database-level security.
3. In the General tab of the Connection Pool dialog, select Shared logon, and then enter :USER and :PASSWORD in the User name and Password fields. The :USER and :PASSWORD syntax automatically passes the value of user credentials upon login to the database. The :USER and :PASSWORD syntax does not refer to session variables.

   Note:

   You can use the database session context to pass end user identity to the database. Use a connection pool script to set up session context. This approach does not rely on database authentication.

4. Click OK in the Connection Pool dialog.
5. Double-click the database object for which you want to set up database-level security.
6. In the Database dialog, select Virtual Private Database. Selecting this option ensures that the Oracle BI Server protects cache entries for each user.
7. Click OK in the Database dialog.

After you have set up row-level security in the database, you must set up object permissions in the repository for Presentation layer or other objects. You can also set query limits (governors). See Setting Up Object Permissions and Setting Query Limits for more information.