Appendix: EPM Row Level Security Bridge for Oracle Business Intelligence (BI) Discoverer

This Appendix provides an overview of the EPM row level security bridge for Oracle BI Discoverer and discusses how to:

• Set up EPM row level security for Discoverer

• Use Oracle Database links

• Validate the Discoverer/EPM row level security setup

Understanding EPM Row Level Security for Oracle BI Discoverer

The Oracle BI Discoverer tool uses a number of mechanisms to prevent unauthorized access to data from your EPM warehouse and other resources. This section discusses these mechanisms, specifically:

• Oracle BI Discoverer Security

• Access control using Virtual Private Databases (VPD)

• VPD for EPM Row Level Security

PeopleSoft and Oracle Security Prerequisites

The EPM security framework addresses the need to provide row and column level security for data residing in a database table. It provides an additional layer of security in addition to the database table level access privileges. Using this framework, you can restrict certain users from accessing certain rows or columns of data in a table. You define security access on dimension tables, and these same access rules are used while the user accesses fact table data. Users may be granted or denied access to certain rows within a database table. Users may also be permitted to, or restricted from, viewing certain columns within a table.

PeopleSoft assumes that you are using Discoverer with the Multidimensional Warehouse (MDW) and that you have already enabled EPM row level security for the (MDW). You should have defined your security parameters and run the Security Processing Application Engine to generate access data for the security join tables.

Data access privileges within the EPM warehouse are defined on dimension tables. A security administrator defines the security profile for each user based on their access privileges on dimension tables. Using the same Role names as defined here will be your constant across all three (Oracle, PeopleSoft, and Oracle BI Discoverer) security systems

PeopleSoft assumes that the Oracle Discoverer metadata and reports exist on the same physical database as the MDW, or on different databases linked using Oracle database links.

PeopleSoft also assumes you have set up the User Identity Management for Discoverer. User Identity Management is a key component in Oracle Discoverer certification with EPM 9. This certification ensures that a PeopleSoft user is able to log into Oracle Discoverer using their PeopleSoft user name and password. This list is a recap of these steps:

1. Install the Oracle 10g database.

2. Install the Oracle 10g client.

3. Install the Oracle Internet Directory (OID).

4. Register the Oracle 10g Database with OID.

5. Configure PeopleSoft EPM to use the OID for LDAP authentication.

6. Create Enterprise users in the OID.

7. Create global schema and global roles in the database.

8. Create Enterprise roles in the OID.

9. Map Database roles to Enterprise roles in the OID.

10. Map Enterprise roles to users in the OID.

11. Map the shared database schema to Enterprise users on the OID.

12. Connect to Discoverer as an Enterprise user.

See Securing EPM.

See Oracle Database Security Guide 10g Release 2

Oracle BI Discoverer Security

Oracle BI Discoverer uses a number of security mechanisms to prevent unauthorized access to data and other resources. The Oracle database security model is one of the various security models Oracle BI Discoverer respects. Using the database security model, at the most basic level, data in the database is protected from unauthorized access by the database’s own security model. In the case of an Oracle database, this security model is comprised of:

• Database users and roles

• Database Privileges

The database privileges granted directly to database users (or granted indirectly via database roles) determine the data that users can access.

Oracle BI Discoverer uses the database’s own security model to make sure that users don’t see data to which they do not have access at the database level.

Oracle database uses access control via VPD policies for providing row and column level security, which Oracle BI Discoverer respects. Using this model, the EPM security privileges can be translated to the database model, which is then enforced for Oracle BI Discoverer which accesses your Oracle database. Doing this at the database level also enforces row and column level security irrespective of the tool used to log in to the database (e.g. Oracle BI Discoverer, SQLPLUS).

The following sections provide more details about this security model, and how EPM security translates to this model.

See Oracle Business Intelligence Discoverer Administration Guide, “Controlling Access to Information”

Access Control by VPD

Oracle 10g has support for row and column level VPD policies. VPD policies are defined in the database and applied against tables or views. Queries executed against the tables or views trigger the VPD policy. Oracle BI Discoverer executes SQL queries against the database and the VPD policies are applied to its SQL queries. The Oracle VPD framework is used to migrate the EPM row level security rules to Oracle BI Discoverer by defining Oracle VPD policies that understand and enforce EPM security rules.

How Oracle VPD policies work

The Oracle BI Discoverer reporting engine generates SQL queries for each report defined by a user. This SQL query is executed against the database when a user executes a report, and the results of the query are displayed to the user.

A VPD policy function defines the security logic. This policy function generates a filter to restrict user access to data. The function uses two parameters, the table owner and table name, and returns a predicate which is appended to the WHERE clause of the query. The policy function is attached to the secure database objects, and is executed each time the object is accessed.

When there is a VPD policy defined against any of the tables or views used in the query, the WHERE clause generated by Oracle BI Discoverer is modified by the policy function. The policy function generates another WHERE predicate, which is combined with AND for the existing predicate.

Note. The predicates returned by multiple VPD Policies defined on the same table are combined by AND to generate the final VPD predicate, which is applied to the user query.

VPD for EPM Row Level Security

This section discusses how security is established for:

• Dimensions

• Facts

• Metrics

Dimensions

The VPD policies are defined on the secured dimension tables. The policy function returns a WHERE filter which contains logic for joining to the EPM security join table for the dimension that triggered the policy. This ensures that only the rows accessible by the user are returned when the user tries to access the table.

A single shared policy function will be defined and attached to all the dimension tables. In Oracle 10g, a single VPD policy can be applied to multiple objects. A single policy function applied to all the dimension objects will ease the administration overhead.

Note that each time the VPD function is called, it returns a predicate appropriate for that dimension. An Oracle BI Discoverer query typically includes fields from multiple dimension tables. The function will be called once for each dimension referred to by the query. Each time, the function evaluates if the dimension is secured or public. The function queries EPM dimension metadata tables for this information. For a public dimension, the function returns a null predicate. For a secured dimension, the function returns the appropriate filter predicate.

Multiple predicates are joined together using AND clause in the final query executed against the database.

Note. The users will have log out and re-login to Oracle BI Discoverer for any security setting changes to be reflected in the reports they are viewing. (for example, if they are given access to more data, or some access is revoked)

Facts

EPM does not provide a framework to secure rows in a fact table directly. However, row level security defined for dimensions is applied to fact tables. Dimension table primary keys are foreign keys in the fact table. When a user queries a fact table, the security framework tests each row in the fact table against the security access defined for its foreign key dimensions. If the user has access to every secured dimension value in a row of data in the fact table, then and only then the fact row is accessible to the user.

VPD policies are defined on both on the fact tables and the dimension tables. The policy function for the fact table uses information on foreign key relationships. Once the foreign keys are known, the function queries the EPM metadata to see if the fact table is joined to any secured dimension. For each secured dimension, it generates a WHERE filter similar to the one outlined in the section on VPD policies for dimensions. The final filter returned from the VPD function is the combined predicate of all its secured dimensions.

The fact and dimension relationships have to be defined as primary/foreign key constraints in the Oracle database for the fact VPD function to take effect. These are disabled constraints, but can still be used by the VPD functions.

Note. The DropEPMRLPolicies.sql script can be used if you want to remove all EPM fact and dimension VPD policies defined on fact and dimension tables . This might be necessary during troubleshooting and testing.

Metrics

A separate VPD function for secured columns will ensure that only the columns accessible to the user are displayed to them. An additional parameter of sec_relevant_cols tells the database that the policy should be invoked only when the secure columns are part of the SQL clause, which accesses the table.

When you define column Level VPD policies, you have two options of how to display the data

• not display the rows to which the user does not have column access

• display the rows, but have null values in the column

The latter behavior is called column masking. The delivered script for defining metric policies uses the column masking option, but can be changed to either option.

A separate column level VPD function is defined for each secured metric.

Note. The DropEPMMetricPolicies.sql script can be used if you want to remove all EPM fact and dimension VPD policies defined on fact and dimension tables . This might be necessary during troubleshooting and testing.

Setting up EPM Row Level Security for Discoverer

You must ensure the same security settings for the Oracle VPD network and EPM row and column level security are in effect for Discoverer. This section discusses how to:

• Define Primary and Foreign Key relationships between Fact and Dimension tables

• Set up Virtual Private Database (VPD) policies

• Grant Exempt Access

Define Primary and Foreign Key relationships between Fact and Dimension tables

The primary and foreign key relationships have to be defined in the RDBMS for row level security on the MDW to work. We can use the Sales Fact Table, Customer Dimension and the Product Dimension as examples.

The Sales Fact table contains six key fields, two of which are foreign keys. One foreign key is Customer_key which is the Primary key to the Customer Dimension Table. The other foreign key in the Sales Fact table is Product_key, which is the primary key for the Product Dimension table.

Warning! You should disable these referential integrity constraints, which were created with the RELY DISABLE NOVALIDATE option, as they interfere with PeopleSoft Applications if they are enabled. The row level fact VPD function uses the primary and foreign key relationships to enforce security. You need to drop these constraints before rebuilding any of your MDW tables and recreate them after you are finished with your data model changes.

See Multidimensional Warehouse (MDW).

Set up Virtual Private Database (VPD) policies

You create VPD policies using the DBMS_RLS.add_policy function, which has the parameter long_predicate. The delivered default value for this parameter is FALSE. To override this restriction, for example, in the case of a large predicate, you can set this parameter to TRUE, which permits a long_predicate parameter of up to 32 kilobytes.

To setup VPD policies for EPM, you must run the EPMVPDSetup.sql as the PeopleSoft database owner ID. (This is the schema that owns the PeopleSoft database tables.) This ID must have the appropriate privileges to add VPD functions and policies to MDW tables. The PL-SQL script creates the EMPPREDICATES security package and also adds VPD policies for the dimension, fact, and metric tables that are secured.

The OWE Tables require a different script to be run. Use the EPMVPDSetupOWE.sql script to establish the VPD policies for reporting from OWE tables. The fact – dimension relationship in the OWE is based on the dimension metadata alternate keys. Fact tables belonging to the OWE are labeled as such when defined in the record metadata.

If you are securing MDW and OWE tables, run the EPMVPDSetup.sqlscript first, followed by the EPMVPDSetupOWE.sqlscript

See Setting Up Record Metadata.

Note. If you are connected using SYS, or any user connected as SYSDBA, this will bypass the VPD policies. You also have the option of granting the extremely powerful privilege EXEMPT ACCESS POLICY to any DBA, giving it the same bypass ability.

If some of your security settings change, you may have to reconfigure the VPD policies, by rerunning the EPMVPDSetup.sql script, for the new security settings to take effect. Changed security settings may include:

• Securing new dimensions, or making previously secured dimensions public.

• Securing new metrics, or making previously secured metrics public.

• Defining new primary/foreign key relationships between fact and dimension tables.

The following PL_SQL scripts must be run to set up the VPD policies.

Grant Exempt Access

To grant Exempt Policy Access, you must run the GrantExemptDBOwner.sql with the user ID of SYSTEM or SYSDBA. This grants the exempt policy access privilege to the PeopleSoft database owner ID. Doing this exempts the PeopleSoft database owner from VPD policies and ensures those policies are not enforced. This is useful for tasks such as setting up security using PeopleSoft Internet Architecture (PIA) and using the Application Designer tools.

Note. You must modify the GrantExemptDBOwner.sql script if there are multiple PeopleSoft databases installed on the same Oracle instance.

Using Oracle Database Links

This section discusses how to configure your system so that the EPM security privileges are respected when using a distributed database using Oracle database links. This step is not required if you are using a single Oracle database instance.

Typically, the Discoverer End User Layer (EUL) and PeopleSoft EPM 9 reside in separate Oracle 10g instances. In this situation, to have the user identity correctly propagated from the logon database (the Discoverer EUL database) to the PeopleSoft EPM 9 database, you must set up an externally initialized context and a logon trigger to propagate that context.

Setup User Identity Propagation for Oracle Database Links

Run the EPMDBLinkSetup.sql script on the Discoverer EUL database.

This script creates a logon trigger and an externally initialized context to propagate user identity to the linked database.

Validating the Discoverer/EPM Row Level Security Setup

Once you have performed the preceding steps to set up EPM security for Discoverer, you can verify that the setup has been successful. Use the Discoverer Security Validation page to validate that row level security settings have been migrated to the Oracle database, so that Discoverer can use them. You can also use this page for troubleshooting problems that might have occurred.

Pages used to Validate Discoverer Security

Validating Discoverer/EPM Row Level Security - Dimensions

Access the Discoverer Security Validation page and view the MDW Secured Dimensions grid

Use this grid to identify the secured dimensions, OID information, and additional configuration information related to Discoverer EPM row level security

Validating Discoverer/EPM Row Level Security - Facts

Access the Discoverer Security Validation page and view the MDW Secured Facts grid.

Use this grid to identify the secured facts, OID information, and additional configuration information related to Discoverer EPM row level security

Validating Discoverer/EPM Row Level Security - Metrics

Access the Discoverer Security Validation page and view the MDW Secured Metrics grid.

Use this grid to identify the secured metrics, OID information, and additional configuration information related to Discoverer EPM row level security