13 Applying Data Access Security to Repository Objects

Where Do I Find Information About Security Tasks?

Oracle Business Intelligence security tasks are covered in this guide, in Oracle Fusion Middleware Security Guide for Oracle Business Intelligence Enterprise Edition, and in other sources. Table 13-1 summarizes the Oracle Business Intelligence security tasks and where to go for more information.

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.

Figure 13-1 illustrates 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.

Figure 13-1 Row-Level Security Enforcement in the Oracle BI Server

Description of "Figure 13-1 Row-Level Security Enforcement in the Oracle BI Server"

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 Security 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. Table 13-2 lists and describes the other buttons and options.

    Table 13-2 Data Filters Tab: 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 Security Manager.

Setting Up Row-Level Security in the Database

To set up Oracle Business Intelligence for row-level security that has been implemented in the database, 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.

Note that the row-level database security described in this section is different from database authentication, a topic discussed in Oracle Fusion Middleware 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.

Figure 13-2 illustrates 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.

Figure 13-2 Row-Level Security Enforcement in the Database

Description of "Figure 13-2 Row-Level Security Enforcement in the Database"

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.

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. Note that the :USER and :PASSWORD syntax does not refer to session variables.

   Figure 13-3 shows the General tab of the Connection Pool dialog.

   Figure 13-3 Entering Credentials for Database-Level Security in the Connection Pool

   
   Description of "Figure 13-3 Entering Credentials for Database-Level Security in the Connection Pool"
   
   

   Note:

   Alternatively, 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. Note that 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 still need to 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.

About Permission Inheritance for Users and Application Roles

Users can have explicitly granted permissions. They can also have permissions granted through membership in application roles, that in turn can have permissions granted through membership in other application roles, and so on. Permissions granted explicitly to a user have precedence over permissions granted through application roles, and permissions granted explicitly to the application role take precedence over any permissions granted through other application roles.

If there are multiple application roles acting on a user or application role at the same level with conflicting security attributes, the user or application role is granted the least restrictive security attribute. Any explicit permissions acting on a user take precedence over any permissions on the same objects granted to that user through application roles.

Filter definitions, however, are always inherited. For example, if User1 is a member of Role1 and Role2, and Role1 includes a filter definition but Role2 does not, the user inherits the filter definition defined in Role1.

Note that you should always define object permissions for application roles rather than for individual users.

Figure 13-5 User Permissions and Application Role Permissions

Description of "Figure 13-5 User Permissions and Application Role Permissions"

Figure 13-6 Permissions Example

Description of "Figure 13-6 Permissions Example"

These are the resulting permissions:

• User1 is a direct member of Role1 and Role2, and is an indirect member of Role3, Role4, and Role5.

• Because Role5 is at a lower level of precedence than Role2, its denial of access to TableA is overridden by the READ permission granted through Role2. The result is that Role2 provides READ permission on TableA.

• The resultant permissions from Role1 are NO ACCESS for TableA, READ for TableB, and READ for TableC.

• Because Role1 and Role2 have the same level of precedence and because the permissions in each cancel the other out (Role1 denies access to TableA, Role2 allows access to TableA), the less restrictive level is inherited by User1. In other words, User1 has READ access to TableA.

• The total permissions granted to User1 are READ access for TableA, TableB, and TableC.

Accessing the Query Limits Functionality in the Administration Tool

Follow the steps in this section to access the Query Limits tab of the User/Application Role Permissions dialog.

To access the query limits functionality in the Administration Tool for a particular application role:

1. Open your repository in the Administration Tool.

2. Select Manage, then select Identity.

3. In the Security 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 query limits.

   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 Query Limits tab.

Limiting Queries By the Number of Rows Received

You can control runaway queries by limiting queries to a specific number of rows.

To limit queries by the number of rows received:

1. Follow the steps in "Accessing the Query Limits Functionality in the Administration Tool" to access the Query Limits tab.

2. In the Max Rows column, type the maximum number of rows for users to retrieve from each source database object.

3. In the Status Max Rows field, select one of the following options for each database:

   • Enable: This limits the number of rows to the value specified. If the number of rows exceeds the Max Rows value, the query is terminated.

   • Disable: Disables any limits set in the Max Rows field.

   • Warn: Does not enforce limits, but logs queries that exceed the set limit in the Query log.

   • Ignore: Limits are inherited from the parent application role. If there is no row limit to inherit, no limit is enforced.

4. Click OK, then click OK again to return to the Security Manager.

Limiting Queries By Maximum Run Time and Restricting to Particular Time Periods

You can forbid queries during certain time periods, or you can specify the maximum time a query can run on a database.

If you do not select a particular time period, access rights remain unchanged. If you allow or disallow access explicitly in one or more application roles, users are granted the least restrictive access for the defined time periods. For example, if a user is a member of an application role that is explicitly allowed access all day on Mondays, but that user also belongs to another application role that is disallowed access during all hours of every day, then the user has access on Mondays only.

To limit queries by maximum run time, or restrict queries to particular time periods:

1. Follow the steps in "Accessing the Query Limits Functionality in the Administration Tool" to access the Query Limits tab.

2. To specify the maximum time a query can run on a database, in the Max Time (Minutes) column, enter the maximum number of minutes you want queries to run on each database object. Then, in the Status Max Time field, select one of the following options for each database:

   • Enable: This limits the time to the value specified.

   • Disable: Disables any limits set in the Max Time field.

   • Warn: Does not enforce limits, but logs queries that exceed the set time limit in the Query log.

   • Ignore: Limits are inherited from the parent application role. If there is no time limit to inherit, no limit is enforced.

3. To restrict access to a database during particular time periods, in the Restrict column, click the Ellipsis button. Then, in the Restrictions dialog, perform the following steps:

   1. To select a time period, click the start time and drag to the end time.

   2. To explicitly grant access, click Allow.

   3. To explicitly deny access, click Disallow.

   4. Click OK.

4. Click OK, then click OK again to return to the Security Manager.

Allowing or Disallowing Direct Database Requests

You can allow or disallow the ability to execute direct database requests for a particular application role. For the selected role, this privilege overrides the property Allow direct database requests by default for the database object in the Physical layer.

To set the ability to execute direct database requests:

1. Follow the steps in "Accessing the Query Limits Functionality in the Administration Tool" to access the Query Limits tab.

2. For each database object, in the Execute Direct Database Requests field, select one of the following options:

   • Allow: Explicitly grants the ability to execute direct database requests for this database.

   • Disallow: Explicitly denies the ability to execute direct database requests for this database.

   • Ignore: Limits are inherited from the parent application role. If there is no limit to inherit, then direct database requests are allowed or disallowed based on the property Allow direct database requests by default for the database object.

3. Click OK, then click OK again to return to the Security Manager.

Allowing or Disallowing the Populate Privilege

When a criteria block is cached, the Populate stored procedure writes the Cache/Saved Result Set value to the database. You can grant or deny this privilege to particular application roles. For the selected application role, this privilege overrides the property Allow populate queries by default for the database object in the Physical layer.

Any Oracle Marketing Segmentation user who writes a cache entry or saves a result set must be a member of an application role that has been assigned the POPULATE privilege for the target database. For more information about marketing cache, see the topic about setting up cache for target levels in the documentation for the Oracle Marketing Segmentation application.

To allow or disallow the Populate privilege:

1. Follow the steps in "Accessing the Query Limits Functionality in the Administration Tool" to access the Query Limits tab.

2. For each database object, in the Populate Privilege field, select one of the following options:

   • Allow: Explicitly grants the Populate privilege for this database. For all Marketing data warehouses, select Allow.

   • Disallow: Explicitly denies the Populate privilege for this database.

   • Ignore: Limits are inherited from the parent application role. If there is no limit to inherit, then the Populate privilege is allowed or disallowed based on the property Allow populate queries by default for the database object.

3. Click OK, then click OK again to return to the Security Manager.

Setting Up Placeholder Application Roles for Offline Repository Development

Application roles are created and managed in the policy store using the Oracle WebLogic Administration Console and Fusion Middleware Control. These application roles are displayed in the Administration Tool in online mode so that you can use them to set data filters, object permissions, and query limits for particular roles. The application roles in the policy store are retrieved by the Oracle BI Server when it starts.

In some cases, you may want to proceed with setting up data access security in your repository for application roles that have not yet been defined in the policy store. You can do this by creating placeholder application roles in the Administration Tool, then proceeding with setting up data access security in the repository.

If you create placeholder application roles in the Administration Tool, you must eventually add them to the policy store. Run the Consistency Checker to identify application roles that have been defined in the Administration Tool, but that have not yet been added to the policy store. Be sure to use the same name in the policy store that you used for the placeholder role in the Administration Tool.

To create placeholder application roles in the Administration Tool:

1. Open your repository in the Administration Tool.

2. Select Manage, then select Identity.

3. In the Security Manager dialog, select Action > New > Application Role.

4. In the Application Role dialog, provide the following information:

   • Name: Provide a name for the role.

   • Description: Optionally, provide a description of this application role.

   • Members: Use the Add and Remove buttons to add or remove users and other application roles as appropriate.

   • Permissions: Set object permissions, data filters, and query limits for this application role as appropriate. Refer to the other sections in this chapter for detailed information.

5. Click OK to return to the Security Manager.

To check for application roles that need to be added to the policy store:

1. Open your repository in online mode in the Administration Tool.

2. Select File, then select Check Global Consistency.

3. Note any entries related to application roles, then add the appropriate roles to the policy store as appropriate. See Oracle Fusion Middleware Security Guide for Oracle Business Intelligence Enterprise Edition for information about adding application roles to the policy store.

4. Optionally, select individual rows and click Copy to copy the entries to a text file.

Alternatively, you can check an individual application role by right-clicking the application role in the Security Manager dialog and then selecting Check Consistency.