Oracle^® Business Intelligence Applications Installation and Configuration Guide > Integrated Security for Oracle BI Applications >

Implementing Data-Level Security in the Oracle BI Repository

This topic describes the process of configuring data from the Oracle Business Analytics Warehouse for Siebel (CRM) and Oracle E-Business Suite deployments, and refers only to the Oracle Business Analytics Warehouse tables. The setup for the transactional database data is similar, although the physical schema joins are different.

In this release, the Position based data level security has been modified by introducing a new table (W_POSITION_DH) to replace the old bridge table W_PARTY_LOGIN, which was used to grant access to managers to the records owned by somebody in their organization. The new table W_POSITION_DH is a flattened hierarchy of W_POSITION_D, and it is treated as a slowly changing dimension of type 2.

A new record is created every time the Primary employee for that position or the parent division of that position are modified. Consequently, every Position in the transaction database table (S_POSTN) can be represented by more than one record in this table (identified by W_POSITION_DH.BASE_POSTN_ID=S_POSTN.ROW_ID), but only one record can have the value of CURRENT_FLG as 'Y' at any time.

The W_POSITION_DH table also contains one set of columns prefixed with CURRENT, and another set of columns not prefixed with CURRENT. The columns that are prefixed with CURRENT reflect the current hierarchy structure for BASE_POSTN_ID at any time. The columns that are not prefixed with CURRENT reflect the hierarchy structure for the same position during the period between EFFECTIVE_START_DT and EFFECTIVE_END_DT.

Installed out of the box, all data security filters in Oracle BI Applications are based on the Current hierarchy columns. The flattened hierarchy table supports 10 levels, but it can be easily extended to support more. Employee attributes are included at every level, to allow analysis based on the Employee in parallel with position. The employee considered here is obviously the Primary Employee for that position. Since managers of an employee at different hierarchy levels will be stored in different columns (for example, W_POSITION_DH.CURRENT_LVL1ANC_LOGIN, W_POSITION_DH.CURRENT_LVL2ANC_LOGIN...W_POSITION_DH.CURRENT_TOP_LVL_LOGIN), a different security group exists for every level of the hierarchy. In every group, a filter is added on every secured dimension/fact for that level, using the corresponding column added for that group level (for example, Core."Dim - Security Dimension"."Current Level 1 Login" = VALUEOF(NQ_SESSION."USER"). Users are associated automatically with the correct user group (Base Hierarchy Level, Hierarchy Level 1, and so on), based on their current hierarchy level (0-9). This new security design has been implemented in three business models (Core, Pharma and Usage Accelerator). In the Core business model, it only supports the Siebel or Oracle E-Business Suite application data warehouse. Primary Position Based Security group is still available in the repository, but has been disabled. For the other Siebel or Oracle E-Business Suite application based business models, the old data security is still enabled. Position based data security does not support Oracle BI Application data warehouse in this release.

Data-level security in Oracle BI Applications is implemented in three major steps:

1. Set up initialization blocks that obtain specific security-related information when a user logs in—for example, the user's primary position ID.

   See Initialization Blocks and Security-Related Information in Oracle BI.

2. Set up the filters for each security group on each logical table that needs to be secured.

   See Filters for Oracle BI Security Groups.

3. Set up the joins to the appropriate security tables in the metadata physical and logical layers.

   See Configuring Oracle BI Repository Table Joins for Security.