| Oracle® Business Intelligence Applications Installation and Configuration Guide Version 7.9.4 E10742-01 |
|
 Previous |
 Next |
| Oracle® Business Intelligence Applications Installation and Configuration Guide Version 7.9.4 E10742-01 |
|
Previous |
Next |
This chapter describes the security features in Oracle Business Intelligence Applications, and includes the following main topics:
Section 7.1, "Types of Security in Oracle Business Intelligence Applications"
Section 7.2, "Integrating Data Security with Oracle E-Business Suite"
Section 7.3, "Integrating Data Security with Oracle's PeopleSoft Enterprise Applications"
Section 7.4, "Metadata Object-Level Security in Oracle BI Applications"
Section 7.5, "Integrating Data Security with the Siebel CRM Application"
Section 7.6, "Data-Level Security In Oracle BI Applications"
Oracle Business Intelligence Applications integrate tightly with the security model of the operational source system to allow the right content to be shown to the right user. Oracle BI Applications are embedded with a host of securing options that an administrator can leverage to authenticate and show critical business data to the right people.
Security in Oracle BI Applications can be classified broadly into three different categories:
User Security (Authentication of users)
Object Security
Data Security
User security refers to authentication and confirmation of the identity of a user based on the credentials provided. Object security controls the visibility to business logical objects (like Subject Areas, Presentation Tables, etc. defined in the metadata repository) and web objects (like Dashboard, Answers, etc. defined in the Presentation Catalog) based on a user's role. An example of a user's role is "Financial Analyst". Data security controls the visibility of data (content rendered in Subject Areas, Dashboards, Answers) based on the user's association to data in the OLTP system. An example is "Business Unit based Security".
Data and Object security are implemented in Oracle BI Applications using Security Groups. These security groups are defined using the Security Manager in Oracle Business Intelligence Administration Tool (Menu: Manage > Security). The standard structure of security groups and users in Oracle BI Applications has the hierarchy Data Security Group, then Object Security Group, then User, as shown in the following figure.
The detail wirings between security groups and users can vary, and needs to be decided on actual implementation.
Oracle BI Applications aligns a user's security profile in Oracle Business Intelligence consistent with their security profiles in the source applications. The alignment is done by one of the following:
Creating Security groups in the Oracle Business Intelligence application repository with the same names as some existing Responsibilities or groups in the source applications. These security groups are added as members to some Oracle Business Intelligence specific security groups (for Position based or Org based security), and the users will inherit this membership based on their own responsibilities or roles in the OLTP application.
Adding new Oracle Business Intelligence specific Responsibilities (Oracle EBS and Siebel CRM Applications) or Roles (PeopleSoft application) in the source applications, making sure their names match the object security groups in Oracle BI Applications, and assigning OLTP users to these new groups. The users will then inherit the security group membership the same way as described in the first scenario.
Note: Users should always be created in the operational application databases or directory services such as LDAP for the Security mechanism to work, never in the Oracle Business Intelligence repository. If the users are created in the RPD, the security mechanism does not work. For details on integration with Oracle E-Business Suite and PeopleSoft applications, please refer to Section 7.2, "Integrating Data Security with Oracle E-Business Suite" and Section 7.3, "Integrating Data Security with Oracle's PeopleSoft Enterprise Applications".
There are two ways for an Administrator to check a user's responsibility:
In the Siebel or Oracle E-Business Suite operational application, go to the Responsibilities view.
In the PeopleSoft application, go to the Roles View to check a user's roles.
In the Oracle Business Intelligence application, click on Settings/My Account link. The Presentation Services group membership for the user is shown near the bottom of the Web page. These are the Presentation Services groups, defined in the Presentation Services Catalog only, and which are usually used to control the ability to perform actions (privileges). If a Presentation Services group has the same name as an Oracle BI Server security group, and the user is a member of the latter, than he will become automatically a member of the corresponding Presentation group.
When you add a new responsibility to a user in Oracle BI Web, the change is not immediately reflected in the Oracle BI environment. In order to register the new user responsibility, both the Administrator and the user must perform a task:
The Oracle BI Administrator must reload the Oracle BI Server metadata through Oracle BI Presentation Services.
Then the user must log out from the from Oracle Business Intelligence application, or from Siebel or Oracle E-Business Suite operational application in case the user is looking at the Oracle Business Intelligence dashboards using an embedded application, and then log in again.
The User Administrator and the Group Administrators are a special user and group respectively, that do not have any restrictions and do not go through the Siebel or Oracle E-Business Suite database. The User SADMIN (password SADMIN) is also a special user, similar to Administrator.
The Administrator group is set up as a member of the Supergroup Administrators, so members of this group have no restrictions.
Note: Be sure to change the default password before migrating to production.
The Administrator group is set up as a member of the supergroup Administrators, so members of this group have no restrictions.
This section explains how security in Oracle BI Applications is deployed with Oracle E-Business Suite (EBS). Read this section if you want to understand how security is configured out-of-the-box so that you can change the way that security is implemented if required. This section contains the following sections:
Section 7.2.2, "Operating Unit-Based Security With Oracle EBS"
Section 7.2.3, "Inventory Organization-Based Security With Oracle EBS"
Section 7.2.4, "Company Org-based Security Against Oracle EBS"
Section 7.2.6, "Business Group Org-based Security Against Oracle EBS"
The authorization process of Oracle BI Applications fetches a user's responsibilities from source Oracle EBS applications, matches them with all Oracle BI Applications security groups, and determine the user's applicable object security during a user's session. The initialization block "Authorization" is used to fetch roles and assign the result set to a special session variable called "GROUP". The initialization block SQL is:
SELECT DISTINCT 'GROUP', RESPONSIBILITY_NAME FROM
FND_USER ,FND_USER_RESP_GROUPS, FND_RESPONSIBILITY_VL
WHERE
FND_USER.user_id=FND_USER_RESP_GROUPS.user_id
AND FND_USER_RESP_GROUPS.RESPONSIBILITY_ID = FND_RESPONSIBILITY_VL.RESPONSIBILITY_ID
AND FND_USER_RESP_GROUPS.RESPONSIBILITY_APPLICATION_ID = FND_RESPONSIBILITY_VL.APPLICATION_ID AND
FND_USER_RESP_GROUPS.START_DATE < SYSDATE AND
(CASE WHEN FND_USER_RESP_GROUPS.END_DATE IS NULL THEN SYSDATE ELSE TO_DATE(FND_USER_RESP_GROUPS.end_Date) END) >= SYSDATE
AND FND_USER.user_id = = (SELECT USER_ID FROM FND_USER WHERE USER_NAME = ':USER')
This release of Oracle BI Applications supports the following types of data security integrating with Oracle EBS:
Operating Unit-Based security against Oracle EBS (for more information, see Section 7.2.2, "Operating Unit-Based Security With Oracle EBS").
Inventory Organization-based security against Oracle EBS (for more information, see Section 7.2.3, "Inventory Organization-Based Security With Oracle EBS").
Company Org-based security against Oracle EBS (for more information, see Section 7.2.4, "Company Org-based Security Against Oracle EBS").
Ledger based security (for more information, see Section 7.2.4, "Company Org-based Security Against Oracle EBS").
Business Group Org-based security against Oracle EBS (for more information, see Section 7.2.6, "Business Group Org-based Security Against Oracle EBS").
Primary Employee/Position Hierarchy-based Security against Oracle EBS - HRMS application (for more information see Section 7.5.1, "Primary Position based Security").
Oracle BI Applications Version 7.9.4 does not support GL flex field or HR security profile-based security against EBS.
This section covers Operating Unit-Based Security With Oracle EBS.
Operating Units are secured by attaching a security profile to a user ID or responsibility. In turn, a security profile is associated with an organization hierarchy, which also has access to the user ID or responsibility (see figure below). The user ID or responsibility is defined using the System Administrator responsibility. The security profile and organization hierarchy are defined using the HRMS manager responsibility.
Operating Unit Dimension is decided by looking at the profiles set at User/Responsibility/Application/Site Levels (in order). In other words, if a value is set in the profile at User Level and at Site Level, the value set at user level takes precedence.
The profiles used are:
MO: Security Profile.
Depending on the Security Profile assigned, the Operating Units that are associated with that Security Profile become accessible to the User.
MO: Operating Unit.
If (1) is set to NULL (not set at any of the 4 levels), the profile MO: Operating Unit is used. (One MO: Operating Unit can be assigned to only one Operating Unit, whereas MO: Security Profile contains a Security Profile, which can be assigned to multiple Operating Units.)
Operating Unit Dimension is essentially a User secured dimension, which does the following:
It looks at the context of the user accessing a report.
It determines which Operating Units the user can access.
It displays the content.
From Oracle EBS R12 onwards, one Operating Unit is not fixed to just one responsibility. Users can still login as "Purchasing, Vision Operations (USA)" and create a PO for "Vision Germany", as OU is now shown as a LOV in the PO screen and the LOV values that show up depend on the 2 profiles.
This section covers Operating Unit-Based Security With Oracle EBS.
The sequence for Operating Unit-Based Security With Oracle EBS is described below:
When a user logs in to Oracle BI Applications, the session variable below is set automatically.
USER (System variable)
The "EBS Single Sign-on Integration" session variable is initialized in the "EBS Single Sign-on Integration" initialization block:
EBS_SSO_INTEGRATION_MODE
This session can be initialized with two possible values, "Integrated" or "Not Integrated", to indicate whether Oracle BI Applications is integrated with EBS SSO or not.
The "EBS Security Context" initialization block then populates these session variables:
OLTP_EBS_RESP_ID
The session variable is initialized with the responsibility of the user's session in Oracle EBS if Oracle BI Applications is integrated with EBS; otherwise it is defaulted to a random value, which will be ignored.
OLTP_EBS_RESP_APPL_ID
The session variable is initialized with the responsibility application of the user session in EBS if Oracle BI Applications is integrated with EBS; otherwise it is defaulted to a random value, which will be ignored.
The Oracle Business Intelligence Server will get the set of books corresponding to the USER from FND_USER_RESP_GROUPS. The following session variable is set automatically:
OU_ORG (Row-wise variable)
The initialization block "Operating Unit Org", which sets the value for this variable, is shown below.
Initialization block -- "Operating Unit Org"
The initialization block "Operating Unit Org" sets the value for variable OU_ORG using the following SQL:
SELECT DISTINCT 'OU_ORG', TO_CHAR(PER_ORGANIZATION_LIST.ORGANIZATION_ID)
FROM PER_ORGANIZATION_LIST,
(SELECT FND_PROFILE.VALUE_SPECIFIC('XLA_MO_SECURITY_PROFILE_LEVEL', USER_ID, RESPONSIBILITY_ID, RESPONSIBILITY_APPLICATION_ID) PROFILE_ID
FROM (SELECT USER_ID, RESPONSIBILITY_ID, RESPONSIBILITY_APPLICATION_ID
FROM FND_USER_RESP_GROUPS
WHERE START_DATE < SYSDATE
AND (CASE WHEN END_DATE IS NULL THEN SYSDATE ELSE TO_DATE(END_DATE) END) >= SYSDATE
AND USER_ID = (SELECT USER_ID FROM FND_USER WHERE USER_NAME = ':USER')
AND RESPONSIBILITY_ID = (CASE WHEN VALUEOF(NQ_SESSION.EBS_SSO_INTEGRATION_MODE) = 'Integrated' THEN
VALUEOF(NQ_SESSION.OLTP_EBS_RESP_ID) ELSE RESPONSIBILITY_ID END)
AND RESPONSIBILITY_APPLICATION_ID = (CASE WHEN VALUEOF(NQ_SESSION.EBS_SSO_INTEGRATION_MODE) = 'Integrated' THEN VALUEOF(NQ_SESSION.OLTP_EBS_RESP_APPL_ID) ELSE RESPONSIBILITY_APPLICATION_ID END)))
WHERE PER_ORGANIZATION_LIST.SECURITY_PROFILE_ID = PROFILE_ID
UNION
SELECT DISTINCT 'OU_ORG', FND_PROFILE.VALUE_SPECIFIC('ORG_ID', USER_ID, RESPONSIBILITY_ID, RESPONSIBILITY_APPLICATION_ID) ORGANIZATION_ID
FROM (SELECT USER_ID, RESPONSIBILITY_ID, RESPONSIBILITY_APPLICATION_ID
FROM FND_USER_RESP_GROUPS
WHERE START_DATE < SYSDATE
AND (CASE WHEN END_DATE IS NULL THEN SYSDATE ELSE TO_DATE(END_DATE) END) >= SYSDATE
AND USER_ID = (SELECT USER_ID FROM FND_USER WHERE USER_NAME = ':USER')
AND RESPONSIBILITY_ID = (CASE WHEN VALUEOF(NQ_SESSION.EBS_SSO_INTEGRATION_MODE) = 'Integrated' THEN VALUEOF(NQ_SESSION.OLTP_EBS_RESP_ID) ELSE RESPONSIBILITY_ID END)
AND RESPONSIBILITY_APPLICATION_ID = (CASE WHEN VALUEOF(NQ_SESSION.EBS_SSO_INTEGRATION_MODE) = 'Integrated' THEN VALUEOF(NQ_SESSION.OLTP_EBS_RESP_APPL_ID) ELSE RESPONSIBILITY_APPLICATION_ID END))
This section explains Inventory Organization-Based Security With Oracle EBS.
Ideally, Inventory organization security is applied on the basis of the currently logged-in responsibility, rather than a current user.
In EBS however, if an Inventory Org V1 has been associated with Responsibility R1 and R2, then V1 is accessible only to those 2 responsibilities. If another Inventory Org V2 has not been defined at all in that screen, then all responsibilities have access to V2. Each record entry into this form inserts a row into ORG_ACCESS table. This is the reason for a UNION based query of BIS_ORGANIZATIONS_V, and ideally, the Inventory Org secured view definition should have been:
SELECT 'INV_ORG', BIS_ORGANIZATIONS_V.ID FROM BIS_ORGANIZATIONS_V WHERE RESPONSIBILITY_ID = :RESPONSIBILITY_ID
This would give us a list of Inventory Organizations that are accessible to the "logged in" responsibility. In case security is set as user level in BI EE, the BI query is secure because it simply checks all the responsibilities accessible to the user, and then queries the ORG_ACCESS to check all the Inventory Organizations accessible to those responsibilities and all those Inventory Organizations that are not specifically allocated to any responsibility (in other words, accessible to everyone). This combined list of Inventory Orgs would be applied in the query. Therefore, it is possible that certain inventory organizations have been explicitly granted to certain responsibilities (using the ORG ACCESS form), and if the responsibility is not assigned to the logged-in user, then those inventory orgs would not be displayed to the user.
The sequence for Inventory-Based Security With Oracle EBS is described below:
When a user logs in to Oracle BI Applications, the session variable below is set automatically.
USER (System variable)
The "EBS Single Sign-on Integration" session variable is initialized in the "EBS Single Sign-on Integration" initialization block:
EBS_SSO_INTEGRATION_MODE
This session can be initialized with two possible values, "Integrated" or "Not Integrated", to indicate whether Oracle BI Applications is integrated with EBS SSO or not.
The "EBS Security Context" initialization block then populates these session variables:
OLTP_EBS_RESP_ID
The session variable is initialized with the responsibility of the user session in Oracle EBS if Oracle BI Applications is integrated with EBS; otherwise it is defaulted to a random value, which will be ignored.
OLTP_EBS_RESP_APPL_ID
The session variable is initialized with the responsibility application of the user session in EBS if Oracle BI Applications is integrated with EBS; otherwise it is defaulted to a random value, which will be ignored.
The Oracle Business Intelligence Server will get the set of books corresponding to the USER from FND_USER_RESP_GROUPS. The following session variable is set automatically:
INV_ORG (Row-wise variable)
The initialization block "Inventory Organizations", which sets the value for this variable, is shown below.
Initialization block -- "Inventory Organizations"
The initialization block "Inventory Organizations" sets the value for variable INV_ORG using the following SQL:
SELECT DISTINCT 'INV_ORG', BIS_ORGANIZATIONS_V.ID
FROM FND_USER_RESP_GROUPS, BIS_ORGANIZATIONS_V
WHERE FND_USER_RESP_GROUPS.RESPONSIBILITY_ID = BIS_ORGANIZATIONS_V.RESPONSIBILITY_ID
AND FND_USER_RESP_GROUPS.START_DATE < SYSDATE
AND (CASE WHEN FND_USER_RESP_GROUPS.END_DATE IS NULL THEN SYSDATE ELSE
AND FND_USER_RESP_GROUPS.USER_ID = (SELECT USER_ID FROM FND_USER WHERE USER_NAME = ':USER')
AND RESPONSIBILITY_ID = (CASE WHEN VALUEOF(NQ_SESSION.EBS_SSO_INTEGRATION_MODE) = 'Integrated' THEN
VALUEOF(NQ_SESSION.OLTP_EBS_RESP_ID) ELSE RESPONSIBILITY_ID END)
AND RESPONSIBILITY_APPLICATION_ID = (CASE WHEN VALUEOF(NQ_SESSION.EBS_SSO_INTEGRATION_MODE) =
'Integrated' THEN VALUEOF(NQ_SESSION.OLTP_EBS_RESP_APPL_ID) ELSE RESPONSIBILITY_APPLICATION_ID END)
Company Org-based security against EBS was introduced in 7.9.3. It is replaced by Ledger Security in 7.9.4.
This section covers Ledger Org-based security against Oracle EBS.
In Oracle E-Business Suite, a set of book is essentially a reporting organization that uses a common chart of accounts, functional currency, and fiscal calendar. The company org-based security filters data based on the set of books associated to the logged in user.
The sequence for Company Org-based security against Oracle EBS is described below:
When a user logs in to Oracle BI Applications, the session variable below is set automatically.
USER (System variable)
The "EBS Single Sign-on Integration" session variable is initialized in the "EBS Single Sign-on Integration" initialization block:
EBS_SSO_INTEGRATION_MODE
This session can be initialized with two possible values, "Integrated" or "Not Integrated", to indicate whether Oracle BI Applications is integrated with EBS SSO or not.
The "EBS Security Context" initialization block then populates these session variables:
OLTP_EBS_RESP_ID
The session variable is initialized with the responsibility of the user session in Oracle EBS if Oracle BI Applications is integrated with EBS; otherwise it is defaulted to a random value, which will be ignored.
OLTP_EBS_RESP_APPL_ID
The session variable is initialized with the responsibility application of the user session in EBS if Oracle BI Applications is integrated with EBS; otherwise it is defaulted to a random value, which will be ignored.
The Oracle Business Intelligence Server will get the set of books corresponding to the USER and OLTP_EBS_RESP_ID from FND_USER_RESP_GROUPS. The following session variable is set automatically:
COMPANY (Row-wise variable)
The initialization block "Companies", which sets the value for this variable, is shown below.
Initialization block -- "Companies"
The initialization block "Companies" sets the value for variable COMPANY using the following SQL:
SELECT DISTINCT 'COMPANY', FND_PROFILE.VALUE_SPECIFIC('GL_SET_OF_BKS_ID', USER_ID, RESPONSIBILITY_ID, RESPONSIBILITY_APPLICATION_ID)
FROM (SELECT USER_ID, RESPONSIBILITY_ID, RESPONSIBILITY_APPLICATION_ID FROM FND_USER_RESP_GROUPS WHERE START_DATE < SYSDATE AND (CASE WHEN END_DATE IS NULL THEN SYSDATE ELSE TO_DATE(END_DATE) END) >= SYSDATE AND USER_ID IN (SELECT USER_ID FROM FND_USER WHERE USER_NAME = ':USER')
AND RESPONSIBILITY_ID = (CASE WHEN VALUEOF(NQ_SESSION.EBS_SSO_INTEGRATION_MODE) = 'Integrated' THEN VALUEOF(NQ_SESSION.OLTP_EBS_RESP_ID) ELSE RESPONSIBILITY_ID END)
AND RESPONSIBILITY_APPLICATION_ID = (CASE WHEN VALUEOF(NQ_SESSION.EBS_SSO_INTEGRATION_MODE) = 'Integrated' THEN VALUEOF(NQ_SESSION.OLTP_EBS_RESP_APPL_ID) ELSE RESPONSIBILITY_APPLICATION_ID END))
The "Company Org-based Security" security group contains all the data access permission filters. During implementation, customers need to decide which users or groups should be associated to this security group.
Ledger-based security against Oracle EBS is introduced in Oracle BI Applications Version 7.9.4. It replaces the Company-based security to support the EBS GL set of books security model in E-Business Suite release 11i and the EBS Data Access Set model in E-Business Suite release 12.
In Oracle EBS Release 11i, a set of books is essentially a reporting entity that defines the reporting context including a chart of accounts, a functional currency, and an accounting calendar. A set of books can be assigned to a user, a responsibility, or to the site as the default for all responsibilities. Each user is associated with a single set of books when they logon to the application under a given responsibility in Oracle Applications. The Ledger-based security filters data based on the set of books associated to the logged in user.
In Oracle EBS Release 12, the set of books is replaced by the ledger. A ledger determines the currency, chart of accounts, accounting calendar, ledger processing options and subledger accounting method. The data access set assigned to the user's responsibility controls what ledgers the user can access. A user may be able to access multiple ledgers from a responsibility. The Ledger-based security filters data based on the ledgers associated to the logged in user.
Source-specific steps for Oracle EBS:
When a user logs in to Oracle Business Intelligence Enterprise Edition, the session variable below is set automatically.
USER (System variable)
The "EBS Single Sign-on Integration" session variable is initialized in the "EBS Single Sign-on Integration" initialization block:
EBS_SSO_INTEGRATION_MODE
This session can be initialized with two possible values, "Integrated" or "Not Integrated", to indicate whether Oracle BI Applications is integrated with EBS SSO or not.
The "EBS Security Context" initialization block then populates these session variables:
OLTP_EBS_RESP_ID
The session variable is initialized with the responsibility of the user session in Oracle EBS if Oracle BI Applications is integrated with EBS; otherwise it is defaulted to a random value, which will be ignored.
OLTP_EBS_RESP_APPL_ID
The session variable is initialized with the responsibility application of the user session in EBS if Oracle BI Applications is integrated with EBS; otherwise it is defaulted to a random value, which will be ignored.
Then this session variable would be initialized in another init block, ÒLedgersÓ, which gets the ledgers (which is essentially the set of books in EBS) corresponding to the USER and OLTP_EBS_RESP_ID and OLTP_EBS_RESP_APPL_ID, via table FND_USER_RESP_GROUPS and procedure FND_PROFILE.
Row-wise variable:
LEDGER (Row-wise variable)
The Oracle BI server gets the set of books or ledgers corresponding to the USER and OLTP_EBS_RESP_ID from the OLTP. The "Ledgers" initialization block then populates these session variables.
The Ledgers initialization block should be set according to the Oracle EBS release, as follows:
If you are using EBS release 12 or after, please use the following SQL as the data source in the initialization block:
SELECT DISTINCT 'LEDGER', TO_CHAR(GAL.LEDGER_ID)
FROM GL_ACCESS_SET_LEDGERS GAL, (SELECT FND_PROFILE.VALUE_SPECIFIC('GL_ACCESS_SET_ID',USER_ID, RESPONSIBILITY_ID, RESPONSIBILITY_APPLICATION_ID) PROFILE_VALUE
FROM (SELECT USER_ID, RESPONSIBILITY_ID, RESPONSIBILITY_APPLICATION_ID
FROM FND_USER_RESP_GROUPS
WHERE START_DATE < SYSDATE AND (CASE WHEN END_DATE IS NULL THEN SYSDATE ELSE
TO_DATE(END_DATE) END) >= SYSDATE AND USER_ID = (CASE WHEN
'VALUEOF(NQ_SESSION.EBS_SSO_INTEGRATION_MODE)' = 'Integrated'
THEN VALUEOF(NQ_SESSION.OLTP_EBS_USER_ID) ELSE (SELECT USER_ID FROM FND_USER WHERE
USER_NAME = 'OPERATIONS') END) AND RESPONSIBILITY_ID = (CASE WHEN
'VALUEOF(NQ_SESSION.EBS_SSO_INTEGRATION_MODE)' = 'Integrated'
THEN VALUEOF(NQ_SESSION.OLTP_EBS_RESP_ID) ELSE RESPONSIBILITY_ID END)AND RESPONSIBILITY_APPLICATION_ID = (CASE WHEN
'VALUEOF(NQ_SESSION.EBS_SSO_INTEGRATION_MODE)' = 'Integrated'
THEN VALUEOF(NQ_SESSION.OLTP_EBS_RESP_APPL_ID) ELSE RESPONSIBILITY_APPLICATION_ID END)
))WHERE GAL.ACCESS_SET_ID = PROFILE_VALUE
The repository file shipped in Oracle BI Applications 7.9.4 release include the EBS release 12 version of the SQL in the ÒLedgerÓ initialization block.
If you are running BI applications against Oracle EBS 11i, please use the following SQL as the data source in ÒLedgerÓ initialization block:
SELECT DISTINCT 'LEDGER', FND_PROFILE.VALUE_SPECIFIC('GL_SET_OF_BKS_ID', USER_ID,
RESPONSIBILITY_ID, RESPONSIBILITY_APPLICATION_ID)
FROM (SELECT USER_ID, RESPONSIBILITY_ID, RESPONSIBILITY_APPLICATION_ID FROM
FND_USER_RESP_GROUPS
WHERE START_DATE < SYSDATE
AND (CASE WHEN END_DATE IS NULL THEN SYSDATE ELSE TO_DATE(END_DATE) END) >= SYSDATE
AND USER_ID IN (CASE WHEN VALUEOF(NQ_SESSION.EBS_SSO_INTEGRATION_MODE) = 'Integrated'
THEN VALUEOF(NQ_SESSION.OLTP_EBS_USER_ID) ELSE (SELECT USER_ID FROM FND_USER WHERE USER_NAME = ':USER') END)
AND RESPONSIBILITY_ID = (CASE WHEN VALUEOF(NQ_SESSION.EBS_SSO_INTEGRATION_MODE) = 'Integrated'
THEN VALUEOF(NQ_SESSION.OLTP_EBS_RESP_ID) ELSE RESPONSIBILITY_ID END)
AND RESPONSIBILITY_APPLICATION_ID = (CASE WHEN
VALUEOF(NQ_SESSION.EBS_SSO_INTEGRATION_MODE) = 'Integrated'
THEN VALUEOF(NQ_SESSION.OLTP_EBS_RESP_APPL_ID) ELSE RESPONSIBILITY_APPLICATION_ID END))
This section explains Business Group Org-based security against Oracle EBS.
A Business Group is the highest level in the organization structure is usually used to represent the entire enterprise or a major division. A business group can have several sets of books.
The sequence for Business Group Org-based security against Oracle EBS is described below:
When a user logs in to Oracle BI Applications, the session variable below is set automatically.
USER (System variable)
The "EBS Single Sign-on Integration" session variable is initialized in the "EBS Single Sign-on Integration" initialization block:
EBS_SSO_INTEGRATION_MODE
This session can be initialized with two possible values, "Integrated" or "Not Integrated", to indicate whether Oracle BI Applications is integrated with EBS SSO or not.
The "EBS Security Context" initialization block then populates these session variables:
OLTP_EBS_RESP_ID
The session variable is initialized with the responsibility of the user session in Oracle EBS if Oracle BI Applications is integrated with EBS; otherwise it is defaulted to a random value, which will be ignored.
OLTP_EBS_RESP_APPL_ID
The session variable is initialized with the responsibility application of the user session in EBS if Oracle BI Applications is integrated with EBS; otherwise it is defaulted to a random value, which will be ignored.
The Oracle Business Intelligence Server will get the set of books corresponding to the USER and OLTP_EBS_RESP_ID from FND_USER_RESP_GROUPS. The following session variable is set automatically:
BUSINESS_GROUP (Row-wise variable)
The initialization block "Business Groups", which sets the value for this variable, is shown below.
Initialization block -- "Business Groups"
The initialization block "Business Groups" sets value for variable INV_ORG using the following SQL:
SELECT DISTINCT 'BUSINESS_GROUP', TO_CHAR(FND_PROFILE.VALUE_SPECIFIC('PER_BUSINESS_GROUP_ID', USER_ID, RESPONSIBILITY_ID, RESPONSIBILITY_APPLICATION_ID))
FROM (SELECT USER_ID, RESPONSIBILITY_ID, RESPONSIBILITY_APPLICATION_ID FROM FND_USER_RESP_GROUPS WHERE START_DATE < SYSDATE AND (CASE WHEN END_DATE IS NULL THEN SYSDATE ELSE TO_DATE(END_DATE) END) >= SYSDATE AND USER_ID = (SELECT USER_ID FROM FND_USER WHERE USER_NAME = ':USER')
AND RESPONSIBILITY_ID = (CASE WHEN VALUEOF(NQ_SESSION.EBS_SSO_INTEGRATION_MODE) = 'Integrated' THEN VALUEOF(NQ_SESSION.OLTP_EBS_RESP_ID) ELSE RESPONSIBILITY_ID END)
AND RESPONSIBILITY_APPLICATION_ID = (CASE WHEN VALUEOF(NQ_SESSION.EBS_SSO_INTEGRATION_MODE) = 'Integrated' THEN VALUEOF(NQ_SESSION.OLTP_EBS_RESP_APPL_ID) ELSE RESPONSIBILITY_APPLICATION_ID END))
The "Business Group Org-based Security" security group contains all the data access permission filters. During implementation, customers need to decide which users or groups should be associated to this security group.
Employee based security restricts data visibility of the records to the Owner of that record, and all employees he/she reports to in the company's Employee hierarchy. This security mechanism uses data from the data warehouse database, and shares the metadata components with other supported applications (Siebel CRM and PeopleSoft). Out of the box, this type of security supports only HR Analytics facts. For more information on how this security mechanism works, see Section 7.5.2, "Primary Position Based Security for CRM Vertical applications".
This section explains how security is implemented for Oracle's PeopleSoft Enterprise Applications in Oracle Business Intelligence Applications. Read this section if you want to understand how security is configured out-of-the-box so that you can change the way that security is implemented if required. This section contains the following sections:
Appendix 7, "About Operating Unit-based Security Against PeopleSoft Financials"
Appendix 7, "About Company Org-based Security Against PeopleSoft Financials or PeopleSoft HR"
Appendix 7, "About Ledger-based Security against Oracle's PeopleSoft Enterprise Applications"
Appendix 7, "About HR Org-based Security Against PeopleSoft HR"
Appendix 7, "About Payables Org-based security Against PeopleSoft Financials"
Appendix 7, "About Receivables Org-based Security Against PeopleSoft Financials"
Appendix 7, "About SET ID-based Security Against PeopleSoft Financials"
Appendix 7, "About Human Resource Personnel Data Analyst Security Against PeopleSoft HR"
The authorization process of Oracle BI Applications fetches a user's role from source PeopleSoft applications, matches the role with all Oracle BI Applications security groups, and determine the user's applicable object security during a user's session. The initialization block "Authorization" is used to fetch roles and assign the result set to a special session variable called "GROUP", which will be examined later by the Oracle Business Intelligence Server for matching. The initialization block SQL is:
SELECT DISTINCT
'GROUP', ROLENAME
FROM
PSROLEUSER
WHERE
ROLEUSER = ':USER'
Oracle Business Intelligence Applications offers the following types of data security:
"About Operating Unit-based Security Against PeopleSoft Financials"
"About Company Org-based Security Against PeopleSoft Financials or PeopleSoft HR"
"About Ledger-based Security against Oracle's PeopleSoft Enterprise Applications"
"About Payables Org-based security Against PeopleSoft Financials"
"About Receivables Org-based Security Against PeopleSoft Financials"
"About Human Resource Personnel Data Analyst Security Against PeopleSoft HR"
The sequence for Operating Unit-based Security against PeopleSoft Financials is described below:
When a user logs in to Oracle BI Applications, the session variable below is set automatically.
USER (System variable)
The Oracle BI Server then gets the operating units (or the general ledger business units in PeopleSoft Financials) corresponding to the USER from the following tables:
PS_SEC_BU_OPR
PS_BUS_UNIT_TBL_GL
PS_INSTALLATION_FS
The following session variable is set automatically:
OU_ORG (Row-wise variable)
The initialization block "Operating Unit Organizations", which sets the value for this variable, is shown below.
Initialization block -- "Operating Unit Org"
The initialization block "Operating Unit Org" sets value for variable OU_ORG using the following SQL:
SELECT DISTINCT 'OU_ORG', S1.BUSINESS_UNIT
FROM PS_SEC_BU_OPR S1, PS_BUS_UNIT_TBL_GL A, PS_INSTALLATION_FS I
WHERE S1.OPRID = ':USER'
AND S1.BUSINESS_UNIT = A.BUSINESS_UNIT
AND I.SECURITY_TYPE = 'O'
AND I.BU_SECURITY = 'Y'
UNION
SELECT DISTINCT 'OU_ORG', S2.BUSINESS_UNIT
FROM PS_SEC_BU_CLS S2,
PS_BUS_UNIT_TBL_GL A,
PS_INSTALLATION_FS I2,
PSOPRDEFN P
WHERE P.OPRID = ':USER'
AND S2.BUSINESS_UNIT = A.BUSINESS_UNIT
AND P.OPRCLASS = S2.OPRCLASS
AND I2.SECURITY_TYPE = 'C'
AND I2.BU_SECURITY = 'Y'
The "Operating Unit Org-based Security" security group contains all the data access permission filters. During implementation, customers need to decide which users or groups should be associated to this security group.
The sequence for Company Org-based Security Against PeopleSoft Financials or PeopleSoft HR is described below:
When a user logs in to Oracle BI Applications, the session variable below is set automatically.
USER (System variable)
The Oracle BI Server then gets the companies or business units corresponding to the USER from the following tables:
PS_SEC_BU_OPR
PS_BUS_UNIT_TBL_GL
PS_INSTALLATION_FS for PeopleSoft Financials
PS_SCRTY_TBL_DEPT
PS_BU_DEPT_VW
PS_BUS_UNIT_TBL_GL
PSOPRDEFN for PeopleSoft HR
The following session variable is set automatically:
COMPANY (Row-wise variable)
The initialization block "Companies", which sets the value for this variable, is shown below.
Initialization block -- "Companies"
The initialization block "Companies" sets value for variable COMPANY using the following SQL:
For PeopleSoft Financials:
SELECT DISTINCT 'COMPANY', S1.BUSINESS_UNIT
FROM PS_SEC_BU_OPR S1, PS_BUS_UNIT_TBL_GL A, PS_INSTALLATION_FS I
WHERE S1.OPRID = ':USER'
AND S1.BUSINESS_UNIT = A.BUSINESS_UNIT
AND I.SECURITY_TYPE = 'O'
UNION
SELECT DISTINCT 'COMPANY', S2.BUSINESS_UNIT
FROM PS_SEC_BU_CLS S2,
PS_BUS_UNIT_TBL_GL A,
PS_INSTALLATION_FS I2,
PSOPRDEFN P
WHERE P.OPRID = ':USER'
AND S2.BUSINESS_UNIT = A.BUSINESS_UNIT
AND P.OPRCLASS = S2.OPRCLASS
AND I2.SECURITY_TYPE = 'C'
AND I2.BU_SECURITY = 'Y'
For PeopleSoft HR:
SELECT DISTINCT 'COMPANY',C.BUSINESS_UNIT
FROM PSOPRDEFN A, PS_SCRTY_TBL_DEPT B, PS_BU_DEPT_VW C, PS_BUS_UNIT_TBL_GL D
WHERE
A.ROWSECCLASS = B.ROWSECCLASS AND
B.ACCESS_CD = 'Y' AND
B.DEPTID = C.DEPTID AND
C.BUSINESS_UNIT = D.BUSINESS_UNIT AND
A.OPRID = ':USER'
The "Company Org-based Security" security group contains all the data access permission filters. During implementation, customers need to decide which users or groups should be associated to this security group.
Ledger-based security against PeopleSoft Enterprise Applications is introduced in 7.9.4. Ledger in PeopleSoft is reference data that is secured by and shared by business units. The Ledger table has the setID field and leverages the TableSet feature from PeopleTool. In addition, the Ledger data access is controlled by the Row-level security. With row-level support, you can implement security to restrict individual users or permission lists from specific rows of data that are controlled by the Ledger. The Ledger-based security filters data based on the ledgers associated to the logged in user. The ledgers are restricted by PeopleSoft TableSet and Row-based security.
The sequence for Ledger-based security against PeopleSoft Financials is described below:
When a user logs in to Oracle BI Applications, the session variable below is set automatically.
USER (System variable)
The Oracle BI Server gets the ledgers corresponding to the USER.
The following session variable is set automatically:
LEDGER (Row-wise variable)
The initialization block "Ledgers", which sets the value for this variable, is set as below.
SELECT DISTINCT'LEDGER', LG.SETID || SO.LEDGER
FROM PS_SEC_LEDGER_OPR SO, PS_LED_DEFN_TBL LG, PS_INSTALLATION_FS IFS
WHERE SO.LEDGER = LG.LEDGER AND IFS.SECURITY_TYPE = 'O'
AND IFS.LEDGER_SECURITY = 'Y' AND SO.OPRID = ':USER'
UNION
SELECT distinct 'LEDGER', LG.SETID || SC.LEDGER
FROM PS_SEC_LEDGER_CLS SC, PS_LED_GRP_TBL LG, PSOPRDEFN OP, PSROLEUSER ORL, PSROLECLASS RCL, PS_INSTALLATION_FS IFS
WHERE SC.LEDGER_GROUP = LG.LEDGER_GROUP AND SC.OPRCLASS = RCL.CLASSID AND OP.OPRID = ORL.ROLEUSER
AND ORL.ROLENAME = RCL.ROLENAME and IFS.SECURITY_TYPE = 'C' AND IFS.LEDGER_SECURITY = 'Y' AND OP.OPRID = ':USER'
When you set up Ledger security against a PeopleSoft application, you should also set up the Company Org-based security against PeopleSoft. The Ledger-based security does not automatically restrict the data by the GL business unit.
The sequence for HR Org-based Security Against PeopleSoft HR is described below:
When a user logs in to Oracle BI Applications, the session variable below is set automatically.
USER (System variable)
The Oracle BI Server gets the HR business units corresponding to the USER from the following tables:
PSOPRDEFN
PS_SCRTY_TBL_DEPT
PS_BU_DEPT_VW
PS_BUS_UNIT_TBL_HR
The following session variable is set automatically:
HR_ORG (Row-wise variable)
The initialization block "HR Organizations", which sets the value for this variable, is shown below.
Initialization block -- "HR Organizations"
The initialization block "HR Organizations" sets value for variable HR_ORG using the following SQL:
SELECT DISTINCT 'HR_ORG', C.BUSINESS_UNIT
FROM PSOPRDEFN A, PS_SCRTY_TBL_DEPT B, PS_BU_DEPT_VW C, PS_BUS_UNIT_TBL_HR D
WHERE
A.ROWSECCLASS = B.ROWSECCLASS AND
B.ACCESS_CD = 'Y' AND
B.DEPTID = C.DEPTID AND
C.BUSINESS_UNIT = D.BUSINESS_UNIT AND
A.OPRID = ':USER'
The "HR Org-based Security" security group contains all the data access permission filters. During implementation, customers need to decide which users or groups should be associated to this security group.
When users create ad-hoc reports, they see the data that is assigned with their permissions. For reports involved with the tables defined above, users are restricted to the data pertaining to their visibility in the organization structure.
The sequence for Payables Org-based security Against PeopleSoft Financials is described below:
When a user logs in to Oracle BI Applications, the session variable below is set automatically.
USER (System variable)
The Oracle BI Server gets the HR business units corresponding to the USER from the following tables:
PSOPRDEFN
PS_SEC_BU_OPR
PS_SEC_BU_CLR
PS_INSTALLATION_FS
PS_BUS_UNIT_TBL_AP
The following session variable is set automatically:
PAYABLES_ORG (Row-wise variable)
The initialization block "Payables Organizations", which sets the value for this variable, is shown below.
Initialization block -- "Payables Organizations"
The initialization block "Payables Organizations" sets value for variable PAYABLES_ORG using the following SQL:
SELECT DISTINCT 'PAYABLES_ORG', s1.BUSINESS_UNIT
FROM PS_SEC_BU_OPR s1, PS_BUS_UNIT_TBL_AP a, PS_INSTALLATION_FS i
WHERE s1.OPRID = ':USER'
AND s1.BUSINESS_UNIT = a.BUSINESS_UNIT
AND i.SECURITY_TYPE = 'O'
AND i.BU_SECURITY = 'Y'
UNION
SELECT DISTINCT 'PAYABLES_ORG', s2.BUSINESS_UNIT
FROM PS_SEC_BU_CLS s2, PS_BUS_UNIT_TBL_AP a, PS_INSTALLATION_FS i2, PSOPRDEFN p
WHERE p.OPRID = ':USER'
AND s2.BUSINESS_UNIT = a.BUSINESS_UNIT
AND p.OPRCLASS = s2.OPRCLASS
AND i2.SECURITY_TYPE = 'C'
AND i2.BU_SECURITY = 'Y'
The "Payables Org-based Security" security group contains all the data access permission filters. During implementation, customers need to decide which users or groups should be associated to this security group.
When users create ad-hoc reports, they see the data that is assigned with their permissions. For reports involved with the tables defined above, users are restricted to the data pertaining to their visibility in the organization structure.
The sequence for Receivables Org-based Security Against PeopleSoft Financials is described below:
When a user logs in to Oracle BI Applications, the session variable below is set automatically.
USER (System variable)
The Oracle BI Server gets the HR business units corresponding to the USER from the following tables:
PSOPRDEFN
PS_SEC_BU_OPR
PS_SEC_BU_CLR
PS_INSTALLATION_FS
PS_BUS_UNIT_TBL_AP
The following session variable is set automatically:
RECEIVABLES_ORG (Row-wise variable)
The initialization block "Receivables Organizations", which sets the value for this variable, is shown below.
Initialization block -- "Receivables Organizations"
The initialization block "Receivables Organizations" sets value for variable RECEIVABLES_ORG using the following SQL:
SELECT DISTINCT 'RECEIVABLES_ORG', s1.BUSINESS_UNIT
FROM PS_SEC_BU_OPR s1, PS_BUS_UNIT_TBL_AR a, PS_INSTALLATION_FS i
WHERE s1.OPRID = ':USER'
AND s1.BUSINESS_UNIT = a.BUSINESS_UNIT AND i.SECURITY_TYPE = 'O'
AND i.BU_SECURITY = 'Y'
UNION
SELECT DISTINCT 'RECEIVABLES_ORG', s2.BUSINESS_UNIT
FROM PS_SEC_BU_CLS s2, PS_BUS_UNIT_TBL_AR a, PS_INSTALLATION_FS i2, PSOPRDEFN p
WHERE p.OPRID = ':USER'
AND s2.BUSINESS_UNIT = a.BUSINESS_UNIT AND p.OPRCLASS = s2.OPRCLASS AND i2.SECURITY_TYPE = 'C'
AND i2.BU_SECURITY = 'Y'
The "Receivables Org-based Security" security group contains all the data access permission filters. During implementation, customers need to decide which users or groups should be associated to this security group.
When users create ad-hoc reports, they see the data that is assigned with their permissions. For reports involved with the tables defined above, users are restricted to the data pertaining to their visibility in the organization structure.
The sequence for SET ID-based Security Against PeopleSoft Financials is described below:
When a user logs in to Oracle BI Applications, the session variable below is set automatically.
USER (System variable)
The Oracle BI Server gets the set IDs corresponding to the USER from the following tables:
PS_SEC_SETID_OPR
PS_SEC_SETID_CLS
PS_INSTALLATION_FS
PSOPRDEFN
The following session variable is set automatically:
SET_ID (Row-wise variable)
The initialization block "Set ID" sets value for variable SET_ID using the following SQL:
For PeopleSoft Financials:
SELECT DISTINCT 'SET_ID', s1.SETID
FROM PS_SEC_SETID_OPR s1, PS_INSTALLATION_FS i
WHERE s1.OPRID = ':USER'
AND i.SECURITY_TYPE = 'O'
AND i.SETID_SECURITY = 'Y'
UNION
SELECT DISTINCT 'SET_ID', s2.SETID
FROM PS_SEC_SETID_CLS s2, PS_INSTALLATION_FS i2, PSOPRDEFN p
WHERE p.OPRID = ':USER'
AND p.OPRCLASS = s2.OPRCLASS
AND i2.SECURITY_TYPE = 'C'
AND i2.SETID_SECURITY = 'Y'
The "Set ID-based Security" security group contains all the data access permission filters. During implementation, customers need to decide which users or groups should be associated to this security group.
HR personnel need to see all data for the Internal Organizations for which they are responsible for and the data for their subordinates in their own organization. The Human Resource Personnel Data Security group supports this requirement. The security mechanism for this group uses the following metadata elements:
Variable HR_ORG, which is a variable defined by the Row-wise Initialization Block: HR Organizations. This data set stores all the Organizations the user is responsible for, plus the user's own organization (the same as the one selected in USER_HR_ORG). The query for populating this data set is:
SELECT DISTINCT
'HR_ORG',
C.BUSINESS_UNIT
FROM
PSOPRDEFN A, PS_SCRTY_TBL_DEPT B, PS_BU_DEPT_VW C, PS_BUS_UNIT_TBL_HR D
WHERE
A.ROWSECCLASS = B.ROWSECCLASS AND
B.ACCESS_CD = 'Y' AND
B.DEPTID = C.DEPTID AND
C.BUSINESS_UNIT = D.BUSINESS_UNIT AND
A.OPRID = ':USER'
UNION
SELECT DISTINCT 'HR_ORG', FINAL_JOB.BUSINESS_UNIT
FROM (
SELECT X.EMPLID, MAX(X.BUSINESS_UNIT) BUSINESS_UNIT FROM
(
SELECT A.EMPLID, A.EMPL_RCD, A.EFFDT,EFFSEQ, A.JOB_INDICATOR,A.EMPL_STATUS, A.BUSINESS_UNIT
FROM PS_JOB A ,
(SELECT EMPLID, MAX(EFFDT) MAX_EFFDT
FROM PS_JOB
WHERE
JOB_INDICATOR = 'P' AND EMPL_STATUS IN ('A', 'L', 'P', 'W')
GROUP BY EMPLID) B
WHERE
A.EMPLID = B.EMPLID
AND A.EFFDT = B.MAX_EFFDT
AND A.JOB_INDICATOR = 'P' AND A.EMPL_STATUS IN ('A', 'L', 'P', 'W')
AND A.EFFSEQ = (SELECT MAX (C.EFFSEQ)
FROM PS_JOB C
WHERE
C.EMPLID = A.EMPLID AND
C.EMPL_RCD = A.EMPL_RCD AND
C.EFFDT = A.EFFDT AND
C.JOB_INDICATOR = 'P' AND C.EMPL_STATUS IN ('A', 'L', 'P', 'W'))
) X
GROUP BY X.EMPLID
) FINAL_JOB, PSOPRDEFN
WHERE
FINAL_JOB.EMPLID = PSOPRDEFN.EMPLID AND
PSOPRDEFN.OPRID = ':USER'
Variable USER_HR_ORG, defined using the Initialization block: User HR Organizations. This variable stores the user's own organization. The query for populating this variable is:
SELECT DISTINCT FINAL_JOB.BUSINESS_UNIT
FROM (
SELECT X.EMPLID, MAX(X.BUSINESS_UNIT) BUSINESS_UNIT FROM
(
SELECT A.EMPLID, A.EMPL_RCD, A.EFFDT,EFFSEQ, A.JOB_INDICATOR, A.EMPL_STATUS, A.BUSINESS_UNIT
FROM PS_JOB A ,
(SELECT EMPLID, MAX(EFFDT) MAX_EFFDT
FROM PS_JOB
WHERE
JOB_INDICATOR = 'P' AND EMPL_STATUS IN ('A', 'L', 'P', 'W')
GROUP BY EMPLID) B
WHERE
A.EMPLID = B.EMPLID
AND A.EFFDT = B.MAX_EFFDT
AND A.JOB_INDICATOR = 'P' AND A.EMPL_STATUS IN ('A', 'L', 'P', 'W')
AND A.EFFSEQ = (SELECT MAX (C.EFFSEQ)
FROM PS_JOB C
WHERE
C.EMPLID = A.EMPLID AND
C.EMPL_RCD = A.EMPL_RCD AND
C.EFFDT = A.EFFDT AND
C.JOB_INDICATOR = 'P' AND C.EMPL_STATUS IN ('A', 'L', 'P', 'W'))
) X
GROUP BY X.EMPLID
) FINAL_JOB, PSOPRDEFN
WHERE
FINAL_JOB.EMPLID = PSOPRDEFN.EMPLID AND
PSOPRDEFN.OPRID = ':USER'
A security group: Human Resources Analyst. The data filter defined for this group is:
Core."Dim - Employee Organization"."Employee Organization Number" = VALUEOF(NQ_SESSION."HR_ORG") AND (Core."Dim - Employee Organization"."Employee Organization Number" <> VALUEOF(NQ_SESSION."USER_HR_ORG") OR Core."Dim - Security Dimension"."Hierarchy Based Column" = VALUEOF(NQ_SESSION."USER"))
This filter joins the fact used in the report to the Employee Organization dimension to get the Organization number for the Employee owner of the fact record. If this Organization is among the HR_ORGs it will be compared next to the user's own organization. If they are different there is no further check, the record is selected. If they are the same, an additional filter is applied based on Employee hierarchy, to make sure the employee owner of this fact record is one of the user's subordinates.
Employee based security restricts data visibility of the records to the Owner of that record, and all employees that they report to in the company's Employee hierarchy. This security mechanism uses data from the data warehouse database, and shares the metadata components with other supported applications (for example, Oracle EBS, Siebel CRM). Out of the box, this type of security supports only HR Analytics facts. For more information on how this security mechanism works, see Section 7.5.2, "Primary Position Based Security for CRM Vertical applications".
Object-level security controls access to various Analytics objects, primarily metadata and Presentation Services objects.
Repository groups control access to metadata objects such as subject areas, tables and columns.
Metadata object security is configured in the Analytics repository (OracleBIAnalyticsApps.rpd) using the Oracle BI Administration Tool. The User Group Everyone is denied access to each of the subject areas. Each subject area is configured to give explicit read access to selected related responsibilities. This access can be extended to table and column level.
|
Note: In the shipped product, only permissions at the subject area level have been configured. |
The exceptions to the explicit configuration rule are the Communications and Financial Analytics industry applications, where there are tables and columns specific to these two industries scattered throughout the general Siebel operational application subject areas. These industry-specific metadata objects are hidden from other groups.
Oracle Business Intelligence supports hierarchies within the groups in the repository. In the Analytics repository there are certain groups that are parent groups, which define the behavior of all the child groups. Inheritance is used to let permissions ripple through to child groups. The parent groups and their purpose are shown in Table 7-1.
Table 7-1 Repository Parent Groups
| Parent Group | Permissions Inherited by |
|---|---|
|
Finance |
All Financial applications groups |
|
Insurance |
All Insurance applications groups |
|
CM General |
All Communications applications |
|
Consumer Sector |
Consumer Sector groups |
|
Pharma |
Life Sciences/Pharmaceuticals applications groups |
|
Channel Managers |
All Channel applications groups |
|
Partner Managers |
All Partner application groups |
Presentation Services objects, such as dashboards and pages, are controlled using Presentation Services groups, which have the same name as the Siebel responsibilities. Access to dashboards and pages is controlled using the Presentation Services groups. If you log on as a user who belongs to the Presentation Services group Field Sales Representative Analytics, then you see only the Overview, Forecasting, and Details pages within the Pipeline Dashboard. In a similar fashion, you see only dashboards that allow you access to at least one page within that dashboard. These groups are customized in the Oracle BI Web interface.
For Oracle Business Intelligence integrated with Oracle's Siebel operational applications, Presentation Services security makes use of the following principles:
Security in Presentation Services has been pre-configured for the groups listed in Table 7-1 for each application.
Permissions to each dashboard in Presentation Services are matched with the permissions of each related Siebel operational application view. In the Siebel operational application, views are controlled through responsibilities. However, in Oracle Business Intelligence Presentation Services, access to dashboards for each group is controlled through Web Administration. If the two access setups do not match, both of the following situations can occur:
If users have access to a view in the Siebel operational application, but do not have access to the corresponding dashboard, then they receive an error message indicating that they do not have access to the dashboard.
If users try to access a dashboard containing reports based on a subject area to which they do not have access, they see a dashboard with no reports.
This section describes how Oracle BI Applications security integrates with Siebel CRM security, and contains the following topics:
Section 7.5.2, "Primary Position Based Security for CRM Vertical applications"
Section 7.5.4, "Embedded Oracle Business Intelligence support for Changing Positions"
This section covers Primary Position based Security and contains the following sections:
Section 7.5.1.3, "Configuring Oracle BI Repository Table Joins for Position/Employee Based Security"
This type of security restricts data visibility for a fact (or dimension) record to the Primary owner of this record and those above him in the hierarchy. The primary owner of a record could be a Position (like for Opportunities and Revenue records in Siebel CRM application), or an Employee (like for Payroll records in Oracle EBS application). Primary Position based Security uses a flattened hierarchy table called W_POSTION_DH, which is based on W_POSITION_D, and is treated as a slowly changing dimension of type 2. For Siebel CRM based data, this table (W_POSITION_D) is populated from the Position table, so a new record is created for the same position, every time a new employee is associated with this position as the Primary Employee. For Oracle EBS and PeopleSoft sourced data, this table is populated from the Employee table (PER_ALL_PEOPLE_F, JTF_RESOURCES among others) for Oracle EBS), so a new record is created for the same Employee, every time the hierarchy structure of that employee, or his position changes.
Consequently, every record in the source table(s) can be represented by more than one record in this table (W_POSITION_DH), 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 the Position (or Employee record) at any time. The columns that are not prefixed with CURRENT reflect the hierarchy structure for the same Position (or Employee record) during the period between EFFECTIVE_START_DT and EFFECTIVE_END_DT. This latter set of columns is used for an AS WAS type of security, meaning the fact records are visible to the owner of a record, and his upper level managers at the time the record was created, even after he changes position or managers in the company hierarchy.
Facts join to this dimension via the record owner, for example W_REVN_F is joined using PR_POSITION_DH_WID, where PR_POSITION_DH_WID is the Primary Position on the Revenue in the source application. Another example is W_PAYROLL_F is joined using EMP_POSTN_DH_WID, where EMP_POSTN_DH_WID is the employee owner of this payroll record.
Primary Position based Security allows only the record owner and any Employee up in his hierarchy chain to see the record. Primary Position based Security uses the following metadata elements in the repository:
Session variable: HIER_LEVEL. It is populated by the Initialization block "User Hierarchy Level" using the following SQL:
Select round(FIXED_HIER_LEVEL) FROM VALUEOF(OLAPTBO).W_POSITION_DH WHERE BASE_LOGIN= ':USER' AND CURRENT_FLG='Y'
HIER_LEVEL value will take a number between 0 and 9, and will designate the level of the user in the Position (or Employee) hierarchy of the company. For example the CEO of the company is the only Employee who's HIER_LEVEL takes the value 9, if the employee hierarchy is a full tree.
A logical dimension "Dim - Security" joined to the supported facts with Primary Position/Employee - based security. This logical dimension is defined using on the physical table W_POSITION_DH.
A logical column in this dimension, called "Hierarchy Based Column", and defined as follows:
"INDEXCOL(VALUEOF(NQ_SESSION."HIER_LEVEL"), "Core"."Dim - Security Dimension"."Current Base Level Login", "Core"."Dim - Security Dimension"."Current Level 1 Login", "Core"."Dim - Security Dimension"."Current Level 2 Login", "Core"."Dim - Security Dimension"."Current Level 3 Login", "Core"."Dim - Security Dimension"."Current Level 4 Login", "Core"."Dim - Security Dimension"."Current Level 5 Login", "Core"."Dim - Security Dimension"."Current Level 6 Login", "Core"."Dim - Security Dimension"."Current Level 7 Login", "Core"."Dim - Security Dimension"."Current Level 8 Login", "Core"."Dim - Security Dimension"."Current Top Level Login")".
The IndexCol function in this definition makes the Hierarchy Based Column default to one of the logical columns in the list based on the value of HIER_LEVEL. So if HIER_LEVEL is 0, the new column will default to the first column in the list, etc.
A filter in the security group "Primary Employee/Position Hierarchy-based Security" defined as follows: ("Core"."Dim - Security Dimension"."Hierarchy Based Column" = VALUEOF(NQ_SESSION."USER")),
A user needs to be a member of the security group "Primary Employee/Position Hierarchy-based Security", through one of his responsibilities (for Siebel and Oracle EBS Applications) and Roles (for PeopleSoft application), for the data security filters to apply. Users are assigned to this security group based on their responsibilities, using the Initialization block: Authorization, defined in Section 7.6.3, "Initialization Blocks Used For Security Oracle BI Applications". Out of the box, this initialization block is populated using the following SQL:
Select 'GROUP', R.NAME
from VALUEOF(TBO).S_RESP R, VALUEOF(TBO).S_PER_RESP P, VALUEOF(TBO).S_USER U
where U.LOGIN=Upper(':USER') and U.ROW_ID=P.PER_ID and P.RESP_ID=R.ROW_ID
UNION
select 'GROUP', CASE VALUEOF(NQ_SESSION.HIER_LEVEL)
WHEN 0 THEN 'Hierarchy Level (Base)'
when 1 then 'Hierarchy Level 1'
when 2 then 'Hierarchy Level 2'
when 3 then 'Hierarchy Level 3'
when 4 then 'Hierarchy Level 4'
when 5 then 'Hierarchy Level 5'
when 6 then 'Hierarchy Level 6'
when 7 then 'Hierarchy Level 7'
when 8 then 'Hierarchy Level 8'
When 9 then 'Hierarchy Level (Top)'
ELSE 'NOGROUP' END from VALUEOF(TBO).S_DUAL
The first part of this SQL selects the user's responsibilities from the Siebel CRM application. The user will be assigned automatically to the Security groups with the same name in the Oracle Business Intelligence repository.
The second part of this sql assigns the user to one of the Oracle Business Intelligence specific security groups: "Hierarchy Level (Base)", …., "Hierarchy Level (Top)", based on the variable HIER_LEVEL. These security groups are not used for data security purposes, but just for Presentation column purposes, in conjunction with the Web Choose function defined in some reports. The purpose of this function is to allow a multi-user report to show different Position columns to the user, based on his hierarchy level. This is very similar to the IndexCol function defined above.
You can add Primary Position based Security to a new dimension or fact in Oracle Business Intelligence. The following tasks use a dimension W_AGREE_D (Agreement) as an example.
To add security support to a dimension
Create an alias on W_POSITION_DH specifically to join to the underlying physical table.
Configure the join in the physical layer.
Add the W_POSITION_DH alias to the dimension's Logical table source.
Add new logical columns CURRENT_BASE_LOGIN, CURRENT_LVL1ANC_LOGI; etc. to the logical table, and map them to the corresponding physical columns.
Add the Hierarchy column "Hierarchy Based Column", as defined in described in section 3.2 above.
Open the security group screen using Manage/Security in Oracle BI Administrator.
Right-click the group "Primary Employee/Position Hierarchy-based Security".and choose Properties.
In the Properties dialog box, click the Permissions box and select the Filter tab.
To add a new filter, click on the Add button.
In the new window, select the Business Model tab, and find the logical table: Dim - Agreement.
A new record will be added to the list of Filters automatically.
Click on the ellipsis box, and add the filter condition "Core"."Dim - Customer"."Hierarchy Based Login" = VALUEOF(NQ_SESSION."USER") in the Security Filter Expression Builder and click OK.
To add security support to a fact
Join the underlying physical table to Dim_W_POSITION_DH_Position_Hierarchy.
This assumes you already created the appropriate foreign key in the fact table, and populated it correctly.
Join the logical table to the Dim - Security Dimension.
Open the security group screen using Manage/Security in Oracle BI Administrator.
Right-click the group "Primary Employee/Position Hierarchy-based Security".and choose Properties.
In the Properties dialog box, click the Permissions box and select the Filter tab.
To add a new filter, click on the Add button.
In the new window, select the Business Model tab, and find the logical table: Dim - Agreement.
A new record will be added to the list of Filters automatically.
Click on the ellipsis box, and add the condition "Core"."Dim - Security Dimension"."Hierarchy Based Column" = VALUEOF(NQ_SESSION."USER") in the Security Filter Expression Builder and click OK.
This section covers primary position based security for CRM vertical applications and contains the following sections:
Section 7.5.2.1, "Oracle Partner Analytics Security Settings"
Section 7.5.2.2, "Consumer Sector Analytics Security Settings"
Section 7.5.2.3, "Communications, Media, and Energy (CME) Analytics Security Settings"
Section 7.5.2.4, "Financial Services Analytics Security Settings"
Section 7.5.2.6, "Usage Accelerator Analytics Security Settings"
Oracle's Siebel operational applications include Oracle's Siebel Sales, Oracle's Siebel Service, and Oracle's Siebel Partner Relationship Management. This topic describes the additional security configurations that may be necessary for the Siebel operational application Siebel ERM Analytics, which is divided into HR, Partner Manager, and Partner Portal Analytics.
Oracle Partner Analytics incorporates the concept of role-based analytics. Role-based analytics provides brand owners the ability to display dashboards and pages to users based on their specific roles. For example, a sales manager would have the ability to view dashboards related to pipeline and sales effectiveness, whereas the marketing manager would have the ability to view dashboards related to campaigns. Oracle Partner Analytics also includes flexible security mechanisms to control access to subject areas and to data.
The Analytics roles map to Siebel Responsibilities in the Siebel operational application. This topic describes the roles and associated dashboards and pages for both partner Manager and Partner Portal applications. It also includes subject area and data-level security settings for responsibilities.
Table 7-2 provides the dashboard and page tab mapping for specific responsibilities in the Siebel PRM Partner Manager application.
Table 7-2 Siebel Responsibilities for PRM Analytics
| Responsibility | Dashboard | Page Tab Name |
|---|---|---|
|
Channel Account Manager Analytics User |
Channel Customers |
Overview |
|
Channel Customers |
Sales |
|
|
Channel Sales |
Products |
|
|
Channel Sales |
Sales |
|
|
Channel Service |
Products |
|
|
Channel Service |
Service |
|
|
Channel Training |
Training Profile |
|
|
Channel Executive Analytics User |
Channel Customers |
Customer Profile |
|
Channel Executive |
Customer Satisfaction |
|
|
Channel Executive |
Pipeline |
|
|
Channel Executive |
Product |
|
|
Channel Executive |
Program |
|
|
Channel Executive |
Revenue |
|
|
Channel Executive |
Service |
|
|
Channel Segmentation |
Channel Mix |
|
|
Channel Segmentation |
Partner Territory |
|
|
Channel Segmentation |
Partner Tier |
|
|
Channel Segmentation |
Partner Type |
|
|
Channel Marketing Manager Analytics User |
Channel Customers |
Overview |
|
Channel Customers |
Sales |
|
|
Customer Marketing |
Effectiveness |
|
|
Customer Marketing |
Responses |
|
|
Customer Marketing |
ROI |
|
|
Channel Operations Analytics User |
Channel Commerce |
Orders |
|
Channel Commerce |
Overview |
|
|
Channel Commerce |
Quotes |
|
|
Channel Commerce |
Products |
|
|
Channel Customers |
Overview |
|
|
Channel Customers |
Sales |
|
|
Channel Customers |
Service |
|
|
Channel Marketing |
Effectiveness |
|
|
Channel Marketing |
Overview |
|
|
Channel Sales |
Margins |
|
|
Channel Sales |
Pipeline |
|
|
Channel Sales |
Revenue |
|
|
Channel Sales |
Sales Cycle |
|
|
Channel Sales |
Wins |
|
|
Channel Segmentation |
Partner Territory |
|
|
Channel Segmentation |
Partner Tier |
|
|
Channel Segmentation |
Partner Type |
|
|
Channel Service |
Customer Satisfaction |
|
|
Channel Service |
Overview |
|
|
Channel Service |
Products |
|
|
Channel Service |
Resolution Time |
|
|
Channel Service |
Service Requests |
|
|
Channel Training |
Overview |
|
|
Channel Training |
Performance |
The dashboard and page tab mapping for specific responsibilities in the Siebel PRM Partner Portal application are shown in Table 7-3.
Table 7-3 Responsibilities for PRM Partner Portal Analytics
| Responsibility | Dashboard | Page Tab Name |
|---|---|---|
|
Partner Executive Analytics User |
Partner Executive |
Pipeline |
|
Partner Executive |
Products |
|
|
Partner Executive |
Sales Effectiveness |
|
|
Partner Executive |
Service |
|
|
Partner Operations Analytics User |
Partner Commerce |
Overview |
|
Partner Commerce |
Products |
|
|
Partner Marketing |
Overview |
|
|
Partner Marketing |
ROI |
|
|
Partner Sales |
Pipeline |
|
|
Partner Sales |
Revenue |
|
|
Partner Service |
Customer Sat |
|
|
Partner Service |
Overview |
|
|
Partner Service |
Service Requests |
|
|
Partner Training |
Training |
|
|
Partner Sales Manager Analytics User |
Partner Commerce |
Orders |
|
Partner Commerce |
Overview |
|
|
Partner Commerce |
Quotes |
|
|
Partner Sales |
Pipeline |
|
|
Partner Sales |
Revenue |
|
|
Partner Sales |
Subordinates |
|
|
Partner Training |
Subordinates |
|
|
Partner Sales Rep Analytics User |
Partner Commerce |
Orders |
|
Partner Commerce |
Overview |
|
|
Partner Commerce |
Quotes |
|
|
Partner Sales |
Pipeline |
|
|
Partner Sales |
Revenue |
|
|
Partner Sales |
Subordinates |
|
|
Partner Training |
Subordinates |
|
|
Partner Service Manager Analytics User |
Partner Service |
Customer Sat |
|
Partner Service |
Overview |
|
|
Partner Service |
Service Requests |
|
|
Partner Service |
Subordinates |
|
|
Partner Training |
Subordinates |
|
|
Partner Service Rep Analytics User |
Partner Service |
Overview |
|
Partner Service |
Service Requests |
|
|
Partner Service |
Subordinates |
|
|
Partner Training |
Subordinates |
Ad hoc queries in Siebel PRM Analytics are built by the user, depending on user responsibilities and based on columns in subject areas in the Oracle Business Intelligence application. By restricting visibility to subject areas based on responsibilities, Oracle's Siebel PRM Analytics provides brand owners a flexible way to deploy role-based analytics.
The subject area visibility for responsibilities in Partner Manager are shown in Table 7-4, where a X indicates that subject area is visible for the user holding that responsibility.
Table 7-4 Responsibilities for PRM Partner Manager Analytics
| Subject Area | Channel Executive Analytics User | Channel Operations Analytics User | Channel Account Manager Analytics User | Channel Marketing Manager Analytics User |
|---|---|---|---|---|
|
Activities |
X |
X |
X |
X |
|
Assets |
X |
X |
X |
|
|
Campaigns |
X |
X |
X |
X |
|
Consumers |
X |
X |
X |
X |
|
Customer Satisfaction |
X |
X |
X |
|
|
Customers |
X |
X |
X |
X |
|
Orders |
X |
X |
X |
X |
|
Partner Training |
X |
X |
X |
|
|
Partners |
X |
X |
X |
X |
|
Pipeline |
X |
X |
X |
X |
|
Pricing |
X |
X |
X |
X |
|
Products |
X |
X |
X |
X |
|
Real-Time Activity |
||||
|
Real-Time Assets |
||||
|
Service Requests |
X |
X |
X |
The subject area visibility for roles in Partner Portal is shown in Table 7-5, where a X indicates that subject area is visible for the user holding that responsibility.
Table 7-5 Subject Area Visibility for PRM Partner Portal
| Subject Area | Partner Executive Analytics User | Partner Operations Manager Analytics User | Partner Sales Manager Analytics User | Partner Sales Rep Analytics User | Partner Service Manager Analytics User | Partner Service Rep Analytics User |
|---|---|---|---|---|---|---|
|
Activities |
X |
X |
X |
X |
X |
X |
|
Assets |
X |
X |
X |
X |
||
|
Campaigns |
X |
X |
||||
|
Consumers |
X |
X |
||||
|
Customer Satisfaction |
X |
X |
X |
X |
||
|
Customers |
X |
X |
X |
X |
X |
X |
|
Orders |
X |
X |
X |
X |
X |
X |
|
Partner Training |
X |
X |
X |
X |
X |
X |
|
Partners |
X |
X |
||||
|
Pipeline |
X |
X |
X |
X |
||
|
Pricing |
||||||
|
Products |
X |
X |
X |
X |
X |
X |
|
Real-Time Activity |
||||||
|
Real-Time Assets |
||||||
|
Service Requests |
X |
X |
X |
X |
Oracle PRM Analytics also provides brand owners the ability to restrict security based on the user's organization or position. This security mechanism makes sure that one user does not have access to another user's data. It also makes sure that one partner does not have access to another partner's data. Data-level security is administered for responsibilities. Details regarding setting up data-level visibility are provided in the topic Section 7.6.2, "Implementing Data-Level Security in the Oracle BI Applications Repository". To change Partner Service Representative and Sales Representative to Organization-based security, follow the procedure shown in the topic Section 7.5.4, "Embedded Oracle Business Intelligence support for Changing Positions".
Table 7-6 shows the data-level security settings included for the responsibilities in Partner Manager and Partner Portal.
Table 7-6 Oracle PRM Data-Level Security Settings
| Responsibility | Data-Level Security | Type | Comments |
|---|---|---|---|
|
Channel Executive Analytics User |
No |
N/A |
N/A |
|
Channel Operations Analytics User |
No |
N/A |
N/A |
|
Channel Account Manager Analytics User |
No |
N/A |
N/A |
|
Channel Marketing Manager Analytics User |
No |
N/A |
N/A |
|
Partner Executive Analytics User |
Yes |
Organization |
Displayed records should match organization of the user. |
|
Partner Sales Manager Analytics User |
Yes |
Organization |
Displayed records should match organization of the user. |
|
Partner Sales Rep Analytics User |
Yes |
Position |
Displayed records should match position of the user. |
|
Partner Service Manager Analytics User |
Yes |
Organization |
Displayed records should match organization of the user. |
|
Partner Service Rep Analytics User |
Yes |
Position |
Displayed records should match position of the user. |
Table 7-7 describes the consumer sector responsibilities associated with each CS Dashboard.
Table 7-7 Consumer Sector Responsibilities Associated with Each CS Dashboard
| Responsibility | Dashboard | Pages |
|---|---|---|
|
VP Sales |
VP Sales |
Business Overview, Product Overview |
|
Sales Performance |
Sales Volume Planning, Hierarchy, Trends, Growth |
|
|
Promotion |
Plan Year to Date, Corporate |
|
|
Key Account Manager |
Key Account Manager |
Business, Category |
|
Promotion |
Plan year to date, Key account |
|
|
Funds |
Account |
|
|
Retail Audit |
Last audit, Trends |
|
|
Sales Performance |
Sales Volume Planning, Hierarchy, Trends, Growth |
Oracle's CME family of products (Oracle Communications, Media and Energy Sales Analytics, Oracle Communications, Media and Energy Service Analytics, Oracle Communications, Media and Energy Marketing Analytics) applies the Oracle's Siebel operational applications security model; that is, it uses Oracle's Siebel operational applications responsibilities (and corresponding repository and Presentation Services groups) for controlling access to Oracle's Siebel operational applications objects (both metadata and Presentation Services objects). This security model is described in the topic Section 7.1, "Types of Security in Oracle Business Intelligence Applications".
In addition to responsibilities provided by the operational applications, Oracle Communications, Media, and Energy (CME) provides additional responsibilities, and responsibility-specific security, as indicated in Table 7-8.
Table 7-8 CME Responsibilities Associated with Each CME Dashboard
| CME Responsibility | CME Dashboard | Dashboard Pages |
|---|---|---|
|
CM Marketing Analytics User CM Marketing Analytics Administrator |
Loyalty Management |
|
|
CM Sales Analytics User CM Sales Analytics Administrator |
Revenue Management |
|
|
Account Management |
|
|
|
CM Service Analytics User CM Service Analytics Administrator |
Account Management |
|
The following applications apply Oracle's Siebel operational applications security model:
The Financial Analytics family of products (Finance Sales Analytics, Finance Service Analytics, Finance Marketing Analytics, Finance Institutional Analytics, Finance Retail Analytics).
The Insurance Analytics family of products (Oracle Insurance Partner Manager Analytics, Oracle Insurance Sales Analytics, Oracle Insurance Service Analytics, Oracle Insurance Marketing Analytics, Oracle Insurance Partner Manager Analytics).
The Healthcare Analytics family of products (Oracle Healthcare Sales Analytics, Oracle Healthcare Service Analytics, Oracle Healthcare Marketing Analytics, Oracle Healthcare Partner Manager Analytics).
In addition to responsibilities provided by the Siebel operational applications, these applications provide additional responsibilities, and responsibility-specific security, as indicated in Table 7-9.
For the Financial Services products, the Oracle's Siebel operational applications security model has been extended in the following ways:
Financial Analytics user
A finance-specific responsibility (and corresponding repository and Presentation Services group) that must be used in conjunction with Oracle's Siebel operational applications responsibilities and groups to control access to Finance-specific objects in Financial Analytics.
A user in the Oracle Insurance Analytics family of products (Oracle Insurance Partner Manager Analytics, Oracle Insurance Sales Analytics, Oracle Insurance Service Analytics, Oracle Insurance Marketing Analytics, Oracle Insurance Partner Manager Analytics)
An insurance-specific responsibility (and corresponding repository and Presentation Services group) that must be used to control access to the Insurance and Healthcare-specific objects in Insurance and the Healthcare Analytics family of products (Oracle Healthcare Sales Analytics, Oracle Healthcare Service Analytics, Oracle Healthcare Marketing Analytics, Oracle Healthcare Partner Manager Analytics).
For example, when you give a salesperson all horizontal Sales responsibilities and also include the finance responsibility Financial Analytics User, this user is able to see, in addition to all horizontal sales objects (Dashboards, Subject Areas, folders in the presentation Layer, and so on), all finance-specific Sales objects. Similarly, in order to see Insurance and Healthcare-specific objects, you need to add one of the Oracle Insurance Analytics family of products (Oracle Insurance Partner Manager Analytics, Oracle Insurance Sales Analytics, Oracle Insurance Service Analytics, Oracle Insurance Marketing Analytics, Oracle Insurance Partner Manager Analytics) user responsibilities to this user.
Oracle Business Intelligence supports hierarchies in the repository groups, and certain groups within the Oracle Business Intelligence repository are parent groups that define the behavior of all the child groups. For Financial Services Analytics, the parent groups are the following:
Finance
Parent group for all Financial applications groups. Financial Analytics User is a child group of Finance group.
Insurance
Parent group for all Insurance applications groups. Insurance Analytics User is a child group of Insurance group.
Inheritance is used to let permissions ripple through to child groups. The parent groups for Financial Services and their purpose are shown in Table 7-9.
|
Note: A Financial Services Analytics user is provided as a child to both Finance and Insurance. Therefore this user has permissions available to both Finance and Insurance. If you have purchased both Financial Analytics and one of the Oracle Insurance Analytics family of products (Oracle Insurance Partner Manager Analytics, Oracle Insurance Sales Analytics, Oracle Insurance Service Analytics, Oracle Insurance Marketing Analytics, Oracle Insurance Partner Manager Analytics), you should use the Financial Services Analytics user responsibilities to view all relevant dashboards. |
Table 7-9 shows the additional responsibilities, and responsibility-specific security in Oracle's Financial Analytics family of products (Finance Sales Analytics, Finance Service Analytics, Finance Marketing Analytics, Finance Institutional Analytics, Finance Retail Analytics), the Oracle Insurance Analytics family of products (Oracle Insurance Partner Manager Analytics, Oracle Insurance Sales Analytics, Oracle Insurance Service Analytics, Oracle Insurance Marketing Analytics, Oracle Insurance Partner Manager Analytics), and the Healthcare Analytics family of products (Oracle Healthcare Sales Analytics, Oracle Healthcare Service Analytics, Oracle Healthcare Marketing Analytics, Oracle Healthcare Partner Manager Analytics).
If you are also deploying Usage Accelerator, Financial Services-specific Usage Accelerator responsibilities are shown in Table 7-11.
Table 7-9 Financial Services Responsibility Required to View FS Dashboards
| FS Responsibilities | Dashboards |
|---|---|
|
Financial Analytics User |
Credit |
|
Credit Card |
|
|
Private Banking |
|
|
Consumer Banking |
|
|
Corporate and Commercial Banking |
|
|
Investment Holdings |
|
|
Separate Account Management |
|
|
Wealth Management |
|
|
Institutional Sales |
|
|
Investment Banking |
|
|
Finance Marketing |
|
|
Finance Executive |
|
|
User in one of the Oracle Insurance Analytics family of products (Oracle Insurance Partner Manager Analytics, Oracle Insurance Sales Analytics, Oracle Insurance Service Analytics, Oracle Insurance Marketing Analytics, Oracle Insurance Partner Manager Analytics) |
Policy Sales |
|
Policy Service |
|
|
Insurance Marketing |
|
|
Insurance Executive |
|
|
Insurance Claims |
|
|
Health Plan Sales |
|
|
Health Plan Service |
|
|
Health Plan Marketing |
|
|
Health Plan Executive |
|
|
Insurance Agents / Partners |
Data-level security in Oracle Pharma Sales Analytics and Oracle Pharma Marketing Analytics is based on Siebel Position ID for all Pharma Analytics responsibilities except PH Executive Analytics. Siebel Position ID is always resolved through the fact table.
Data visibility is unconstrained for administrative roles. For other roles, data visibility is controlled by Position ID. The Oracle Business Analytics Warehouse uses table W_POSITION_DH for user position based security control.address not being available. A user sees only the data that are available to that user's positions. This security model is enforced for all queries, with the exception of queries that deal exclusively with dimension data only, such as:
Time Period
Product
Invitee Status
Table 7-10 shows Pharma Analytics Responsibilities and Functions.
Table 7-10 Pharma Analytics Responsibilities and Functions
| Responsibility | Use |
|---|---|
|
LS Administrator |
Administrator privileges to all options on Pharma Analytics. |
|
PH Call Activity Analytics Admin |
Administrator privileges to Call Activity Analytics option. |
|
PH EMEA Call Activity Analytics User |
Enables brick-based metrics to be used in the Presentation Services for Pharma subject areas. Note that in the 7.7 Analytics Release, all report columns use position-based hierarchies, where in earlier releases, report columns used alignment-based sales hierarchies. All brick-based alignment pages have been removed from the reports. Therefore, if you want to use brick-based position hierarchies, you must reconfigure the reports to maintain the alternate hierarchy. |
|
PH EMEA Executive Analytics User |
Enables brick-based metrics to be used in the Presentation Services for Pharma subject areas. Note that in the 7.7 Analytics Release, all report columns use position-based hierarchies, where in earlier releases, report columns used alignment-based sales hierarchies. All brick-based alignment pages have been removed from the reports. Therefore, if you want to use brick-based position hierarchies, you must reconfigure the reports to maintain the alternate hierarchy. |
|
PH EMEA Marketing Analytics User |
Enables brick-based metrics to be used in the Presentation Services for Pharma subject areas. Note that in the 7.7 Analytics Release, all report columns use position-based hierarchies, where in earlier releases, report columns used alignment-based sales hierarchies. All brick-based alignment pages have been removed from the reports. Therefore, if you want to use brick-based position hierarchies, you must reconfigure the reports to maintain the alternate hierarchy. |
|
PH EMEA Sales Analytics User |
Enables brick-based metrics to be used in the Presentation Services for Pharma subject areas. Note that in the 7.7 Analytics Release, all report columns use position-based hierarchies, where in earlier releases, report columns used alignment-based sales hierarchies. All brick-based alignment pages have been removed from the reports. Therefore, if you want to use brick-based position hierarchies, you must reconfigure the reports to maintain the alternate hierarchy. |
|
PH Executive Analytics Admin |
Unrestricted access to all Pharma Analytics options with ZIP territories. |
|
PH Marketing Analytics Administrator |
Administrator privileges to Pharma ROI, Call Activity Profit & Loss Report, Pharma Promotional Effectiveness Subject Area, and Medical Education Effectiveness Subject Area. |
|
PH Medical Education Analytics Admin |
Administrator privileges to Medical Education Analytics option. |
|
PH Medical Education Analytics User |
Enables access to Medical Education Analytics option. |
|
PH Disconnected Analytics Admin |
Administrator privileges to the PH Disconnected Manager Analytics User and Sales Rep Analytics Dashboards. |
|
PH Disconnected Analytics User |
Enables the Pharma Disconnected Analytics Home Page. Allows access to Sales Rep Dashboard as part of the Sales Rep Analytics option. |
|
PH Disconnected Manager Analytics Admin |
Administrator privilege to the PH Disconnected Manager Analytics User and District Manager Analytics Dashboards. |
|
PH Disconnected Manager Analytics User |
Enables the Pharma Disconnected Analytics Home Page. Allows access to the District Manager Dashboard as part of the Sales Rep Analytics option. |
|
PH Sales Analytics Administrator |
Administrator privileges to Rx Sales Analytics option. |
|
PH US Call Activity Analytics User |
Enables access to Call Activity Analytics Option for ZIP territory alignments. |
|
PH US Executive Analytics User |
Unrestricted access to all Pharma Disconnected Analytics options with ZIP-based territories. |
Table 7-11 describes the additional security configurations that may be necessary and the particular responsibilities associated with the Oracle Usage Accelerator application dashboards.
Table 7-11 Usage Accelerator Responsibilities and Dashboards
| User Responsibility | Data Level Security | Dashboard Name (View) | Dashboard Page |
|---|---|---|---|
|
Usage Accelerator–Sales Rep |
Primary Position Data Level Security |
Score Card |
Individual Scorecard |
|
Usage Accelerator–Financial Services Sales Rep |
Action Plan |
Account Coverage Contact Coverage Opportunity Coverage Financial Account Coverage—Financial Services only Account Completeness Contact Completeness Opportunity Updates |
|
|
Usage Accelerator–Sales Manager |
No position-based Security |
Score Card |
Team Scorecard Individual Scorecard |
|
Action Plan |
Account Coverage (Team) Contact Coverage (Team) Opportunity Coverage (Team) Financial Account Coverage (Team) —Financial Services only Account Completeness (Team) Contact Completeness (Team) Opportunity Updates (Team) |
||
|
Usage Accelerator–Financial Services Sales Manager |
Coverage |
Account Coverage Account Coverage (Team) Contact Coverage Opportunity Coverage Financial Account Coverage—Financial Services only |
|
|
Completeness |
Account Completeness Contact Completeness |
||
|
Opportunity Updates |
Opportunity Updates |
||
|
User Adoption |
Active Users Application Usage—excluded for Financial Services Application Usage—Financial Services only* |
||
|
Usage Accelerator–Sales Executive |
No position-based Security |
Scorecard |
Organization Scorecard Individual Scorecard |
|
Usage Accelerator–Financial Services Sales Executive |
Action Plan |
Account Coverage (Org) Contact Coverage (Org) Opportunity Coverage (Org) Financial Account Coverage (Org)—Financial Services only Account Completeness (Org) Contact Completeness (Org) Opportunity Updates (Org) |
|
|
Usage Accelerator–Administrator |
Coverage |
Account Coverage Contact Coverage Opportunity Coverage Financial Account Coverage—Financial Services only |
|
|
Usage Accelerator–Financial Services Administrator |
Completeness |
Account Completeness Contact Completeness |
|
|
Opportunity Updates |
Opportunity Updates |
||
|
User Adoption |
Active Users Application Usage (excluded for Financial Services) Application Usage—Financial Services only Although the Application Usage dashboard names appears similar, the Application Usage—Financial Services-only version of this dashboard is different from the other dashboard. |
This section covers team based visibility and contains the following sections:
Team based visibility will enable the Oracle Business Intelligence users to have access to all records of an entity (for example, opportunity) based on the team that they or their subordinates are part of. This is very similar to the team visibility implemented in Siebel transactional application.
This section uses Opportunity as an example entity to explain how team based visibility can be implemented. The same concept can be extended to other entities.
|
Note: This type of data security is not supported in the metadata that is installed out-of-the-box. Oracle BI Applications provides only some of the required ETL mappings in the Informatica repository, as described in this section. The examples in this section should provide enough information to enable you to code the missing pieces and add them to your application. |
In Siebel transactional application, "my team" view allows users to see those records of which they are members of the record's team. It has been noted that similar view is needed for Oracle Business Intelligence users when they query the Oracle BI Application Data Warehouse. In this visibility mode, the query should return records for which the user (or people reporting to the user) is part of the record's team.
Oracle BI Applications administrator will create special group for team-based visibility that will contain appropriate filters on select entities. The Administrator will assign users to this group who need this visibility filter to be applied. This configuration will force the visibility filter to be applied to all queries issued by the users on associated entities, causing only the right set of rows to be returned to the user.
The design of team-based visibility implementation needs special visibility tables to be populated in the Oracle Business Analytics Warehouse. These tables will be populated by special ETL mappings and used to define the filter condition for analytics queries.
In the following sections, Opportunity is used as an example entity for which team-based visibility needs to be implemented. The same concept can be extended to other entities.
The W_PARTY_LOGIN table (included in the out-of-the-box application) decodes the position hierarchy information that is used for all entities. The table below describes the structure of the W_PARTY_LOGIN table.
Table 7-12 Structure of W_PARTY_LOGIN table
| Columns | Description |
|---|---|
|
PARTY_ID |
The party_id (position) for the user. |
|
LOGIN |
The login_id for the parent position (including any level parent). |
|
PARTY_TYPE |
'P' for Position party type, 'O' for Organization party type, 'E' for Employee party type. |
The transactional source for this table is S_PARTY_RPT_REL. All possible pairs of position_id and its parent's login at any level are selected from the source table during ETL and inserted in this table. The same table is being used to store hierarchy relationships for other party objects (for example, Organization). Therefore a party_type column has been added to separate these different sets of records.
One table for each entity is needed to store the team members (Positions) associated with every object. The following example is for Opportunity. The table below describes the structure of the W_OPTY_PARTY table.
Table 7-13 Structure of W_OPTY_PARTY table
| Columns | Description |
|---|---|
|
OPTY_ID |
The row_id for the opportunity. |
|
PARTY_ID |
The party_id (position) for the user associated to opportunity. |
The transactional source for this table is S_OPTY_POSTN. The grain of this table is same as the grain of the source table. The relationship between these tables and the W_OPTY_D (the Oracle Business Analytics Warehouse table that requires visibility filtering) is shown in the following diagram.
|
Note: There is a M:M relationship between W_OPTY_D (the entity table which require visibility) and the W_PARTY_LOGIN table. Therefore a filter using the joins shown above will provide duplicate opportunity rows and cause errors in aggregation. |
To get around this issue the party tables needs to be pre-joined and duplicate rows filtered. Both these are combined to provide a distinct set of (OPTY_ID, LOGIN) in the table W_OPTY_LOGIN (not included in the out-of-the-box application). The table below describes the structure of the W_OPTY_LOGIN table.
Table 7-14 Structure of W_OPTY_LOGIN table
| Columns | Description |
|---|---|
|
OPTY_ID |
The row_id for the opportunity. |
|
LOGIN |
The login id for the user who will have visibility access to the opportunity. |
This is done using the following SQL:
SELECT W_PARTY_LOGIN.LOGIN, W_OPTY_PARTY.OPTY_ID
FROM W_PARTY_LOGIN, W_OPTY_PARTY
WHERE W_OPTY_PARTY.PARTY_ID = W_PARTY_LOGIN.PARTY_ID AND
W_PARTY_LOGIN.PARTY_TYPE = 'P' /*For only position records */
GROUP BY LOGIN, OPTY_ID
The relationship between the W_OPTY_D table and the W_OPTY_LOGIN table is a one-to-many relationship, and can be used for filtering. This is shown in the following diagram:
Similar intersection tables need to be created to support team visibility security for the other dimensions (or facts). Depending on your requirements, you must create one (or more) of the following tables, in a manner similar to W_OPTY_LOGIN.
W_ACT_LOGIN (for Activity)
W_CON_LOGIN (for Contacts)
W_HHOLD_LOGIN (for Households)
W_ORDER_LOGIN (for Orders)
W_ORG_LOGIN (for Accounts)
W_PROG_LOGIN (for Marketing Programs)
W_QUOTE_LOGIN (for Quotes)
W_RESP_LOGIN (for Responses)
W_SEG_LOGIN (for Segments)
W_SR_LOGIN (for Service Requests)
These tables should be created both in the DAC repository (under a custom container), and in the ETL repository, to be able to build the corresponding mappings.
It has been shown how to use the W_OPTY_LOGIN table to secure data in W_OPTY_D. If you want to secure the fact table W_REVN_F with team visibility, you can choose one of the following options:
Define the filter on "Fact – Revenue", using the Opportunity Dimension (Dim – Opportunity).Login field. In this case, all Revenue reports will join to the Opportunity dimension, even if there is no field being selected from that table in the group by clause.
Use W_OPTY_LOGIN to create a filter on the Revenue fact (Fact – Revenue) directly. But in this case you need to add an OPTY_ID to W_REVN_F, so you can join directly to W_OPTY_LOGIN.
The sections covers ETL Design.
There will be a set of ETL routine to populate the tables mentioned in the data model section. In the standard application there are visibility mappings to populate the following tables:
Common party login table:
W_PARTY_LOGIN
Entity Party tables:
W_OPTY_PARTY (for Opportunity)
W_ACT_PARTY (for Activity)
W_CON_PARTY (for Contacts)
W_HHOLD_PARTY (for Households)
W_ORDER_PARTY (for Orders)
W_ORG_PARTY (for Accounts)
W_PROG_PARTY (for Marketing Programs)
W_QUOTE_PARTY (for Quotes)
W_RESP_PARTY (for Responses)
W_SEG_PARTY (for Segments)
W_SR_PARTY (for Service Requests)
The standard ETL mappings responsible for populating these tables, available in the Informatica repository, are:
VisibilityAccountParty
VisibilityActivityParty
VisibilityContactParty
VisibilityHouseHoldParty
VisibilityOpportunityParty
VisibilityOrderParty
VisibilityProgramParty
VisibilityQuoteParty
VisibilityResponseParty
VisibilitySegmentParty
VisibilityServiceRequestParty
VisibilityPartyLogin
It should be noted that the records in these tables need to be removed when a position is removed from a team. This has to be done by tracking deletes on the corresponding source table and using the deleted row information. Any removed rows need to be deleted by ETL process from the target tables.
The mappings to delete rows are available in the Informatica repository as follows:
VisibilityAccountParty_LoadDeletedRows
VisibilityActivityParty_LoadDeletedRows
VisibilityContactParty_LoadDeletedRows
VisibilityHouseHoldParty_LoadDeletedRows
VisibilityOpportunityParty_LoadDeletedRows
VisibilityOrderParty_LoadDeletedRows
VisibilityProgramParty_LoadDeletedRows
VisibilityQuoteParty_LoadDeletedRows
VisibilityResponseParty_LoadDeletedRows
VisibilitySegmentParty_LoadDeletedRows
The ETL mappings to populate W_<entity>_LOGIN do not exist in the standard Oracle Business Intelligence Applications and can be easily built using the SQL provided in the data model section.
Some Image tables need to be updated automatically when the OLTP data is changed. For this purpose, you need to enable some triggers as follows:
In the Applications Data Warehouse, preconfigured visibility tables are activated by default. If your organization is not going to use any of the visibility-related data warehouse tables, you need to inactivate them in the Tables/Related Tables screen of the Design view on the DAC client. If you leave them as activate because you are planning to enable team visibility, you should also create delete triggers on the optional tables.
When creating delete triggers using the Data Warehouse Configurator, you have the option of including the optional tables for which you want to enable the delete triggers. You also have the option of having the trigger statements be directly executed or written to a file, which can then be executed by a database administrator.
To create a delete trigger:
From the DAC menu bar, click Tools > ETL Management Configure.
Select the Create Delete Triggers in Transaction Database check box.
The Delete Triggers tab is now active.
Select Create Triggers.
Select the transactional database where the triggers will be created.
(Optional) Select the Include optional triggers check box to include optional tables.
Click Start.
All the Visibility tables and corresponding tasks mentioned above have already been imported into the DAC metadata. All tasks responsible for synchronizing the tables with OLTP tables (in case of Deletion) are also automatically included. Each of these tables (like W_OPTY_LOGIN) is listed as a Related table for the corresponding Fact table (W_OPTY_D in this case). If you are adding a fact to your Subject area, these tasks will be included in your Execution plan automatically. For the additional intersection tables (W_OPTY_LOGIN etc.), assuming that you already created them in DAC, you need to do the following steps to ensure they will be added to your execution plan, and populated during ETL:
Import the ETL workflows responsible for populating them to the Task list.
Add every table as a Related table to the corresponding fact or dimension.
All the Visibility tables and corresponding tasks mentioned above have been already imported into the DAC metadata. All tasks responsible for synchronizing the tables with the OLTP (in case of Deletion.) are also automatically included. Each of these tables (like W_OPTY_LOGIN) is listed as a Related table for the corresponding Fact table (W_OPTY_D in this case). If you are adding a fact to your Subject area, these tasks will be included in your Execution plan automatically. For the additional intersection tables (W_OPTY_LOGIN,…), assuming that you already created them in DAC, you need to do the following steps to ensure they will be added to your execution plan, and populated during ETL:
Import the ETL workflows responsible for populating them to the Task list.
Add every table as a Related table to the corresponding fact or dimension.
In the metadata that is provided with the Oracle Business Intelligence Applications, Primary Position based Security security is implemented using the security group: Primary Employee/Position Hierarchy-based Security. In order to apply this type of security to a user, you need to add at least one of the user's responsibilities (in OLTP) to this group. This is different from what was done in Oracle BI Applications 7.9.0, 7.9.1 and 7.9.2 releases and from what is described in the Oracle Business Intelligence Applications Installation and Configuration Guide version 7.9 > Integrated Security for Oracle BI Applications. This security group is still based on the Primary Position owner of the record (Fact or Dimension), and the Position hierarchy stored in W_POSITION_DH.
The team-based security is based on the W_<entity>_LOGIN tables that have been described above. To enable Team-based security, you need to add some metadata elements, and change some of the standard elements.
To enable team-based security:
In the Physical Layer:
Import the new W_<entity>_LOGIN table in physical layer.
Join the table W_<entity>_LOGIN to the corresponding dimension or fact. For example, join W_OPTY_LOGIN to W_OPTY_D.
In the Logical Layer:
If the table to be secured is a Dimension, add the W_<entity>_Login table to the logical table source for the corresponding dimension.
If the table to be secured is a Fact, create a new Logical dimension based on the W_<entity>_LOGIN table, for example, Dim – Team Security (Opportunity).
Expose the LOGIN column in the logical layer as a logical column.
Security Filters:
Create a new user group for team-based security (for example, Team Position Based Security).
Users to whom you want to apply team-based visibility security filters need to have at least one of their responsibilities as part of this group. You can add a responsibility to a group by double clicking on the group name, and then clicking the Add/Remove button.
Create filters on the respective fact or dimension tables. The screenshot below shows the Filters tab of the User/Group Positions dialog, with filters specified for both fact and dimension tables.
Users who are members of this Team Visibility group should not be members of the existing Primary Employee/Position Hierarchy-based Security. The filter in this latter group is more restrictive and it will not add anything to the data set, since the two filters will be grouped with an OR condition.
In the Siebel CRM Application, a user can assume a new Position during the same session by clicking on the button: Change Position in User Preferences/Change Position screen. This screen allows the user to choose from a set of Positions already associated with him through Employee/Position screen. Currently, when the user changes positions in the OLTP application, the data in the embedded Oracle Business Intelligence reports is still filtered by his Primary Position, and not the new position he selected. This section describes a solution that allows users to connect seamlessly to Oracle Business Intelligence dashboards from an OLTP application using the new Position, and see all the data available for the impersonated position. The data security in this case will filter on the Login of the Primary Owner for the new Position.
This enhancement requires the following steps to be implemented:
Configure the RunAs option in the Oracle Business Intelligence Enterprise Edition environment.
This step is explained in Chapter 8 of Oracle Business Intelligence Presentation Services Administration Guide, in the section: About Authorizing Users to Act for Others. This document can be obtained at the following address:
http://www.oracle.com/technology/documentation/bi_ee.html
This feature allows a user to impersonate other Oracle Business Intelligence users, based on a table that lists the Proxy (the impersonating user) and the target user(s) with their login. This information can be stored in a physical table, or in a view populated dynamically using the association between Positions in S_PARTY_PER. You can find a definition of this view in the installation directory under (\\installdirectory\EmbeddedAnaltyics\Proxy_View.sql).
Configure the Siebel CRM Application to communicate the new Position to the Oracle BI server.
This step requires adding two new functions to the Siebel CRM Application you are running (like Universal Agent, Marketing, etc.). The purpose of these functions is:
Capture the new Position selected by the Change Position button.
Find the login of the Primary Employee of the new position.
Pass this login to a new session variable ActPosLogin.
The sif files containing these records are available in your installation directory. You should import these sif files to your Siebel Repository (using Siebel Tools), then recompile the corresponding projects into your srf file. The sif files are available for eScript (\\installdirectory\EmbeddedAnalytics\EScript) and VB Script (\\installdirectory\EmbeddedAnalytics\VB Script). You can use either one of them, based on your preference. If the application you are using is not Siebel Universal Agent, you can create a similar record directly in Siebel Tools for your own application. You need to do the following steps:
Go to Siebel Tools/Detail/Application/<your application> /Application Server Scripts
Create a new script called Application_Start as defined in the sample files available in \\installdirectory\EmbeddedAnalytics (under Escript and VB Script directories.)
Add a new Symbolic URL parameter to the existing embedded dashboards.
The last step is to add a new parameter under Symbolic URL screen for each of the embedded dashboards. The new parameter is called RUNAS.
In the OLTP application, go to Integration Administration/Symbolic URL.
Choose Symbolic URL Administration.
Search for the dashboard you want to support with this new functionality.
Under Parameters, add a new parameter called RUNAS as follows:
i. ArgType: Profile Attribute
ii. ArgumentValue: ActPosLogin
The RUNAS parameters is expected by the Proxy feature described in the first paragraph, and the ActPosLogin is the new variable added and populated by the new metadata added to the Siebel Repository in the second paragraph.
The Business Unit security is supported through the Security group named Primary Org-Based Security. When installed out-of-the-box, only a few dimensions in the Core, Workforce Analytics and Forecasting business models are supporting this data security type. Other facts and dimensions can be added to this security group if they have the column VIS_PR_BU_ID column populated.
The security filter in this security group is defined as:
"Core"."Dim - Order"."VIS_PR_BU_ID" = VALUEOF(NQ_SESSION."ORGANIZATION")
The session variable ORGANIZATION is a Row-wise variable, initialized using the Initialization block: Orgs for Org-Based Security. This Init Block runs the following SQL against the Siebel OLTP data source, to populate the ORGANIZATION variable:
select distinct 'ORGANIZATION', PRR.SUB_PARTY_ID
from VALUEOF(TBO).S_POSTN P, VALUEOF(TBO).S_USER U, VALUEOF(TBO).S_PARTY_PER PP, VALUEOF(TBO).S_PARTY_RPT_REL PRR
where U.ROW_ID=PP.PERSON_ID and P.ROW_ID=PP.PARTY_ID and PRR.PARTY_ID = P.BU_ID and PRR.PARTY_TYPE_CD = 'Organization' and U.LOGIN = ':USER'
Primary Owner security is supported through the security group named Primary Owner-Based Security. This type of security mechanism allows records to be visible only to their Primary Owner. When installed out-of-the-box, this type of security supports a few dimensions in the Core business model, but other tables can be added if they have a primary owner's source Integration Id column.
The security filter in this security group is defined as:
"Core"."Dim - Activity"."VIS_PR_OWN_ID" = VALUEOF(NQ_SESSION."PR_OWNER_ID")
The session variable PR_OWNER_ID is a single value variable, populated by the Initialization block: Primary Owner ID. This Init Block runs the following sql against the Siebel OLTP data source, to populate the variable:
select PAR_ROW_ID
from VALUEOF(TBO).S_USER
where LOGIN = ':USER'
This section describes the data-level security features in Oracle BI Applications, and contains the following topics:
Section 7.6.1, "Data-Level Security in Oracle Business Intelligence Applications"
Section 7.6.2, "Implementing Data-Level Security in the Oracle BI Applications Repository"
Section 7.6.3, "Initialization Blocks Used For Security Oracle BI Applications"
Data-level security defines what a user in an OLTP application (for example, Oracle E-Business Suite, Siebel (CRM), PeopleSoft) can access inside a report. The same report, when run by two different users, can bring up different data. This is similar to how the My Opportunities view in an operational application displays different data for different users. However, the structure of the report is the same for all users, (unless a user does not have access to a column in a report, in which case the column is not displayed for that user). As another example, the Procurement Manager of Vision USA should only see the Procurement details that happened in USA (assuming that the organization has clearly demarcated their procurement organization on the basis of geographical territories). Similarly, the Procurement Manager of Asia-Pacific should see procurement data of Asia-Pacific Region only.
Even if both these persons belong to the "Procurement Manager" group, the actual data seen by them varies, depending on the access controls they have. This is a critical requirement and spans across all pillars (HCM, SCM, FII, CRM) etc.
Since the early Siebel Analytics releases, the solution supported the following types of data security:
Primary Position-based security for Siebel CRM Applications.
Primary Owner based security for Siebel CRM Applications.
Business Unit based security for Siebel CRM Applications.
In release 7.9.0, Oracle BI Applications have introduced the following types of data security:
Operating Unit Org-based security for Oracle EBS.
Inventory Org-based security for Oracle EBS.
The Position-based security for Siebel CRM Applications has been extended to support Slowly changing position hierarchies, and AS IS/AS WAS type of analysis.
In release 7.9.3, Oracle BI Applications have expanded the scope to:
Company Org-based security for EBS, PeopleSoft Financials, and PeopleSoft HCM.
Business Group Org-based security for EBS.
Operating Unit Org-based security for PeopleSoft Financials, and PeopleSoft HCM.
SET ID-based security for PeopleSoft Financials, and PeopleSoft HCM.
In Version 7.9.4, Oracle BI Applications replaced the Company Org-based security with:
Ledger security for EBS, PeopleSoft Financials, and PeopleSoft HCM.
The following data security type is not supported, buy will be included in future releases:
GL flex field (for EBS).
Here is the summary chart of these security groups and different source applications they support in different Oracle Business Intelligence Enterprise Edition Application releases. During installations and configuration, customers have to make sure the correct security group and initialization blocks are set up for different sources and different releases. A security group, which is out of the source-and-release matrix as below, should be disabled as well as its corresponding initialization blocks. The details of security group and its initialization blocks are addressed in parts after this chart.
Table 7-15 Summary of security groups and different source applications supported in Oracle Business Intelligence Applications releases
| Oracle EBS | PeopleSoft Financials | PeopleSoft HR | Siebel | |
|---|---|---|---|---|
|
Operating Unit Org-based Security |
Available since 7.9 |
Available since 7.9 |
Available since 7.5 |
|
|
Inventory Org-based Security |
Available since 7.9 |
|||
|
Company Org-based Security |
Available in 7.9.3 and obsolete in 7.9.4 |
Available in 7.9.3 |
Available in 7.9.3 |
|
|
Business Group Org-based Security |
Available since 7.9.3 |
|||
|
HR Org-based Security |
Available since 7.9.3 |
|||
|
Payables Org-based Security |
Available since 7.9.3 |
|||
|
Receivables Org-based Security |
Available since 7.9.3 |
|||
|
SET ID-based security |
Available since 7.9.3 |
Available since 7.9.3 |
||
|
Position-based security |
Available since 7.9.4 for HRMS |
Available since 7.9.3 |
Available since 7.5 |
|
|
Ledger-based security |
Available since 7.9.4 |
Available since 7.9.4 |
Data-level security in Oracle BI Applications is implemented in three major steps:
Set up initialization blocks that obtain specific security-related information when a user logs in—for example, the user's Hierarchy Level in the Organization hierarchy, or the User's responsibilities.
See Section 7.6.3, "Initialization Blocks Used For Security Oracle BI Applications".
Set up the joins to the appropriate security tables in the metadata physical and logical layers.
Set up the filters for each security group on each logical table that needs to be secured.
See Section 7.5.1, "Primary Position based Security" for Employee/Position Based security, and Section 7.2, "Integrating Data Security with Oracle E-Business Suite" and Section 7.3, "Integrating Data Security with Oracle's PeopleSoft Enterprise Applications" for Organization based security.
In the Oracle Business Intelligence repository, the initialization blocks are set up for obtaining a given user's primary position, primary organization, and the owner ID, as described below:
Authorization
This initialization block is used to associate users with all Security groups to which they belong. It obtains a user's responsibilities or roles from the source OLTP application, matches them with Oracle BI applications security groups, and determine the user's applicable object security during the session. This initialization block populates a variable set called GROUP.
Business Groups
This initialization block is used to retrieve the business groups from OLTP application to which the corresponding login responsibility has access. This initialization block populates a variable set called BUSINESS_GROUP, which is used to drive security permissions for Business Group Org-based security.
Companies
This initialization block is used to retrieve the companies from OLTP application to which the corresponding login responsibility has access. This initialization block populates a variable set called COMPANY, which is used to drive security permissions for Company Org-based security.
HR Organizations
This initialization block is used to retrieve the HR organizations from OLTP application to which the corresponding login user has access. This initialization block populates a variable set called HR_ORG, which is used to drive security permissions for HR Analysts.
Inventory Organizations
This initialization block is used to retrieve the inventory organizations from OLTP application to which the corresponding login responsibility has access. This initialization block populates a variable set called INV_ORG, which is used to drive security permissions for Inventory Org-based security.
Ledgers
This initialization block is used to retrieve the ledgers from the OLTP application to which the corresponding login responsibility has access. This initialization block populates a variable set called LEDGER, which is used to drive security permissions for Ledger-based security.
Operating Unit Organizations
This initialization block is used to retrieve the operating unit organizations from OLTP application to which the corresponding login responsibility has access. This initialization block populates a variable set called OU_ORG, which is used to drive security permissions for Operating Unit Org-based security.
Orgs for Org-Based Security
This initialization block is used to retrieve the Organizations reporting to the current user's Business Unit, from the Siebel CRM OLTP application.
This initialization block populates a variable set called ORGANIZATION, which is used to drive Primary Org-Based Security.
Payable Organizations
This initialization block is used to retrieve the payable organizations from OLTP application to which the corresponding login responsibility has access. This initialization block populates a variable set called PAYABLE_ORG, which is used to drive security permissions for Payable Org-based security.
Primary Owner ID
This initialization block obtains the owner ID for the given user. It obtains this information from the Siebel OLTP and populates the PR_OWNER_ID variable.
Receivable Organizations
This initialization block is used to retrieve the payable organizations from OLTP application to which the corresponding login responsibility has access. This initialization block populates a variable set called PAYABLE_ORG, which is used to drive security permissions for Payable Org-based security.
Set ID
This initialization block is used to retrieve the Set IDs from OLTP application to which the corresponding login responsibility has access. This initialization block populates a variable set called SET_ID, which is used to drive security permissions for Set ID-based security.
User Hierarchy Level
This initialization block obtains the fixed hierarchy level of the given user, based on the user's login, from W_POSITION_DH. It populates the variable HIER_LEVEL. The SQL used by the block is run against the data warehouse. Therefore it reflects the hierarchy level at the time of the last ETL run that populated this table (W_POSITION_DH).
User HR Organizations
This initialization block is used to retrieve the current HR organization from OLTP application to which the current user belongs. This initialization block populates a variable called USER_HR_ORG.
The table below describes the security groups used in Oracle Business Intelligence Applications and the Application to which they apply. Some selected security groups, which share the same name as Responsibilities (for Siebel CRM and Oracle EBS applications) and Roles (for PeopleSoft application), have been added as members of some of these security groups. A user who has any of these responsibilities or roles in the Source application will be a member of the corresponding data security group automatically when he/she logs in to the Analytics application. Other security groups based on similar objects in the source applications can be added to the Analytics repository and added to these data security groups, if you need the corresponding data filters to apply to any additional group of users.
Table 7-16 Data Security Groups in Oracle BI Applications
| Security Group Name | Application | Description |
|---|---|---|
|
Business Group Org-based Security |
Oracle E-Business Suite |
A Business Group is the highest level in the organization structure, and is usually used to represent the entire enterprise or a major division. A business group can have several sets of books. |
|
Company Org-based Security |
PeopleSoft |
The Company Org-based Security filters data based on the General Ledger business units associated to the logged in user. The Business Unit is the highest level key structure in PeopleSoft. |
|
HR Org-based Security |
PeopleSoft |
The HR Org-based Security filters data based on the HR business units associated to the logged in user. The Business Unit is the highest level key structure in PeopleSoft. |
|
Human Resources Analyst |
PeopleSoft |
HR analysts have a special visibility requirement: They need to see all data for the Internal Organizations they are responsible for plus the data for their subordinates in their own organization. This is a special security group to address this requirement. |
|
Operating Unit Org-based Security |
Oracle E-Business Suite |
Monthly and yearly fiscal reports are created by Operating Units which can belong to only one Legal Entity. Operating Units are secured by attaching a security profile to a user ID or responsibility. In turn, a security profile is associated with an organization hierarchy, which also has access to the user ID or responsibility. The Operating Unit Org-based security filters data based on the OUs associated to the logged in user. |
|
Payable Org-based Security |
PeopleSoft |
The Payables Org-based Security filters data based on the Payables business units associated to the logged in user. The Business Unit is the highest level key structure in PeopleSoft. |
|
Primary Employee/Position Hierarchy-based Security |
Not applicable. |
The Employee/Position based security allows only the record owner, and any Employee up in his hierarchy chain to see the record. |
|
Primary Org-Based Security |
Siebel CRM |
Filters data based on the organization(s) associated to the logged in user. |
|
Primary Owner-Based Security |
Siebel CRM |
Filters data based on the logged in user. |
|
Primary Position-Based Security (Obsolete) |
Not applicable. |
Use the new Primary Employee/Position Hierarchy-based Security. |
|
Receivables Org-based Security |
PeopleSoft |
The Receivables Org-based Security filters data based on the Receivables business units associated to the logged in user. The Business Unit is the highest level key structure in PeopleSoft. |
|
SET ID-based Security |
PeopleSoft |
The SET ID-based Security filters data based on the set IDs associated to the logged in user. |
|
Inventory Org-based Security |
Oracle E-Business Suite |
An Inventory Organization tracks inventory transactions and balances, and/or manufactures or distributes products or components. The Inventory Org-based security filters data based on the inventory orgs associated to the logged in user. |
|
Ledger-based Security |
Oracle E-Business Suite |
A ledger is essentially a reporting organization that uses a common chart of accounts, functional currency, fiscal calendar, and accounting method. The Ledger-based security filters data based on the ledgers associated to the logged in user. |
